Using Office 2007...
I have some VBA code to import 6 different worksheets in a single workbook.
If I want to import a single tab, just using the name of the sheet as the "Range" argument, it works just fine. That code looks like this:
Code:
DoCmd.TransferSpreadsheet acImport, 10, "tbl-EN_Records", myExcelFileName, True, "Enrollment!"
Where "Enrollment" is the worksheet name that the data resides on.
Now suppose that I do not want to import all the columns on my "Enrollment" tab. I want to exclude the first two columns. So I have set up a named range named "Rng_Enrollment". So I change my code to look like this:
Code:
DoCmd.TransferSpreadsheet acImport, 10, "tbl-EN_Records", myExcelFileName, True, "Rng_Enrollment"
Now here is the strange part. My second bit of code posted above will only run if the Excel workbook is open, whereas the first bit of code will run fine on a closed workbook.
Using worksheet names in the Range argument does not seem to require the workbook to be open.
Is that normal? In using named ranges in importing data via Transferspreadsheet, does the Excel workbook need to be open?
Thanks