Transferspreadsheet Import with Dynamic Range Name

[ShredDude]

I have a line of code similar to this

DoCmd.TransferSpreadsheet acImport, 8, _
    "OldSched", strFileName, True, "OldScheduleFixed"

that works fine IF the Range name in Excel is defined as a predetermined number of cells.

For example, if "OldScheduleFixed" is a named range defined as ("A1:K100"), then no problems. It imports 100 rows of data into my table "OldSched"

However, if I try to use a Dynamic Range Name definition in Excel for the Range to import from such as:

=OFFSET(mysheet!$A$1,0,0,COUNTIF(mysheet!$b$1:$b$100,">0")+1,11)

the transfersheet method fails, claiming it cannot find the Range Name.

I was jsut trying to limit the range in Excel from which data would be pulled, as opposed to importing empty rows to my Access Table.

When I go to use the resulting Table in Access I can limit the data selected through a query, but I was trying to do it on the other end.

Any ideas as to why transferspreadsheet can't use a valid Range Name if it's defined with a formula?

 

[jal]

I'm fairly sure you're right - this functionality simply isn't built into the engines. One solution is to desist from dynamic ranges. Instead use a regular named range that resizes automatically when the user closes the workbook. Handle this event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lastRow As Long
Dim Range1 As Range
lastRow = Sheet1.Range("A65536").End(xlUp).Row
'Let's assume your table begins at cell A3 and has two columns
Set Range1 = Sheet1.Range("A3").Resize(lastRow, 2)
Range1.Name = "Customers"
Set Range1 = Nothing
End Sub

Actually oZgrid publications suggested putting this code in the BeforeSave event.

 

[ShredDude]

Jal:

Thanks for that insight. I hadn't thought about redefining the range name when I put the sheet away. I was trying to rely on Excel doing it automatically for me with a dynamic range definition as I usually do when just using Excel. This is my first attempt at using an Excel sheet to drive some forms/reports in Access.

After looking at this some more, I noticed that the list of Range Names that appear in Excel when you press F5 (GoTo) for example, excludes all the dynamic range names. Only the Statically defined Range Names appear. This must be the list of "valid" Range Names that Access looks to when using the TransferSpreadsheet method.

Your suggestion will work out just fine for my situation. I'll try the Before_Save Event too.

Thanks again,

Shred