Import Excel Data Not Working With Dynamic Named Range

[mrMozambique]

Hi all. This may exist here already, but my searches have been futile so far. I have a dynamic named range (using offset) in an Excel file. When I try to import the data into Access, the dynamic range doesn't appear in the list of named ranges. If I change the range to a fixed area (=A$1$:AJ$:500$ or whatever), it works fine.

Does Access not recognize dynamic named ranges? What gives!?

 

[Joe4]

I use Named Ranges all the time to import Excel data into Access. As long as you have defined/named the range prior to trying to import, and select the Showed Named Ranges option, it should work.

At what point are you naming the range in Excel?
Are you saving the Excel file after setting the Name Range, but prior to trying to import into Access?

 

[mrMozambique]

I use Named Ranges all the time to import Excel data into Access. As long as you have defined/named the range prior to trying to import, and select the Showed Named Ranges option, it should work.

At what point are you naming the range in Excel?
Are you saving the Excel file after setting the Name Range, but prior to trying to import into Access?

Thanks, Joe4. It does work if I use a normal named range. However, I'm using a dynamic named range. Here's my range formula in Excel: =OFFSET(db_export!$A$1,0,0,COUNTA(db_export!$A:$A),COUNTA(db_export!$1:$1))

For whatever reason, the range disappears in the Access dropdown if I make it dynamic. Any ideas?

 

[Joe4]

Use VBA to make it a "normal" named range. You can trigger the VBA code to run whenever you want, allowing you to keep the dynamic nature of it.

 

[mrMozambique]

Use VBA to make it a "normal" named range. You can trigger the VBA code to run whenever you want, allowing you to keep the dynamic nature of it.

Thanks again, Joe. I appreciate the help.

So I've attempted to "select" the dynamic named range in VBA and create a new range based on that selection. My code is below. I receive an error: Runtime-Error 1004: Select Method of Range Class Failed. I've noticed before that Excel doesn't allow you to select dynamic named ranges in the dropdown above A1 - only "normal" ranges appear there. Are these two related?

How else should I be "selecting" the dynamic range in VBA?

Application.ScreenUpdating = False
Sheet3.Unprotect ("XXXX")
Sheet3.Visible = xlSheetVisible

'This is the error line:
Range("dynamic_range").Select

ActiveWorkbook.Names.Add Name:="dbexport", RefersTo:=Selection
Sheet3.Visible = xlSheetVeryHidden
Sheet3.Protect ("XXXX")
ThisWorkbook.Save
Application.ScreenUpdating = True

 

[Joe4]

Can you explain the logic of it all, in plan English?
How do you determine the range you want to name?

 

[mrMozambique]

Sure. I have a range in a hidden worksheet. The dynamic range is called "dynamic_range" and the sheet is "db_export". The range can grow or shrink (in both rows and columns) based on the values in another worksheet. So, the source of "dynamic_range" is the Offset formula I've indicated in my previous post.

I have an Access database that imports the data in db_export worksheet, range dynamic_range into a temporary table. If I just create a range normally by selecting the cells (e.g. =db_export!A1:AQ500), I find the named range no problem in the dropdown of the import external data wizard in Access. However, when I create the dynamic range using the Offset as the source, it doesn't appear in the dropdown in the wizard. I don't know why it doesn't appear.

I liked your recommendation of just creating a non-dynamic - or normal - range in VBA in the BeforeClose event of the workbook so I wrote the code above to try to do that, but I'm getting the error I pasted b/c Excel can't seem to select the dynamic range using VBA (at least with my poor VBA that is).

I really just want to be able to import the data in db_export worksheet, but need to have the range grow and shrink dynamically. Hope that makes sense. Thanks for your patience and assistance.

 

[Joe4]

Trying doing your offset/dynamic range calculation right in the VBA code, like this:

    Dim dynamic_range As Range
    Set dynamic_range = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    ActiveWorkbook.Names.Add Name:="dbexport", RefersTo:=dynamic_range

 

[Fazza]

To confirm what you've found, dynamic named ranges are not usable in queries.

Agree with Joe, create in VBA. Such as,

'I'm sure you can leave the sheet very hidden,
'but I think it needs to be unprotected to use
'current region. If you have set a scroll area
'you need to clear it first, too
 
'then again, if the sheet is hidden or very hidden,
'it doesn't need to be protected?
sheet3.unprotect
sheet3.range("A1").currentregion.name = "dbexport"
sheet3.protect
 
'or you could even protect it with userinterfaceonly:=true
 
'another thought, if you leave the workbook protected,
'I think you won't be able to query it

 

[Fazza]

PS.

It is the same in Excel, you can't query dynamic named ranges.

Note though you can query by worksheet names or specific ranges.
For info google. Such as http://support.microsoft.com/kb/278973

cheers