Solved

Append File Name from Excel file into table

Posted on 2004-09-21
5
349 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 65

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 65

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 65

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 your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now