Append File Name from Excel file into table

I need a little bit of help to complete my project.  I want to know is there any way to append a excel file name into a table.  Currently I have code that is append all data from the excel file into a table.  I want to know is there a way to append the file name to each record.  You help will be greatly appreciated.  Here is my code below.

Option Compare Database


Public Function LoadData()

Dim xl As Excel.Application
Dim objWkBook As Excel.Workbook
Dim objSheet As Worksheet
Dim strWBname As String
Dim strWSname As String
Dim i As Integer, j As Integer
Dim intCount As Integer
Dim strFilename, tblName As String, sFileName As String
Dim Msg As String

Msg = "Voided File Name"

Dim fs As Object
Set fs = Application.FileSearch
With fs
    .LookIn = "\\C:Test"
    .fileName = "*.xls"
    .SearchSubFolders = True          'this will search the sub folders
    If .Execute > 0 Then
    For j = 1 To .FoundFiles.Count
    strFilename = .FoundFiles(j)

sFileName = Dir(.FoundFiles(j))
tblName = Mid(Left$([sFileName], InStr(1, [sFileName], ".") - 1), 1)

Set xl = New Excel.Application
Set objWkBook = GetObject("" & strFilename & "")
strWBname = objWkBook.Name
'intCount = (objWkBook.Sheets.Count) - 1
intCount = (objWkBook.Sheets.Count)
 

For i = 1 To (intCount)
Set objSheet = objWkBook.Sheets(i)
  strWSname = objSheet.Name
 
'If strWSname <> "Checklist" And Len("" & tblName & strWSname & "") < 65 Then
   
   'Debug.Print i; intCount; strWSname  'use this for testing on the immediate window
     '   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     '   "" & tblName & strWSname & "", strFilename, True, "" & strWSname & "!"
   
   If strWSname Like "Payee Note*" And Len("" & tblName & strWSname & "") < 65 Then
   
   Debug.Print i; intCount; strWSname  'use this for testing on the immediate window
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "Payee Note", strFilename, True, "" & strWSname & "!"
       
   ElseIf strWSname Like "Billing*" And Len("" & tblName & strWSname & "") < 65 Then
   
   Debug.Print i; intCount; strWSname  'use this for testing on the immediate window
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "Billing", strFilename, True, "" & strWSname & "!"
   
   ElseIf strWSname Like "Payment*" And Len("" & tblName & strWSname & "") < 65 Then
   
   Debug.Print i; intCount; strWSname  'use this for testing on the immediate window
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "Payment", strFilename, True, "" & strWSname & "!"
       
       
  ElseIf strWSname Like "Delinquency*" And Len("" & tblName & strWSname & "") < 65 Then
   
   Debug.Print i; intCount; strWSname  'use this for testing on the immediate window
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "Delinquency", strFilename, True, "" & strWSname & "!"
       
       
  ElseIf strWSname Like "Remittance - Detail*" And Len("" & tblName & strWSname & "") < 65 Then
   
   Debug.Print i; intCount; strWSname  'use this for testing on the immediate window
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "Remittance - Detail", strFilename, True, "" & strWSname & "!"
       
       
  ElseIf strWSname Like "Parcel*" And Len("" & tblName & strWSname & "") < 65 Then
   
   Debug.Print i; intCount; strWSname  'use this for testing on the immediate window
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "Parcel", strFilename, True, "" & strWSname & "!"
       
  ElseIf strWSname Like "Jurisdiction Payee Form*" And Len("" & tblName & strWSname & "") < 65 Then
   
   Debug.Print i; intCount; strWSname  'use this for testing on the immediate window
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "Jurisdiction Payee Form", strFilename, True, "" & strWSname & "!"
       
       

   
   
   ElseIf strWSname Like "Supplemental Billing*" And Len("" & tblName & strWSname & "") < 65 Then
   
   Debug.Print i; intCount; strWSname  'use this for testing on the immediate window
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "Supplemental Billing", strFilename, True, "" & strWSname & "!"
        Debug.Print i; intCount; strWSname  'use this for testing
       
    ElseIf strWSname Like "Supplemental Payment*" And Len("" & tblName & strWSname & "") < 65 Then
   
   Debug.Print i; intCount; strWSname  'use this for testing on the immediate window
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "Supplemental Billing", strFilename, True, "" & strWSname & "!"
        Debug.Print i; intCount; strWSname  'use this for testing
       
    ElseIf strWSname Like "Supplemental Payee Parcel*" And Len("" & tblName & strWSname & "") < 65 Then
   
   Debug.Print i; intCount; strWSname  'use this for testing on the immediate window
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "Supplemental Billing", strFilename, True, "" & strWSname & "!"
        Debug.Print i; intCount; strWSname  'use this for testing
       
       
    Else: Debug.Print i; intCount; strWSname; Msg 'use this for testing on the immediate window
   End If
   
Next i

objWkBook.Close False
xl.Application.Quit


Set objWkBook = Nothing
Set objSheet = Nothing
Set xl = Nothing

Next j

End If

End With

GetWorkbook_Exit:
   Exit Function
GetWorkbook_Err:
    Debug.Print i; intCount; strWSname
   MsgBox Err.Number & " " & Err.Description
   Resume GetWorkbook_Exit

End Function
jedwards2Asked:
Who is Participating?
 
david251Commented:
jedwards2,

you could try something like this after each transfer text:

Sub subAlter(strTbl as string, strFileName as string)
    Dim strSQL As String
    strSQL = "Alter Table " & strTbl & " add myFileName varchar(255)"
    CurrentDb.Execute strSQL
    strSQL = "Update " & strTbl & " set myFileName='" & strFileName & "'"
    CurrentDb.Execute strSQL
End Sub

It adds a field and updates it to the File name

good luck,

-David251
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Without looking at your SQL dump, the best way to do this would be DoCmd.TransferSpreadsheet, where the file name is a variable in code, then run an append query that updates all ImportTable.FileName fields to = that variable.

Hope this helps.
-Jim
0
 
jedwards2Author Commented:
That is the problem.  I don't have any SQL related to the import.  As you can see there can be multiple file names and multiple sheet names which means the field names are not consistent.  I am dumping the whole excel file into the table.  Should I change the way I am mporting the spreadsheet and use SQL?  I don't completely understand how to do that, but can try.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If the file names are not consistent: names, columns, and data types, then how exactly do you expect to automate this process?  This is a huge amount of import errors waiting to happen.  You need to resolve this with whomever gives you these files before writing code.

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Looks like you have a couple of answers to your original question.  Please award points and close the question.  Thanks in advance.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.