0

I also seem to have a problem with the location of the Public Function location, as well...I seem to have to place it outside of the Sub cmdImport, or else when I submit my form, it kicks back an error. When I place the function outside of the Sub, however, the message that gets kicked back to me is that 0 records were imported.

Just to recap, here's my code as it stands now:

Public Function USED()
        USED = Application.CountIf(ActiveSheet.Columns(1), ">""") + Application.CountIf(ActiveSheet.Columns(1), ">0")
    End Function 'This will get the maximum rows to be copied. There are other ways as well...

Private Sub cmdImport_Click()

      Dim Row As Integer, Max_Row As Integer, Max_Col As Integer

    'Make sure they enter a file before continuing
    If IsNull(Me.txtFileName) Or Len(Me.txtFileName & "") = 0 Then
        MsgBox "Please Select a CSV File Before Proceeding"
        Me.cmdSelect.SetFocus
        Exit Sub
    End If

    'declare your database variable
    Dim proddb1 As Database

    'declare your script variables
    Dim f As Integer, strSQL, strvalues, RecordCount As Integer, RowCount As Integer

    'declare your database field variables
    'Dim ID As String
    Dim ClientIP As String, ImportDate As String, ReportDate As String
    Dim Requests As String, PageViews As String
    Dim BrowseTime As Integer, BrowseTimeUnit As String
    Dim TotalBytes As Integer, TotalBytesUnit As String
    Dim BytesReceived As Integer, BytesReceivedUnit As String
    Dim BytesSent As Integer, BytesSentUnit As String

    Dim CSVFile
    Dim CurrentRow As String
    
    'Assign values to the Dims
    CSVFile = Me.txtFileName
    ReportDate = Me!ReportMonth & "/01/" & Me!ReportYear
    ImportDate = DateValue(Now)
    f = FreeFile()
    RowCount = 1
    RecordCount = 0
    
    Set proddb1 = OpenDatabase("internet_statistics.mdb")
    
    'Open the CSV file based on the Dim name
    Open CSVFile For Input As #f
    'Open CSVFile For Binary Access Write As f
    
    'Walk through the rows and perform the operation in the loop,
    'breaking out of the loop when you hit the end of the file
    'Do Until EOF(f)

    For Row = 2 To Max_Row 'you can change this to For row = 4 to max_row etc
        Input #f, ClientIP, Requests, PageViews, BrowseTime, TotalBytes, BytesReceived, BytesSent
        'If Len(ClientIP) > 0 Then
        
            If RowCount < 5 Then
                MsgBox "Row: " & RowCount & Chr(13) _
                & "Client IP: " & ClientIP & Chr(13) _
                & "Import Date: " & ImportDate & Chr(13) _
                & "Report Date: " & ReportDate & Chr(13) _
                & "Requests: " & Requests & Chr(13) _
                & "Page Views: " & PageViews & Chr(13) _
                & "Browse Time: " & BrowseTime & " " & BrowseTimeUnit & Chr(13) _
                & "Total Bytes: " & TotalBytes & " " & TotalBytesUnit & Chr(13) _
                & "Bytes Received: " & BytesReceived & " " & BytesReceivedUnit & Chr(13) _
                & "Bytes Sent: " & BytesSent & " " & BytesSentUnit & Chr(13) _
                

            End If
        
            If BrowseTime < 60 Then
                BrowseTimeUnit = " Mins"
            ElseIf BrowseTime >= 60 And BrowseTime < 1440 Then
                BrowseTime = BrowseTime / 60
                BrowseTimeUnit = " Hrs"
            ElseIf BrowseTime >= 1440 Then
                BrowseTime = BrowseTime / 1440
                BrowseTimeUnit = " Days"
            End If
            
            If TotalBytes < 1024 Then
                TotalBytesUnit = "Bytes"
            ElseIf TotalBytes >= 1024 And TotalBytes < 1048576 Then
                TotalBytes = TotalBytes / 1024
                TotalBytesUnit = "KB"
            ElseIf TotalBytes >= 1048576 Then
                TotalBytes = TotalBytes / 1048576
                TotalBytesUnit = "MB"
            ElseIf TotalBytes >= 1073741824 Then
                TotalBytes = TotalBytes / 1073741824
                TotalBytesUnit = "GB"
            End If
            
            If BytesReceived < 1024 Then
                BytesReceivedUnit = "Bytes"
            ElseIf BytesReceived >= 1024 And BytesReceived < 1048576 Then
                BytesReceived = BytesReceived / 1024
                BytesReceivedUnit = "KB"
            ElseIf BytesReceived >= 1048576 Then
                BytesReceived = BytesReceived / 1048576
                BytesReceivedUnit = "MB"
            ElseIf BytesReceived >= 1073741824 Then
                BytesReceived = BytesReceived / 1073741824
                BytesReceivedUnit = "GB"
            End If
            
            If BytesSent < 1024 Then
                BytesSentUnit = "Bytes"
            ElseIf BytesSent >= 1024 And BytesSent < 1048576 Then
                BytesSent = BytesSent / 1024
                BytesSentUnit = "KB"
            ElseIf BytesSent >= 1048576 Then
                BytesSent = BytesSent / 1048576
                BytesSentUnit = "MB"
            ElseIf BytesSent >= 1073741824 Then
                BytesSent = BytesSent / 1073741824
                BytesSentUnit = "GB"
            End If
        
            strvalues = Chr(34) & ClientIP & Chr(34) & "," _
            & Chr(34) & ImportDate & Chr(34) & "," _
            & Chr(34) & ReportDate & Chr(34) & "," _
            & Chr(34) & Requests & Chr(34) & "," _
            & Chr(34) & PageViews & Chr(34) & "," _
            & Chr(34) & BrowseTime & Chr(34) & "," _
            & Chr(34) & BrowseTimeUnit & Chr(34) & "," _
            & Chr(34) & TotalBytes & Chr(34) & "," _
            & Chr(34) & TotalBytesUnit & Chr(34) & "," _
            & Chr(34) & BytesReceived & Chr(34) & "," _
            & Chr(34) & BytesReceivedUnit & Chr(34) & "," _
            & Chr(34) & BytesSent & Chr(34) & "," _
            & Chr(34) & BytesSentUnit & Chr(34)

            strSQL = "INSERT INTO tblUsage (ClientIP,ImportDate,ReportDate,Requests,PageViews,BrowseTime,BrowseTimeUnit,TotalBytes,TotalBytesUnit,BytesReceived,BytesReceivedUnit,BytesSent,BytesSentUnit) "
            strSQL = strSQL & "VALUES(" & strvalues & ") "
            'strSQL = strSQL & "VALUES(" & Chr(34) & ClientIP & "," & ImportDate & Chr(34) & "," & Chr(34) & ReportDate & Chr(34) & "," & Requests & "," & PageViews & "," & BrowseTime & "," & Chr(34) & BrowseTimeUnit & Chr(34) & "," & TotalBytes & "," & Chr(34) & TotalBytesUnit & Chr(34) & "," & BytesReceived & "," & Chr(34) & BytesReceivedUnit & Chr(34) & "," & BytesSent & "," & Chr(34) & BytesSentUnit & Chr(34) & ") "
            Debug.Print strSQL
            
            'Execute INSERT statement to append this record to Access table
            If RowCount > 1 Then
                proddb1.Execute strSQL
            End If
            
            'If RowCount = 1 Then
            '    MsgBox strvalues
            'End If

            RecordCount = RecordCount + 1
            RowCount = RowCount + 1
            'Else: Exit Do
        'End If
    Next Row
    'Loop

    MsgBox RecordCount & " Records Imported"

    Close #f

    proddb1.Close

