Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Append File Name from Excel file into table

Posted on 2004-09-21
5
Medium Priority
?
399 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:jedwards2
  • 3
5 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 12113212
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
 

Author Comment

by:jedwards2
ID: 12113313
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 12113395
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
 
LVL 19

Accepted Solution

by:
david251 earned 300 total points
ID: 12113472
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
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 450 total points
ID: 12166795
Looks like you have a couple of answers to your original question.  Please award points and close the question.  Thanks in advance.  -Jim
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question