Need help rewriting a Macro for Importing Excel file instead of Text file into Access

[adibakale]

I have a VBA macro in Access for importing text files into the database. I would like to use this macro to import Excel files.

The VBA Macro performs the following:

- There is a form button attached to the macro to import the text file
- A Temporary table is created to import the data
- The chosen text file is then opened
- Each entry in the text file is 3 lines, so the macro creates each record with the data from the 3 lines to create each single record before moving on to the next
- Once the macro has determined each record from each group of 3 lines in the text file, it then puts the data into the appropriate fields in the temporary table it created
- From the temporary table, it then appends the data into the master table

The excel file that I need to import is very similar to the text file, the data for each entry is in 3 rows(lines). Of course, since it is in excel, it is separated by cells unlike the text file.

Please let me know what details I need to provide (if any) for clarification. Thank you

---

Option Compare Database

Private Sub impReport_Click()
'Check naming convention for the OFAC Report and also location it is saved.
Dim txtBANK, txtOFAC, txtLINE, txtCARDNUM, txtNAME, txtADDRESS, txtCITY, txtSTATE, txtZIP, txtSOC, txtLISTNAME As String
Dim tblIMP, FDialog As Object

DoCmd.SetWarnings (False)
DoCmd.Hourglass (True)

Set FDialog = Application.FileDialog(3)
With FDialog
.InitialFileName = "J:\Prepaid Operations Documentation\Business Operations\Compliance\OFAC FILES"
.Title = "Select Text File"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Text Files", "*.txt"

' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
txtOFAC = .SelectedItems.Item(1)
End If
End With

If txtOFAC = "" Then
i = MsgBox("Import Failed. Please click " _
& vbCr & "'Import Tracker from FIS'" _
& vbCr & "to start over.", , "Import Failed")
Exit Sub
End If

'Creates the temporary import table.
DoCmd.RunSQL ("create table tmpOFAC(bank text, num text, chname text, address text, city text, state text, " _
& "zip text, soc text, listname text);")

'SECTION 1: This section opens the file for import that it was selected above.

Set tblIMP = CurrentDb.OpenRecordset("tmpOFAC")
Open txtOFAC For Input As #1
Do While Not EOF(1)

'The report breaks each entry into three lines. This IF block grabs a line from the report then checks for
'identifiers to determine which of the three lines it is looking at. Once determined it will enter
'relevent data into variables for import.
Line Input #1, txtLINE
If Trim(Mid(txtLINE, 42, 25)) = "OFAC SUSPECT REPORT" Then
If Left(txtLINE, 1) = "0" Then
txtBANK = Trim(Mid(txtLINE, 3, 3))
Else
txtBANK = Trim(Mid(txtLINE, 4, 3))
End If
ElseIf IsNumeric(Left(txtLINE, 16)) = True Then
txtCARDNUM = Trim(Left(txtLINE, 16))
txtNAME = Trim(Mid(txtLINE, 21, 39))
txtADDRESS = Trim(Mid(txtLINE, 59, 42))
txtCITY = Trim(Mid(txtLINE, 101, 22))
txtSTATE = Trim(Mid(txtLINE, 124, 2))
txtZIP = Trim(Mid(txtLINE, 128, 5))
x = 1
ElseIf x = 1 Then
txtSOC = Trim(Mid(txtLINE, 27, 12))
x = 0
ElseIf Trim(Mid(txtLINE, 16, 10)) = "LIST NAME:" Then
txtLISTNAME = Trim(Mid(txtLINE, 26, 100))
x = 2
End If

'Once the if block above determines that it has read the third line of the entry and gathered required data
'it sets x = 2. By doing this it tells the program that it is ready to add the entry to the temporary OFAC
'table.
If x = 2 Then

'Adds the record to tmpOFAC.
With tblIMP
.AddNew
!bank = txtBANK
!num = txtCARDNUM
!Chname = txtNAME
!Address = txtADDRESS
!City = txtCITY
!State = txtSTATE
!Zip = txtZIP
!soc = txtSOC
!Listname = txtLISTNAME
.Update
.Bookmark = .LastModified
End With
x = 0
End If
Loop
Close #1
tblIMP.Close
'END OF SECTION 1

'Update and appoend imported data to tblOFAC.
DoCmd.RunSQL ("UPDATE tblOFAC INNER JOIN tmpOFAC ON (tblOFAC.ListName = tmpOFAC.listname) AND " _
& "(tblOFAC.CardNumber = tmpOFAC.num) SET tblOFAC.Complete = No, tblOFAC.bank = [tmpOFAC]![bank], " _
& "tblOFAC.Chname = [tmpOFAC]![chname], tblOFAC.Social = [tmpOFAC]![soc], tblOFAC.Address = [tmpOFAC]![address], " _
& "tblOFAC.City = [tmpOFAC]![city], tblOFAC.State = [tmpOFAC]![state], tblOFAC.Zip = [tmpOFAC]![zip] " _
& "WHERE ((([tmpOFAC]![chname])<>[tmpOFAC]![chname])) OR ((([tblOFAC]![Social])<>[tmpOFAC]![soc])) " _
& "OR ((([tblOFAC]![address])<>[tmpOFAC]![address])) OR ((([tblOFAC]![city])<>[tmpOFAC]![city])) " _
& "OR ((([tblOFAC]![state])<>[tmpOFAC]![state])) OR ((([tblOFAC]![zip])<>[tmpOFAC]![zip]));")
DoCmd.RunSQL ("INSERT INTO tblOFAC ( Bank, CardNumber, chname, address, city, state, zip, Social, listname ) " _
& "SELECT tmpOFAC.bank, tmpOFAC.num, tmpOFAC.chname, tmpOFAC.address, tmpOFAC.city, tmpOFAC.state, tmpOFAC.zip, " _
& "tmpOFAC.soc, tmpOFAC.listname FROM tmpOFAC;")

DoCmd.RunSQL ("drop table tmpOFAC;")

MsgBox ("Complete.")

DoCmd.SetWarnings (True)
DoCmd.Hourglass (False)
End Sub

---