End Sub
0

Under your command button -

Call USED 'will invoke the function

Have some more beer while you're at it. I'm on number 3 already being Friday and half past five here by me....;)

0

Just missed your second post.

Everything is suppose to go under the "USED" function. It is there that the records will be imported using the loop.

Under your "cmdImport_Click" event, "Call USED"

This post has been going on for nearly 2 weeks and we are getting nowhere. I will reproduce the entire thing and re write a much simpler solution. What I need from you is:

attached .csv/xls file where I will be getting my data from
Database Table name and field names
Database name
Attached .xls/.csv where data must be copied to

I'll use the code as above and paste the solution with comments tomorrow sometime (depending on the hangover status!!!!).

0

Just missed your second post.

Everything is suppose to go under the "USED" function. It is there that the records will be imported using the loop.

Under your "cmdImport_Click" event, "Call USED"

This post has been going on for nearly 2 weeks and we are getting nowhere. I will reproduce the entire thing and re write a much simpler solution. What I need from you is:

attached .csv/xls file where I will be getting my data from
Database Table name and field names
Database name
Attached .xls/.csv where data must be copied to

I'll use the code as above and paste the solution with comments tomorrow sometime (depending on the hangover status!!!!).

Understood, thanks...I've zipped all the relevant files, and attached the zip file here.

