Importing Multiple Excel files into Access with file path

[vbaddiction]

I have seen threads out there about how to import multiple files that are formatted the same into one table in Access. That is what I am trying to do but I also need to import the filename and path into a field in the table. Can someone post some code to do those two things together?

Much appreciated!

 

[Joe4]

Welcome to the Board!

Can you post the code that you have now for importing the file, and we'll see if we can incorporate that part?

 

[vbaddiction]

Function UploadProcess()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Dim strFile As String<o></o>
strFile = Browseforfile<o></o>
'Dim xlApp As Object 'Excel.Application<o></o>
<o> </o>
<o></o>
DoCmd.RunSQL "DELETE * From uploadTEMPORARYtemplate;"<o></o>
<o> </o>
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "UploadTemporarytemplate", strFile, True<o></o>
<o> </o>
'DoCmd.OpenQuery "TemporaryTableQuery"<o></o>
<o> </o>
DoCmd.RunSQL "INSERT INTO GretchenArchiveTemplates ( [Original/Submitted], Account, [Account Description], [Cost Center], [Cost Center Description], PersonResponsible, [2011 P01], [2011 P02], [2011 P03], [2011 P04], [2011 P05], [2011 P06], [2011 P07], [2011 P08], [2011 P09], [2011 P10], [2011 P11], [2011 P12], [FY 2011], NYPBUD1, NYPBUD2, NYPBUD3, NYPBUD4, NYPBUD5, NYPBUD6, NYPBUD7, NYPBUD8, NYPBUD9, NYPBUD10, NYPBUD11, NYPBUD12, [FY 2012], FileName, [Date/Time] ) " & _<o></o>
"SELECT uploadTEMPORARYtemplate.[Original/Submitted], uploadTEMPORARYtemplate.Account, uploadTEMPORARYtemplate.[Account Description], uploadTEMPORARYtemplate.[Cost Center], uploadTEMPORARYtemplate.[Cost Center Description], uploadTEMPORARYtemplate.PersonResponsible, uploadTEMPORARYtemplate.[2011 P01], uploadTEMPORARYtemplate.[2011 P02], uploadTEMPORARYtemplate.[2011 P03], uploadTEMPORARYtemplate.[2011 P04], uploadTEMPORARYtemplate.[2011 P05], uploadTEMPORARYtemplate.[2011 P06], " & _<o></o>
"uploadTEMPORARYtemplate.[2011 P07] , uploadTEMPORARYtemplate.[2011 P08], uploadTEMPORARYtemplate.[2011 P09], uploadTEMPORARYtemplate.[2011 P10], uploadTEMPORARYtemplate.[2011 P11], uploadTEMPORARYtemplate.[2011 P12], uploadTEMPORARYtemplate.[FY 2011], uploadTEMPORARYtemplate.NYPBUD1, uploadTEMPORARYtemplate.NYPBUD2, uploadTEMPORARYtemplate.NYPBUD3, uploadTEMPORARYtemplate.NYPBUD4, uploadTEMPORARYtemplate.NYPBUD5, uploadTEMPORARYtemplate.NYPBUD6, uploadTEMPORARYtemplate.NYPBUD7, uploadTEMPORARYtemplate.NYPBUD8, uploadTEMPORARYtemplate.NYPBUD9, " & _<o></o>
"uploadTEMPORARYtemplate.NYPBUD10, uploadTEMPORARYtemplate.NYPBUD11, uploadTEMPORARYtemplate.NYPBUD12, uploadTEMPORARYtemplate.[FY 2012], '" & strFile & "' AS FileName, Now() AS [Date/Time] " & _<o></o>
"FROM uploadTEMPORARYtemplate;"<o></o>
<o> </o>
<o> </o>
<o> </o>
MsgBox "Complete."<o></o>
<o> </o>
<o> </o>
End Function<o></o>

 

[Joe4]

So, are you looking to import a list of files from a table, or import a bunch of files and write their file names to a file?

What is the logic for determining the file to be imported?
Are they all in the same directory?

 

[vbaddiction]

So, are you looking to import a list of files from a table, or import a bunch of files and write their file names to a file?

What is the logic for determining the file to be imported?
Are they all in the same directory?

I am looking to import a bunch of Excel files from the same directory to the same table in Access. I want to import the filename and path to each record.

Basically, I want to consolidate a bunch of records from a bunch or files and be able to see which file each record came from.

 

[Joe4]

OK, here is one way to approach that.

Add a field to your table structure for "FileName".
In your VBA code, you will then want a loop to import the files that you want from your directory.
Within that loop, you will first:
- Import the file into your table
- Run SQL code that will update this FileName field for all records which the value is Null (so as not ot overwrite the file name for the previous records imported) with the name of the file.

 

[Joe4]

Here is some sample code I came up with that you should be able to adapt to your situation.

I am assuming the table I am importing into is named "MyTable", and the file field name I am updating is named "MyFileName". I am calling the code from a command button on a Form I have.

Private Sub Command0_Click()
 
    Dim strPath As String
    Dim strFile As String
    Dim strSQL As String
 
'   Set file directory to load fiels from
    strPath = "G:\C\Test\"
'   Tell it to import all Excel files
    strFile = Dir(strPath & "*.xls*")
 
'   Start loop
    Do While strFile <> ""
        ' Import file
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "MyTable", strPath & strFile, True
        ' Run update query
        DoCmd.SetWarnings False
        strSQL = "UPDATE MyTable SET MyFileName=" & Chr(34) & strPath & strFile & Chr(34) & "WHERE MyFileName IS NULL"
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        ' Loop to next file in directory
        strFile = Dir
    Loop
 
End Sub

 

[vbaddiction]

Thanks Joe, I will try this. In the meantime if I want to import from a specific tab called "BudgetTemplate" that is the first tab in each file and from cell A21 to the last datacell in that tab, what would the code look like using the code you just sent?

 

[Joe4]

If it were me, I would probably create an Excel macro to dynamcially name all the ranges in each file, and use the named range in the TransferSpreadsheet command.

However, take a look at this thread here, specifically Denis's (SydneyGeek's) replies: http://www.mrexcel.com/forum/showthread.php?t=227965

 

[vbaddiction]

Joe thanks, I created a macro and it worked! Now how do I import a named range into MyTable above?