Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Append File Name from Excel file into table

Posted on 2004-09-21
5
Medium Priority
?
391 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 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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