Enjoy your weekend!

0

Ok, this is what I have thus far. I had not had much time to test, but all seems fine now.

I have replaced most of your code, so make a backup before replacing it with the following -

In you module, delete all and paste the following...

Public Sub OpenExcel()

'This will open the excell sheet for viewing
Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")

xlApp.Workbooks.Open CurrentProject.Path & "\IPStatistics.xls", True, False
xlApp.Visible = True 'Or false ...

Set xlApp = Nothing
End Sub

Public Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure can be edited as you see fit ...

Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim r As Long
Dim xID As Integer, xRows As Integer
    ' connect to the Access database
    Set cn = New ADODB.Connection
    
    'GetCn cn, rs, "SELECT * FROM tblUsage", , "", ""
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=" & CurrentProject.Path & "\internet_statistics.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM tblUsage", cn, adOpenKeyset, adLockOptimistic
    ' all records in a table
    
    r = 3 ' the start row in the worksheet
    xID = 1 'First record in database
    
    Dim xlApp As Excel.Application
    Set xlApp = CreateObject("Excel.Application")

    xlApp.Workbooks.Open CurrentProject.Path & "\IPStatistics.xls", True, False
    xlApp.Visible = False

    'Set xlApp = Nothing
    xRows = xlApp.WorksheetFunction.CountA("A:G")
    
    Do While Len(xlApp.Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("ID") = xID + 1
            .Fields("ClientIP") = xlApp.Range("A" & r).Value
            .Fields("ImportDate") = DateValue(Now)
            .Fields("ReportDate") = DateValue(Now) 'Forms![Detail]![txtDate].Value
            .Fields("Requests") = xlApp.Range("B" & r).Value
            .Fields("PageViews") = xlApp.Range("C" & r).Value
            .Fields("BrowseTime") = xlApp.Range("D" & r).Value
            
            Dim BrowseTimeUnit As String, BrowseTime As String
            
            BrowseTime = xlApp.Range("D" & r).Value
            
            If BrowseTime < 60 Then
                BrowseTimeUnit = BrowseTime & " Mins"
            ElseIf BrowseTime >= 60 And BrowseTime < 1440 Then
                BrowseTime = BrowseTime / 60
                BrowseTimeUnit = BrowseTime & " Hrs"
            ElseIf BrowseTime >= 1440 Then
                BrowseTime = BrowseTime / 1440
                BrowseTimeUnit = BrowseTime & " Days"
            End If
            
            .Fields("BrowseTimeUnit") = BrowseTimeUnit
            
            .Fields("TotalBytes") = xlApp.Range("E" & r).Value
            
            Dim TotalBytesUnit As String, TotalBytes As String
            
            TotalBytes = xlApp.Range("E" & r).Value
            
            If TotalBytes < 1024 Then
                TotalBytesUnit = TotalBytes & "Bytes"
            ElseIf TotalBytes >= 1024 And TotalBytes < 1048576 Then
                TotalBytes = TotalBytes / 1024
                TotalBytesUnit = TotalBytes & "KB"
            ElseIf TotalBytes >= 1048576 Then
                TotalBytes = TotalBytes / 1048576
                TotalBytesUnit = TotalBytes & "MB"
            ElseIf TotalBytes >= 1073741824 Then
                TotalBytes = TotalBytes / 1073741824
                TotalBytesUnit = TotalBytes & "GB"
            End If
            
            .Fields("TotalBytesUnit") = TotalBytesUnit
            
            .Fields("BytesReceived") = xlApp.Range("F" & r).Value
            
            Dim BytesReceivedUnit As String, BytesReceived As String
            
            TotalBytes = xlApp.Range("F" & r).Value
            
            If BytesReceived < 1024 Then
                BytesReceivedUnit = BytesReceived & "Bytes"
            ElseIf BytesReceived >= 1024 And BytesReceived < 1048576 Then
                BytesReceived = BytesReceived / 1024
                BytesReceivedUnit = BytesReceived & "KB"
            ElseIf BytesReceived >= 1048576 Then
                BytesReceived = BytesReceived / 1048576
                BytesReceivedUnit = BytesReceived & "MB"
            ElseIf BytesReceived >= 1073741824 Then
                BytesReceived = BytesReceived / 1073741824
                BytesReceivedUnit = BytesReceived & "GB"
            End If
            
            .Fields("BytesReceivedUnit") = BytesReceivedUnit
            
            .Fields("BytesSent") = xlApp.Range("G" & r).Value
            
            Dim BytesSentUnit As String, BytesSent As String
            
            BytesSent = xlApp.Range("G" & r).Value
            
            If BytesSent < 1024 Then
                BytesSentUnit = BytesSent & "Bytes"
            ElseIf BytesSent >= 1024 And BytesSent < 1048576 Then
                BytesSent = BytesSent / 1024
                BytesSentUnit = BytesSent & "KB"
            ElseIf BytesSent >= 1048576 Then
                BytesSent = BytesSent / 1048576
                BytesSentUnit = BytesSent & "MB"
            ElseIf BytesSent >= 1073741824 Then
                BytesSent = BytesSent / 1073741824
                BytesSentUnit = BytesSent & "GB"
            End If
            
            .Fields("BytesSentUnit") = BytesSentUnit
            
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

In you form code, the following...

Private Sub GetReportDate()

Dim xMonth As String, xYear As String, ReportDate As String

With Me.ReportYear
 If ReportMonth.ListIndex < 0 Then
  MsgBox "Please select a month from the list", vbOKOnly + vbInformation, "No Month Selected"
  
  Exit Sub
    Else
Select Case ReportMonth.ListIndex

Case Is = 0
xMonth = "01"

Case Is = 1
xMonth = "02"

Case Is = 2
xMonth = "03"

Case Is = 3
xMonth = "04"

Case Is = 4
xMonth = "05"

Case Is = 5
xMonth = "06"

Case Is = 6
xMonth = "07"

Case Is = 7
xMonth = "08"

Case Is = 8
xMonth = "09"

Case Is = 9
xMonth = "10"

Case Is = 10
xMonth = "11"

Case Is = 11
xMonth = "12"
End Select

xYear = ReportYear.Value

ReportDate = "01\" & xMonth & "\" & xYear

txtDate.Visible = True
txtDate.SetFocus
txtDate.Text = ReportDate
cmdImport.SetFocus
txtDate.Visible = False
End If
End With
End Sub

Private Sub cmdImport_Click()

Call GetReportDate
Call ADOFromExcelToAccess
End Sub

Private Sub cmdSelect_Click()
    
Call OpenExcel
End Sub
Private Sub Form_Load()

Dim CurrentYear As Integer
Dim i As Integer
 
 CurrentYear = Format(Date, "yyyy")
 
Me.cboYear.RowSourceType = "Value List"
 
 i = 5
 For y = 1 To 5
  Me.cboYear.AddItem CurrentYear - i
 i = i - 1
 Next y
 
i = 0
 For y = 1 To 6
  Me.cboYear.AddItem CurrentYear + i
 i = i + 1
 Next y
 
End Sub

The code is in VBA and not VB6, just for interest sake;)

