Importing Folder of Excel Files

[monrosal]

Does anyone know how I can import a whole list of Excel files within a folder? I'm using the transerSpreadsheet method. I'm thinking that I may have to get the listing within the folder. Create an array. Then loop through each item in the array doing the transferSpreadsheet method. Does anyone know an easier way? Thanks

Ramon

 

[tlbroadbent]

Reading the names of files in the directory and importing them will require looping and use of the DIR() function. Here is some sample code adapted from one of my applications.

-------------
Dim FileName As String

FileName = Dir("C:\Exceldata\*.xls&quot ' find first file name

If FileName <> &quot;&quot; Then ' Check for file in directory
Do Until FileName = &quot;&quot; ' loop until no more files found

' Insert logic to TransferSpreadsheet

FileName = Dir() ' Get next file name
Loop
End If
-------------

Hope this helps. Let me know if you have any questions or problems with the code. Terry
------------------------------------
People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker

 

[monrosal]

Terry,

Thanks for the help, but everytime I specify the location it still looks in &quot;C:\My Documents\...&quot; . Why is that?

Ramon

 

[tlbroadbent]

Don't know why without seeing the code. Please post the code in your module.

Thanks, Terry
------------------------------------
People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker

 

[monrosal]

Dim fileName2 As String
Dim folderPath As String

folderPath = &quot;C:\ExcelFiles\*.xls&quot;
fileName2 = Dir(folderPath, vbDirectory)
Me.fileListing = fileName2

If fileName2 <> &quot;&quot; Then
Do Until fileName2 = &quot;&quot;

DoCmd.TransferSpreadsheet acImport, 8, _
&quot;Sales&quot;, fileName2, True, &quot;Sales!&quot;
DoCmd.TransferSpreadsheet acImport, 8, _
&quot;Profit&quot;, fileName2, True, &quot;Profit!&quot;

fileName2 = Dir()
Loop
End If

 

[tlbroadbent]

You'll need to include the path in the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acImport, 8, _
&quot;Sales&quot;, &quot;c:\excelfiles\&quot; & fileName2, True, &quot;Sales!&quot;
DoCmd.TransferSpreadsheet acImport, 8, _
&quot;Profit&quot;, &quot;c:\excelfiles\&quot; & fileName2, True, &quot;Profit!&quot;
Terry
------------------------------------
People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker