Solved

Append File Name from Excel file into table

Posted on 2004-09-21
5
388 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 100 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 150 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

626 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