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!?

 

[mrMozambique]

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

This worked great, thanks. I had never used the currentregion function. I use Ctrl+* to select the current region when I'm in the worksheet, but I never figured VBA would have an equivalent.

Thanks from Cape Town!

 

[Joe4]

This worked great, thanks. I had never used the currentregion function. I use Ctrl+* to select the current region when I'm in the worksheet, but I never figured VBA would have an equivalent.

The Macro Recorder can be a great tool for making these discoveries! Just record yourself doing it on the worksheet and see what VBA code results.