VB - Importing Selected cells from Excel To Access

ainran

I'm trying to import some cells from an excel file to an access database. The cells i want are always in the same place from file to file (there are ~500 files). I could write a macro in Excel but the problem is applying that to 500 files so what my plan was is to write a macro in access. Thats all very well but for i can't seem to figure out how to select different cells from different sheets. I'm using

' VB Code
DoCmd.TransferSpreadsheet acImport, 9, _
"Supplier","C:\supplier.xls", False, "A1:G12"

This works fine, but only on the first sheet.
Anyone know the method of changing sheets?

Is there any other method that would work?

(I've tried TransferText but because that requires a CSV or similar file type with a schema.ini file its turning out to be much more trouble than its worth)

Evil Phil

This works in Excel, you'll have to implement it in Access yourself. If loops through each sheet in the workbook.

For i = 1 To Sheets.Count
        Sheets(i).Select
        ...
Next i

I'd imagine you'd have to instansiate a Workbook object and then loop through the Sheets property. Dunno if this helps.

ainran

its ok, found it

If i use "Sheet Name$A1:Q1" it works

(you wouldnt believe the time it took me to find that)

thanks anyway