Import Multiple Sheets from Same Workbook in Excel

[chrisbrocco]

Re: Importing from Excel problem.

Im having trouble importing a spreadsheet into access.

The sheet im trying to import has approx 15 columns, all formatted correctly, but whenever i try to import im only getting the first 2 columns.

Im using the following code to do this -

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, _
"tablename", _
"my path", _
1, _
"A1:U200"

ive tried doing the import using the wizard, and get the same results. Only the first 2 columns.

Anyone got any ideas?

Using this code how could I import in the same way but import all sheets within one book

 

[MyBoo]

Not sure... I tried the following and it seems to work fine.

DoCmd.TransferSpreadsheet acImport, 8, "newtable", "c:\excel files\poolstats.xls", False, "Stats!A1:M70"

You might want to create a TransferSpreadsheet macro, test it and then convert it to VBA. Compare it's syntax to yours.

 

[chrisbrocco]

This works great, would you know how I could make a table with all the file paths in and make the code look at each file path and import it in.

 

[MyBoo]

This works great, would you know how I could make a table with all the file paths in and make the code look at each file path and import it in.

This should get you going:

Private Sub Command0_Click()
Dim db As Database
Dim rst As Recordset
Dim x
Dim path

Set db = CurrentDb()
Set rst = db.OpenRecordset("pathtable", dbOpenTable)

If rst.RecordCount > 0 Then
Do Until rst.EOF
With rst
For x = 1 To rst.RecordCount
path = rst("path")
DoCmd.TransferSpreadsheet acImport, 8, "newtable", path & "\poolstats.xls", False, "Stats!A1:M70"
.MoveNext
Next
End With
Loop
End If

End Sub


I created a form with a command button on it. and added the code to the On Click Event.

I tested this with two paths (pathtable contains two records).
It appends the spreadsheets into the same table each time you run this.

HTH