Import Dynamic Named Range from Excel

[Ian Mac]

All,

Does anyone know how to Import a Dynamic Named Range from Excel.
I have (well...ok...they have) workbooks, each one has a Named range 'MyTable'.

Which is something like =OFFSET($A$2,0,0,COUNTA(A:A)-1,19)

Access doesn't see this type of Named Range.

I wish to loop through each file/location and Import the data on a daily basis.

Anyone any ideas what I might try.

Cheers,

 

[Jafa]

Does not work apparently

Hi

I asked the same question before I saw your post. The thread is at

http://www.access-programmers.co.uk/forums/showthread.php?t=78708
(look at the end of the thread)

If this is the case, I am thinking that maybe you could set up an On Close macro that would define a static range based on the dynamic range as the wookbook is closed.

You can use a dynamic range while it is open and Access could use the defined one after the file is closed.

I am trying to work out how to do that (I learn by trial and error).

I can select the dynamic range:
Application.Goto Reference:="Title_Dynamic" (Title_Dynamic is defined using the OFFSET function)

But have not worked out how to name the now selected range. The code below works to name the dynamic range, so instead of the RefersTo...... I want to use the selected range. Not sure how to do that.

ActiveWorkbook.Names.Add Name:="Title_Static", RefersToR1C1:= _
"=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))"


Cheers

 

[Jafa]

This works

Think I have worked it out.

I have a dynamically named range ("Title_Dynamic") that uses Offset.

This Auto_Close macro runs and creates a normally defined range ("Title_Static).


Private Sub Auto_Close()

ActiveWorkbook.Names.Add Name:="Title_Static", RefersTo:= _
Range("Title_Dynamic")

End Sub


If the Excel sheet might be open, you could change it to update when the wookbook is saved as well.



Hope it works for your solution.

Cheers