Import/append Excel workbook with multiple sheets using VBA.

[IMNovice]

Each worksheet has a named range.
How to write VBA code to import/append into 1 access table
using the named ranges. I know how to use the TransferSpreadsheet method for 1 worksheet
and use the range name, but not sure how tp write the loop to get the
multiple range names from all worksheets with range names.

 

[xenou]

I think you only need to run your transferspreadsheet X times (once for each range name).

 

[IMNovice]

Yes, I know that. My question was how to use the named ranges in a worksheet as a variable. Then create a loop to perform the import process X times using the variable to get the correct named range from each worksheet. Thanks.

 

[xenou]

Not sure if I follow you precisely.

I would put the range names in an array, possibly:

Dim arr(0 To 2) As String

arr(0) = "RangeName1"
arr(1) = "RangeName2"
arr(2) = "RangeName3"

For i = 0 To 2
    DoCmd.TransferSpreadsheet acImport, , "MyTableName", "myfile.xlsx", True, arr(0)
Next i

If I only had a few range names I might dispense with the array:

DoCmd.TransferSpreadsheet acImport, , "MyTableName", "myfile.xlsx", True, "RangeName1"
DoCmd.TransferSpreadsheet acImport, , "MyTableName", "myfile.xlsx", True, "RangeName2"
DoCmd.TransferSpreadsheet acImport, , "MyTableName", "myfile.xlsx", True, "RangeName3"

 

[Micron]

Wouldn't that be
DoCmd.TransferSpreadsheet acImport, , "MyTableName", "myfile.xlsx", True, arr(i)

This looks more like an Excel question but it's posted in the Access forum. Thus I wonder if there is not a way to loop through ranges in Excel (my Excel vba is rusty). However, I find the information conflicting. First post indicates each of several sheets has one range

Each worksheet has a named range.

then there is

My question was how to use the named ranges in a worksheet as a variable.

I have dealt with named ranges when automating Excel from Access but IIRC, there was only one on a sheet.

 

[xenou]

arr(i) is correct thank you.