Hello all.
I have been out of the mix for a while. Trying to use VBA to import a text file. I have used this code in the past, but can't seem to get it to work... I get 'Compile error: Variable not defined'.
I thought it was because I was missing the Microsoft DAO Reference, but when I try to select that I get 'Name conflicts with existing module, project, or object library'. I don't see any conflict with the name...so not sure what to do....
Thanks for your help.
Here is my code:
Option Compare Database
Option Explicit
Public Const cDailyActivityReport As String = "T:\Visa\Visa Month End\Daily Activity Data\CD 021 Data.txt"
Public Const cTITLE2 As String = "Daily Activity Report"
Public Function ImportEFile()
Dim myCheck
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strInfFile As String, intInFile As Integer, strFileData As String
Dim strCardnumber As String
Dim strTranCode As String
Dim strAmount As String
Dim strReferenceNumber As String
Dim strTransactionDate As String
Dim strFT As String
On Error GoTo ImportFile_Err
strInFile = cDailyActivityReport
DoEvents
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_CD-021 Data", dbOpenDynaset)
intInFile = FreeFile
Open strInFile For Input As intInFile
SysCmd acSysCmdInitMeter, "Importing Data File", LOF(intInFile)
Do Until EOF(intInFile)
SysCmd acSysCmdUpdateMeter, (Loc(intInFile) * 128)
Line Input #intInFile, strFileData
If StrComp(Mid(strFileData, 8, 4), "XXXX") = 0 Then 'Or StrComp(Mid(strFileData, 16, 3), " B ") = 0 Then
strCardnumber = Mid(strFileData, 8, 16)
strTranCode = Mid(strFileData, 29, 3)
strAmount = Mid(strFileData, 35, 15)
strReferenceNumber = Mid(strFileData, 52, 17)
strTransactionDate = Mid(strFileData, 71, 6)
strFT = Mid(strFileData, 79, 2)
End If
With rst
.AddNew
!CardNumber = strCardnumber
!TranCode = strTranCode
!Amount = strAmount
!ReferenceNumber = strReferenceNumber
!TransactionDate = strTransactionDate
!FT = strFT
.Update
End With
rst.Close
dbs.Close
ImportFile_Exit:
SysCmd acSysCmdClearStatus
Close intInFile
Set dbs = Nothing
Set rst = Nothing
'Set cd = Nothing
Exit Function
ImportFile_Err:
'MsgBox Err & " " & Err.Description & vbLf & "Job Name: ImportFile" & vbLf & cSUPPORT, vbCritical
Resume ImportFile_Exit
End Function
I have been out of the mix for a while. Trying to use VBA to import a text file. I have used this code in the past, but can't seem to get it to work... I get 'Compile error: Variable not defined'.
I thought it was because I was missing the Microsoft DAO Reference, but when I try to select that I get 'Name conflicts with existing module, project, or object library'. I don't see any conflict with the name...so not sure what to do....
Thanks for your help.
Here is my code:
Option Compare Database
Option Explicit
Public Const cDailyActivityReport As String = "T:\Visa\Visa Month End\Daily Activity Data\CD 021 Data.txt"
Public Const cTITLE2 As String = "Daily Activity Report"
Public Function ImportEFile()
Dim myCheck
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strInfFile As String, intInFile As Integer, strFileData As String
Dim strCardnumber As String
Dim strTranCode As String
Dim strAmount As String
Dim strReferenceNumber As String
Dim strTransactionDate As String
Dim strFT As String
On Error GoTo ImportFile_Err
strInFile = cDailyActivityReport
DoEvents
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_CD-021 Data", dbOpenDynaset)
intInFile = FreeFile
Open strInFile For Input As intInFile
SysCmd acSysCmdInitMeter, "Importing Data File", LOF(intInFile)
Do Until EOF(intInFile)
SysCmd acSysCmdUpdateMeter, (Loc(intInFile) * 128)
Line Input #intInFile, strFileData
If StrComp(Mid(strFileData, 8, 4), "XXXX") = 0 Then 'Or StrComp(Mid(strFileData, 16, 3), " B ") = 0 Then
strCardnumber = Mid(strFileData, 8, 16)
strTranCode = Mid(strFileData, 29, 3)
strAmount = Mid(strFileData, 35, 15)
strReferenceNumber = Mid(strFileData, 52, 17)
strTransactionDate = Mid(strFileData, 71, 6)
strFT = Mid(strFileData, 79, 2)
End If
With rst
.AddNew
!CardNumber = strCardnumber
!TranCode = strTranCode
!Amount = strAmount
!ReferenceNumber = strReferenceNumber
!TransactionDate = strTransactionDate
!FT = strFT
.Update
End With
rst.Close
dbs.Close
ImportFile_Exit:
SysCmd acSysCmdClearStatus
Close intInFile
Set dbs = Nothing
Set rst = Nothing
'Set cd = Nothing
Exit Function
ImportFile_Err:
'MsgBox Err & " " & Err.Description & vbLf & "Job Name: ImportFile" & vbLf & cSUPPORT, vbCritical
Resume ImportFile_Exit
End Function