0

Definitely good to know what language I'm working in, thanks! (I kind of just got thrown into this project).

I put your code in place of mine, however, and i'm still getting a few errors. The first one, "Compile error: Method or data member not found", with line 80, element "cboYear" highlighted, I resolved by renaming the Year drop-down menu in the form from "ReportYear" to "cboYear" - as that's what I gathered you meant.

The next error, I wasn't so sure of - this one, "Compile Error: User-defined type not defined", highlights the Dim declaration "xlApp As Excel.Application" on line 4 of your module code. It doesn't seem to like the type of Dim being declared.

Either way, thank you for your work in evaluating and correcting my code. I'm sure we're close to making this thing work!

0

ah...found it, and added the reference - ran the form again, but got an error, after clicking on the browse button:

Run-time error '1004'

'C:\Documents and Settings\...IPStatistics.xls' could not be found.'

Which raises a couple questions:

* The files I'm looking to import are CSV files, not XLS
* The import file name and location will likely not be the same from import to import, which is why I was expecting to bring up a file-browse window, so the user can locate the file. This script seems to be looking for a file of a specific name in a specific location, every time the "Browse..." button is clicked.

1

Just add the browsed path to a string and open the file from there using the string after the user selected the path. Also change the .xls part to.csv.

I'll have to look at the code again tomorrow, will let you know why an error was returned.:)

Votes + Comments
Thanks for assisting a total VBA newb in his quest to import CSVs into Access! Cheers!
0

