Import Excel Data

Topic

New Reply

I am having issues trying to find the correct syntax to import data from Excel. I have the TrasnferSpreadsheet down fine. What I would like to do is give the statement a data range in the workbook.

I have tried ‘Region 33’!A1:T900, ‘Region 33’ A1:T900, and may more combinations. I am not having any luck finding the correct way to give the range.

My final goal is to set up a loop where I can feed a variable sheet name to the statement and have Access read in the A1:T900 range from many sheets in the workbook. Sheets have the naming convention Regionxx. I have a workbook containing 30 tabs for the given Regions.

thanks for any help in getting this statement working.

Reply | Quote

Replies

Would it be possible to use a “Named Range” in Excel? If you can modify the workbook to have Named Ranges on each of the worksheets, and you can use a consistent naming convention, then that should let you do the import programatically. If you can’t use that approach, then the syntax to define the range, given you know the range, is:
WorksheetName & “!A1:T900”

Reply | Quote

I will try this and see how it goes.

Thanks Wendell.

Reply | Quote

Named Range worked. For some reason Access converter the following from “‘Region 33′” & “!A1:T900” to ‘Region 33’$A1:T900. Of course Excel could not find that range.

But named rangeds worked like a dream.

Thanks for your help.

Reply | Quote