Import a named range from Excel

[Greg]

Using the TransferSpreadsheet method, how do you import a
named range from an Excel spreadsheet? The code I have
so far is:

DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, "AccessTable
Name", "LocationofExcelFile", True, "Percent of Sales
Applied.Range(StatSample)"

with Percent of Sales being my Worksheet name and
StatSample being my Named Range in Excel.

 

[Arni Laugdal]

Greg,
Following sub will do the job. I the Access table exist
must it have same column order and column names as
headings in Excel.

Private Sub cmbTest_Click()
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, _
"tblTest", "C:\Percent of Sales.xls", _
True, "StatSample"
End Sub

Enjoy,
Arni Laugdal,
Office 2000 Master Instructor
www.til.is/msaccess

 

[John Nurick]

Hi Greg,

The Range parameter is usually just the name of the range, i.e.
"StatSample".

If you have defined the same name on more than one worksheet, try using
the same syntax as when referring to a local name in Excel VBA, which I
think would be "'Percent of Sales Applied'!StatSample".