VBA for copying remote data

[ronwessel]

Hello,

I have a macro that references a defined-name-range to another file
(e.g. CopyFrom_Range ='[some_file.xls]Sheet1'!$A$2:$B$33)

The macro cycles thru the cells of the defined-range with:
For Each c In Range("CopyFrom_Range").

This works as long as the SOME_FILE.XLS is already open. Do I have to manually check-for/open the linked-file, or is there a better way?

Thanks,

Ron

 

[John Davis]

Hello,

I have a macro that references a defined-name-range to another file
(e.g. CopyFrom_Range ='[some_file.xls]Sheet1'!$A$2:$B$33)

The macro cycles thru the cells of the defined-range with:
For Each c In Range("CopyFrom_Range").

This works as long as the SOME_FILE.XLS is already open. Do I have to manually check-for/open the linked-file, or is there a better way?

Thanks,

Ron

Ron:

Would this help you?

If Not IsFileOpen("D:\SOME_FILE.xls") Then
Workbooks.Open "D:\SOME_FILE.xls"
Else: Windows("SOME_FILE.xls").Activate
End If

 

[ronwessel]

Hi John,

Thanks for your example code:
If Not IsFileOpen("D:\SOME_FILE.xls") Then
Workbooks.Open "D:\SOME_FILE.xls"
Else: Windows("SOME_FILE.xls").Activate
End If

...but it's not really what I'm looking for. I was aware that I could manually perform the actions as you describe, but it's going to be more difficult than that. I have a defined-name called "COPY_FROM" that identifies the data to be copied. It's content changes depending on whether the target-file is already opened:
(e.g. ='C:\where_ever\[some_file.xls]Sheet1'!$A$1:$B$33) <--If the file is closed
(e.g. ='[some_file.xls]Sheet1'!$A$2:$B$33) <--If the file is already open

So, for example, to perform the actions of your code, I must first parse-out the filename for use in the OPEN statements, and then parse out the sheet-name for use with the windows-activate command. Since the contents of the defined-name is different depending on whether the file is open, it starts to get a little complicated. I can certainly do this, but I was simply hoping I could be lazy.

Thanks for your feedback,

Ron