Referencing Closed via open workbook external links - VBA

[spydey]

EDIT: Sorry, the title should have read:

"Referencing Closed Workbook via Open Workbook's external links - VBA"

---------------------------------

I have some code in WorkbookA where I ask for the location of WorkbookB (the data and location constantly change). Via a prompt I am able to select WorkbookB and store that location as a variable and change the LinkSources(xlExcelLink) in WorkbookA to the newly selected WorkbookB file location. This allows me to update different in-cell formulas I have in WorkkbookA that depend on the data in WorkbookB. Works great!

I need to ad some new code to perform some additional tasks.

My question is two-fold:

1.) Am I able to "refresh" or "update" a variable mid-stride so that I don't have to manually re-assign what it contains?

Example:

cLinks = ThisWorkbook.LinkSources(xlExcelLinks)

This gives me the current link (there is only one) found in WorkBookA.
After running some code (as mentioned above), the link has changed, however, if I look at the value of cLinks after selecting the new WorkbookB, it is still the old value; which makes sense because that value was stored. The 'ThisWorkbook.LinkSource(xlExcelLinks)' has changed though, but cLinks has not.

Can I simply update cLinks to refresh its value?

Perhaps something like:

cLinks.Update

or

cLinks.Refresh

This would force cLinks to go back to 'ThisWorkbook.LinkSources(xlExcelLinks)' and get the now newest value/location.

I guess I could simply write:

cLinks = ThisWorkbook.LinkSources(xlExcelLinks)

but I would prefer not to have to do it that way if possible. I am trying to do this as efficiently as possible ..... granted I bet all my other code is nowhere even near as efficient as it could probably be, but I am getting there, hehehehe.



2.) On to the bigger question. Due to already having the WorkbookB location saved as a variable, could I use that variable (i.e. .'cLink') to reference different sheets/Ranges/Named Ranges in WorkbookB (without opening it) from within WorkbookA?

When I pull up cLink from cLinks in the Immediate window via this code:

For Each cLink In cLinks
Debug.Print cLink
Next

it gives me the actual file path and name.

e.g. cLink = C:\Location\Of\WorkbookB\WorkbookB.xlsx

Because WorkbookB is not open, we would typically need to refer to WorkbookB by it's full path/name (right?), so thus could I simply use the cLink variable to refer to the worksheets/ranges in WorkbookB because cLink contains WorkbookB's full path/name?

Such as:

For Each cLink in cLinks
Set rng = cLink.Worksheet("Testing").Range("Named Range")

Other code here

Next

Anything quirky I should know about?

Thanks as always!!

-Spydey

P.S. Sorry about the long write up ..... writing it up tends to help me see the steps needed, etc. There have been many a write up that I never posted because as I wrote it, I realized that I already had the answer ... but there have many more where I did and am grateful for your help and assistance in understanding all this. ray:ray:

 

[spydey]

So I figured that for part 1, perhaps the best way to do it is to just re-state:

cLinks = ThisWorkbook.LinkSources(xlExcelLinks)

So that is what I am going to do.

But what about part 2? Anyone have any insight, thoughts, advise, etc?

Thanks!

-Spydey