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