Importing ranges from a tab within a workbook

[Guest]

Hello All,

I am using a macro to import a spreadsheet into a table
(see macro below). The macro works well to import the
whole sheet when there is only that sheet in the
workbook. However, I need to specify certain ranges on a
particular tab within a workbook. For example, I only
want to import ranges (A3:AA10, A12:AA261) from the Budget
Input tab and the workbook has a few other sheets. Any
ideas?

DoCmd.TransferSpreadsheet acImport, 8, "ImportedSS",
strDirName & strWorkbookName, True, ""

Thanks,
Dion

 

[George Nicholson]

Try replacing "" (the Range argument) with "BudgetInput!A3:AA261"
I've never tried importing a specific (unnamed) range, but I do know that
"BudgetInput!" (including the all-important exclamation point) would import
the BudgetInput sheet in its entirety.

Having said that, I suspect, but do not know for a fact, that importing a
non-contiguous range may not be supported. What I normally do is import a
whole sheet of data into an empty shell temp table and then run an append
query to move only the data I wanted from temp to "Live" (converting values
as necessary). I find it makes the whole process a bit more flexible and
robust: if the structure changes in either Excel or Access it can often be
either ignored or handled by editing the query rather than code.

Getting it into Access is one step. Putting it to bed and tucking it in is
usually another.