Thread: Using links in VBA

Hi.

I am using several spreadsheets which are currently linked to specific cells in other workbooks. These are updated through dating the links whenever needed. However I would rather not use formulas in the cells at all and only have value updated by using the link function within VBA. I cannot find how to do this though.

Just to reiterate. I currently have cells that have"='\\server01\main_folder\subfolder\Document.xls" for example. The formula syntax is not exactly right but in the spreadsheets it is so you get the idea. These links are updated (when the workbooks are not open) when the links are update via the edit menu or a macro which does the same thing.

What I want to do is have no formula in the cells (only values) and use the feature that takes the value from the other workbooks but in VBA exclusively. This is what I have not been able to find out how to do.

Hope I have explained that clearly.

Thanks

If I understand correctly you want to have data from another Workbook, but you would not like to have the "long" formula's in like (e.g.)

HTML Code:

='\\server01\main_folder\subfolder\[Document.xls]'Sheet1!$A$1

An option can be to use External Data from your other Workbook.
As it is a procedure I cannot show an example. Just tell me how the data you want to connect to looks like (Table, Spread over the entire workbook, ....)

Your statement that it involves tables is just about perfect.

Here we go (I have XL2007 so you might have to look around in 2003)
Assume you have a workbook called MySource
Sheet1 has a table (A1:G10), where A1:G1 are headers
Sheet2 has a table (A1:G10), where A1:G1 are headers

In your Target Workbook
1) Get external data from access
2) Cange the filetype to all and select the MySource.xls
3) Automatically you'll be promted with "Table Select"
4) Select Sheet1, Next
5) Go into Properties (change update interval to your preferences)
5) Press OK

Now you'll have the "Table" in your sheet and it it updated according the settings you made (every XX minutes or when you open the WB)