Import many Excel worksheets into the same Access table

[Juan Pablo González]

Hey,

I've got a workbook with 86 sheets, all representing a different date. I need to import this into the same table, but I really don't feel like using the wizard one step at a time.

I've succesfuly used the DoCmd.TransferText command to import many text files, and thought that the DoCmd.TransferSpreadsheet was the one I needed... but, I got stuck because I don't see anyway of telling Access WHICH sheet it should import, it looks like it assumes the first one ?

This is my current code:<pre>Private Sub Comando0_Click()
Dim WB As Workbook
Dim WS As Worksheet
Dim XL As Excel.Application
Dim Rng As Range
Dim i As Long
Dim tblName As String
Dim Count As Long

Set XL = New Excel.Application
Set WB = XL.Workbooks.Open("D:ProyectosPasivosReq Cheque CampanaSolicitudesNoviembreaEnero.xls")
Count = WB.Worksheets.Count
tblName = "Movimientos"
For i = 1 To Count
Set Rng = WB.Worksheets(i).Range("A1").CurrentRegion
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tblName, WB.FullName, True, Rng.Address
Next i
End Sub</pre>Now, how do I do this?

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2003-02-13 18:51

 

[Juan Pablo González]

Ok, solved it. Had to change two things, first, open the workbook as ReadOnly, and second, pass the "external" address of the range, that is, including the sheet name.