retrieve names from external file

[MKC]

Hello everybody!
I am trying copy data from one exel file to the other, the source file being closed. This works quite well, and the macro I found is even able to copy ranges from the closed file to the open one.

As the size and the starting point of the range, that is to be copied, varies from time to time, I thought it smart to define a name in the (closed) source sheet. This name ("start") basically is the name for the cell where my macro is suppossed to start copying the data.
Well, it turned out, that this wasn't actually that smart; or at least I am not able to extract the names reference.

This is my code:
Reference = Application.Workbooks("data.xls").Names("start").RefersToR1C1

It will work if the source file is (data.xls) open, but not if it is closed.

Do you have any ideas how to solve this problem?

Or am I just stupid by sticking to the idea that I wanna do all the importing/copying with the source file being closed?

Or do you have an idea how I could import from a dynamic range without using names defining the starting position?

Thanks a lot for all help! :-)

[Bob Phillips]

Why not just open the workbook in your code, it makes life so much simpler.

[MKC]

Hi xld,
Thanks for the reply!
I will have to apply this code on at least 8 files one right after the other. And I am afraid that the user will be quite confused, if automatically 8 windows pop up and close.
Or is there a way, that all the opening and closing is "hidden" to the user? Will the whole macro take more time if the source workbooks are open?

[Bob Phillips]

If you use code like this


Application.ScreenUpdating = False

Set wb = Workbooks.Open("C:\test\wb1.xls")
'do some stuff
wb.Close savechanges:=False

Set wb = Workbooks.Open("C:\test\wb2.xls")
'do some stuff
wb.Close savechanges:=False

'etc.
Application.ScreenUpdating = True


they shouldn't get confused

[MKC]

Thanks alot!
And is there a difference "execution speed wise"?

[Bob Phillips]

There will be an overhead as you have to open and close the files. You can read the workbooks using ADO, but there is still an overhead to connect to the workbook, get the data and so on. You are extracting data, and so there is an overhead. It is the same in Excel, if you link to a closed workbook, it has to fetch that info. Of course it maintains that info on a worksheet, and Excel is smart enough to update that info on open, but it all takes time and effort.

[MKC]

Ok, thanks a lot! I will try and see how far I can get.

Is there a nice sub opening another workbook, extracting data from a range I define in the macro, and copying that range to my first workbook?

I have seen several of them around but as they are so many I am quite confused which one to use.

[Bob Phillips]

I am not really sure what you are asking/asking for?

[mdmackillop]

Set ws = ActiveSheet
Set wb = Workbooks.Open("C:\test\wb1.xls")
wb.Range("MyData").Copy ws.Range("B2")
wb.Close savechanges:=False

[MKC]

Thanks a lot! :-)