Originally Posted by
JoeM
With TransferSpreadsheet, you are allowed to import named ranges. So use the VBA code to name the range in Excel, and import that named range.
Closer... this almost works, but I must be missing something still. Here's what I've added:
Code:
Dim cell As RangeDim rng As Range
Dim RangeName As String
Dim CellName As String
RangeName = "myData" & i
CellName = Range("A1:A100").Find("Facility").Address & ":K" & LastRow
Set cell = ws.Range(CellName)
wb.Names.Add Name:=RangeName, RefersTo:=cell
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "mm_detail", objFile, True, RangeName
I checked under the Name Manager and I do see that it created "myData1" and it does refer to the correct spreadsheet range I'm looking for. However when I try to import it its throwing an error that it cannot find myData1?