Importing a folders worth of excel work books into Acess

[chrisbrocco]

Is there a way (using VBA) to point to a folder and make access import all the files (which will be excel files) into a access a table.

Prefrably beable to say which colums it imports

 

[Swanson1962]

Hi - Haven't tried it yet, but how about this???
Lisa

'*************************
<TABLE dir=ltr border=0 cellSpacing=0 cellPadding=2 width=262><TBODY><TR><TD height=15 vAlign=center>Sub BatchImport()
</TD></TR><TR><TD height=15 vAlign=center>
</TD></TR><TR><TD height=15 vAlign=center>With
</TD></TR><TR><TD height=15 vAlign=center>Application.FileSearch
</TD></TR><TR><TD height=15 vAlign=center>.NewSearch
</TD></TR><TR><TD height=15 vAlign=center>.LookIn = "C:\MyData"
</TD></TR><TR><TD height=15 vAlign=center>.SearchSubFolders = False
</TD></TR><TR><TD height=15 vAlign=center>.FileType = msoFileTypeExcelWorkbooks
</TD></TR><TR><TD height=15 vAlign=center>
</TD></TR><TR><TD height=15 vAlign=center>If .Execute() > 0 Then 'Files found
</TD></TR><TR><TD height=31 vAlign=center>MsgBox "There were " & .FoundFiles.Count & " file(s) found."
</TD></TR><TR><TD height=15 vAlign=center>For i = 1 To .FoundFiles.Count
</TD></TR><TR><TD height=15 vAlign=center>'Transferspreadsheet
</TD></TR><TR><TD height=15 vAlign=center>Next i
</TD></TR><TR><TD height=15 vAlign=center>Else
</TD></TR><TR><TD height=15 vAlign=center>MsgBox "There were no files found."
</TD></TR><TR><TD height=15 vAlign=center>End If
</TD></TR><TR><TD height=15 vAlign=center>
</TD></TR><TR><TD height=15 vAlign=center>End With 'End Filesearch
</TD></TR><TR><TD height=15 vAlign=center>
</TD></TR><TR><TD height=15 vAlign=center>End Sub
</TD></TR></TBODY></TABLE>

 

[Swanson1962]

.

 

[jauntyangle]

Hello, I've got a similar issue, but know next to nothing about VBA/Access. I think my requirements are however simpler than the original posters.

I have multiple (thousands) of Excel spreadsheets which I need to combine into one report. Given the likely number of rows, it is likely to be too much for Excel, so would like to import them into an Access table.

The details:

It's a one-off task - so no need for future amendments/maintenance once it's done.

Each file has a different name.

Each Excel file has two tabs.

All of the files are stored (or can be stored) in a single folder.

I only want to import the data from one of the tabs (handily each of the data tabs I want is called "Sheet1").

All of the fields are (or should be) the same in each spreadsheet.

Excel and Access versions are 2003.

Will any of the solutions already posted achieve this, or would it have to be adapted? Is there any additional information I would need to provide?

Thanks in advance.

 

[jauntyangle]

Function ImportExcelFiles()
Dim Counter As Integer

With Application.FileSearch
.NewSearch
.LookIn = "\C:\ImportDir" 'change this to your actual directory
.SearchSubFolders = False 'set to True if you want to search subfolders too
FileName = "*.xls" 'changed HERE

If .Execute() > 0 Then 'files found
For Counter = 1 To .FoundFiles.Count 'loop through files
.FileName = .FoundFiles(Counter) 'set / get the file name
'Change the "ImportFile" part in the line below if you are using a different table name
'Note: 1 command for each worksheet. I have assumed they are Sheet1 etc.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", .FileName, False, "Sheet1!"'Changed HERE
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", .FileName, False, "Sheet1!"'Changed HERE
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", .FileName, True, "Sheet1!"'Changed HERE

DoEvents 'don't take over all of the PC resources
Next Counter
MsgBox "Import complete.", vbInformation, "Done"
Else 'files not found
MsgBox "There were no files found.", vbCritical, "Error"
End If
End With
End Function


just changed the $ to !

and .FileType = msoFileTypeAllFiles to FileName = "*.xls"

Works perfectly many thanks.

One addtional piece of help would be great. How could I tag the name of the workbook and/or sheet name to each record I import

Ok, I've used the code above and even changed the table name. There is the file in the directory and the file has a worksheet named "Sheet1".

However, when I try to run the module in Access I keep getting the following error:

Run-time error 3125. 'Sheet1$' is not a valid name. This also happens when I replace the ! with a $.

When I remove both the ! and the $, I then get a different error

Run-time error 3011. Microsoft Jet database engine could not find the object 'Sheet1'.

Could anyone explain what might be wrong. Is the syntax incorrect, or could there be a problem with the name of the worksheet tab?

Thanks in advance.

 

[jauntyangle]

Can anybody help? Thanks.

 

[SydneyGeek]

This section of the code imports 3 sheets.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", .FileName, False, "Sheet1!"'Changed HERE
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", .FileName, False, "Sheet1!"'Changed HERE
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", .FileName, True, "Sheet1!"'Changed HERE

If you are only importing Sheet1 there is no need to specify the sheet name. Try...

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", .FileName, False

Denis

 

[jauntyangle]

Thanks Sydney

In the workbooks I'm trying to import, there are 2 sheets, one called "Summary" which I don't need; the other called "Sheet1" which contains the data, which I do need.

The amendment above seems to only pick up the "Summary" sheet - which happens to be the first tab in every workbook.

How can I amend the VBA to only pick up the data from Sheet1 even though there are other sheets?

 

[jauntyangle]

It's ok, I've worked it out. Adding back the $ at the end of "Sheet1" worked!

Thanks for your help Sydney.

 

[dmolano]

Hi Chris,
Is there a way to go through a spreadsheet's tabs and import a range of data within a worksheet into access ONLY if a particular cell within that worksheet contains the value "1"?

Thanks

 

[ehuff]

Hello. I know this is several years after the original post, but I need help related to it. I am trying to use Denis's code from post #7 and I think it will work, but I cannot seem to get past the issue of the "Application.FileSearch" object no longer being available. I have searched this site and Google for solutions, but each one was either too complicated or too specific to be useful to an utter novice like myself. I would really like to use this instead of importing (and saving the import) of all 116 Excel files I need to have to populate my table and then incorporating each one individually into a macro for future updates. I will do it if I have to but was very hopeful I could use the aforementioned code. Thank you in advance!
- Erik
</SPAN>