Help Please - Macro to upload Excel data into Access

  • Thread starter Thread starter Karthik
  • Start date Start date
K

Karthik

Hi
I am new user of access. I have data in 100 excel file all in same
format. I need to append data from each of these files (cells B1: AA98,
row 1 contains the headings)into one access database(say
masterdata.mdb). I have all the excel files in a folder "C\Temp". I
want a macro which will append data from all these files without having
to write 100 lines in a macro to import these files. Basically a code
with a loop which does something like append until remaining files = 0.

Any help is greatly appreciated.

Thanks
Karthik Bhat
Bangalore
 
Hi Karthik,

You need to reference the MicroSoft Scripting Runtime Lib (Tools->
References) and then use the following code snippet to accomplish what you
need:

Option Compare Database
Dim FSO As New Scripting.FileSystemObject
Dim fldr As Scripting.Folder
Dim Xl_files As Scripting.Files
Dim xl_file As Scripting.file

Sub Import_Excel()
Set fldr = FSO.GetFolder("C:\temp\import")
Set Xl_files = fldr.Files
If Xl_files.Count <> 0 Then
For Each xl_file In Xl_files
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblImp",
xl_file.Path, True
Next
End If
MsgBox ("Imported " & Str(Xl_files.Count) & " Files")
End Sub
 
Of course, the same thing can be done without requiring the Scripting
Runtime:

Option Compare Database

Sub Import_Excel()
Dim intCount As Integer
Dim strFile As String
Dim strFolder As String

intCount = 0
strFolder = "C:\temp\import\"
strFile = Dir(strFolder & *.xls)
Do While Len(strFile) > 0
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblImp",
strFolder & strFile, True
intCount = intCount + 1
strFile = Dir()
Loop
MsgBox ("Imported " & Str(intCount) & " Files")
End Sub
 
Back
Top