Good news!

After banging my head against the wall a few times (not to mention, the vendor changing up the data format of the CSVs they send me, causing me to have to tear down pretty much the entire script, and rebuild it from the ground up), I've been able to get the import script to work!

I know this thread has been dead a while, but I wanted to at least provide some closure, and post the (working and cleaned up) code, so others might find it useful, not to mention to thank AndreRet for his invaluable help in working towards a solution!

Private Sub cmdImport_Click()
      
      'declare your database and CSV variables
      Dim proddb1 As Database
      Dim CSVFile
      
      'declare your script variables
      Dim f As Integer
      Dim strvalues As String
      Dim strSQL As String
      Dim RecordCount As Integer
      Dim RowCount As Integer
      
      'declare your database field variables
      Dim ID As String
      Dim ClientIP As String
      Dim Requests As String
      Dim BrowseTime As String
      Dim TotalBytes As String
      Dim BytesReceived As String
      Dim BytesSent As String
      Dim ImportDate As Date
      Dim ReportDate As Date
      Dim PageViews As String
      
      'Make sure they enter a file before continuing
      If IsNull(Me.txtFileName) Or Len(Me.txtFileName & "") = 0 Then
            MsgBox "Please Select a CSV File Before Proceeding"
            Me.cmdSelect.SetFocus
      End If
      
      'Assign values to the Dims
      CSVFile = Me.txtFileName
      ReportDate = Me!ReportMonth & "/01/" & Me!ReportYear
      ImportDate = DateValue(Now)
      f = FreeFile
      RowCount = 1
      RecordCount = 0
      
      'Display import and report dates (for debugging purposes)
      'MsgBox "Import Date: " & ImportDate
      'MsgBox "Report Date: " & ReportDate
      
      Set proddb1 = OpenDatabase("internet_statistics.mdb")
      
      'Open the CSV file
      Open CSVFile For Input As f
      
      'Walk through the rows and perform the operation in the loop,
      'breaking out of the loop when you hit the end of the file
      Do Until EOF(f)
            Input #f, ClientIP, Requests, PageViews, BrowseTime, TotalBytes, BytesReceived, BytesSent
          
            strvalues = Chr(34) & ClientIP & Chr(34) & "," _
            & Chr(34) & Requests & Chr(34) & "," _
            & Chr(34) & PageViews & Chr(34) & "," _
            & Chr(34) & BrowseTime & Chr(34) & "," _
            & Chr(34) & TotalBytes & Chr(34) & "," _
            & Chr(34) & BytesReceived & Chr(34) & "," _
            & Chr(34) & BytesSent & Chr(34) & "," _
            & Chr(34) & ImportDate & Chr(34) & "," _
            & Chr(34) & ReportDate & Chr(34)
            
            strSQL = "INSERT INTO tblUsage (ClientIP,Requests,PageViews,BrowseTime,TotalBytes,BytesReceived,BytesSent,ImportDate,ReportDate) "
            strSQL = strSQL & "VALUES(" & strvalues & ") "
            
            'only execute the insert if you're working on row 2 or greater (basically, ignore the column labels row)
            If RowCount > 1 Then
                  'Execute INSERT statement to append this record to Access table
                  proddb1.Execute strSQL
                  RecordCount = RecordCount + 1
            
                  'display SQL Insert string for the second row (for debugging purposes)
                  'If RowCount = 2 Then
                        'MsgBox strSQL
                  'End If
            End If
          
            'increment row count up by one
            RowCount = RowCount + 1
        'Do it all again!
        Loop
        
        'Close the CSV
        Close f
      
        'Display the total number of records imported
        MsgBox RecordCount & " Records Imported"

        'Close the database file
        proddb1.Close
      
      End Sub
      
      Private Sub cmdSelect_Click()
      
      Dim strStartDir As String
      Dim strFilter As String
      Dim lngFlags As Long
      
      ' Lets start the file browse from our current directory
      strStartDir = CurrentDb.Name
      strStartDir = Left(strStartDir, Len(strStartDir) - Len(Dir(strStartDir)))
      
      strFilter = ahtAddFilterItem(strFilter, _
      "CSV Files (*.csv)", "*.csv")
      Me.txtFileName = ahtCommonFileOpenSave(InitialDir:=strStartDir, _
      Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
      DialogTitle:="Select CSV File to Import")
      
      End Sub
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.