General discussion

Locked

Import Multiple Excel Spreadsheet into Access

By gtstrauss ·
Does anyone know how to Import Multiple Excel Spreadsheet into an Access database?

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Simply link the spreadsheet as an external table

by awfernald In reply to Import Multiple Excel Spr ...

Then use your macros/scripts inside of Access to query as if they were simply another MS Access table.

Collapse -

perhaps

by csnmjr In reply to Import Multiple Excel Spr ...

i think you can only import 1 by 1 but then you can group them in acces if wanted all in a form , hopes thats of any help?
#

Collapse -

by rkuhn In reply to perhaps
Collapse -

VBA...

by nicholas.paisley In reply to

Option Compare Database
Option Explicit
'***********************************************************
'Macro Loops through the specified directory (strPath)
'and imports ALL Excel files as linked tables in the Access
'Database.
'***********************************************************
Sub Import_To_Excel()
'Directory Path
Const strPath As String = "FULL PATH TO the folder containing your files"
'Filename
Dim strFile As String
'File Array
Dim strFileList() As String
'File Number
Dim intFile As Integer
DoCmd.SetWarnings False
'Loop through the folder & build file list
strFile = Dir(strPath & "*.xls")
Do While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Loop
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
For intFile = 1 To UBound(strFileList)
'replace TABLE NAME and RANGE to fit
DoCmd.TransferSpreadsheet acImport, , _
"TABLE NAME", strPath & strFileList(intFile), True, "RANGE"
Next
DoCmd.SetWarnings True
MsgBox UBound(strFileList) & " Files were Imported"
End Sub

Collapse -

Please help me understand this code

by Maplewood In reply to VBA...

I use access but my knowledge does not extend into vb script. I have used them before off the posts but very simple ones. If you could help me plug my data into the code it would be greatly appreciated. My spreadsheet is called "Financial Detial" and stored on my desktop. Inside "Financial Detail" are over 100 worksheets labeled "Output 1 (0100)", "Output 1 (0101)", "Output 1 (0102)" etc. Basically it is the number in parentheses that changes from worksheet to worksheet.
Thanks

Collapse -

Range?

by thesilverfox In reply to VBA...

This could be really useful to me so thanks for posting it.

I'm not sure about a couple of things. What does RANGE actually refer to in the script? Also, does (TABLE NAME) have to already exist as a table in the db or will it just create one?

I've got over 300 spreadsheet files that ideally I have to automate getting into a single sheet so doing it through Access might just help.

Thanks!

Collapse -

Great Post -- Thanks

by a-ludington In reply to Import Multiple Excel Spr ...

No problems using the VBA, just dropped it in and specified as directed. I also looked up the method on msdn to find a few more options.
http://msdn.microsoft.com/en-us/library/bb214134%28v=office.12%29.aspx

Thanks for posting this. It was very helpful.

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums