Importing multiple Excel workbooks into an Access table

[elsenorjose]

I've tried searching the forum for this but can't find exactly what I'm looking for. I have a directory with Excel workbooks that differ only in name; i.e. workbook1, workbook2, etc. but have the same structure. I have created a table in Access that has all the fields I need and now am trying to write a macro that will import the data from each workbook into my table. Basically, and UPDATE or APPEND query but based on the workbooks. I have some code I've run in Excel to EXPORT the data but that would involve opening each workbook and running the macro. Can someone help me modify this to run in Access so I can just IMPORT the workbooks? Sorry if this isn't clear, feel free to ask for more clarification if needed. Here's my sample code in Excel (it works by the way.)

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents\MyDB.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Table", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 8 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Rec") = Range("A" & r).Value
.Fields("Date") = Range("B" & r).Value
.Fields("DMA") = Range("C" & r).Value
.Fields("Store") = Range("F" & r).Value
.Fields("Ord Ct") = Range("L" & r).Value
.Fields("Avg Sale") = Range("N" & r).Value
.Fields("Sale PCYA") = Range("O" & r).Value
.Fields("Cost %") = Range("I" & r).Value
.Fields("Cost % Variance") = Range("I" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Thanks in advance for the help!

 

[TCARPENTER]

Hi elsnorjose,

Have a look at the DoCmd.TransferSpreadsheet function from Access.

HTH
Todd

 

[elsenorjose]

Hi, yes, I've looked at this function and I haven't seen anything that offers much help by way of importing multiple files. I have a directory that has over 100 files, how would I write a macro or VBA code that would open the first file, import data from a specific starting row and once done move to the next file until the last file in the directory is imported? The Excel code I wrote works but I have to manually open each file and run the macro. This is very time consuming especially since this directory will be updated daily with new files.

 

[PHV]

And what about the Dir function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[elsenorjose]

Thanks for the reply. Where I'm getting stuck is that none of the samples I've seen in the Help file or MSDN give me sample usage of that different arguments I need. I'm hoping someone here has done this before and can help me with syntax/usage. Thanks again.

 

[JoeSmoe55]

.