[SOLVED] Using links in VBA

brillig12 · #1 02-07-2010, 02:05 PM

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

brillig12 · #2 02-09-2010, 04:06 AM

If anyone has any ideas on this it would a great help, otherwise I'll have to abandon the idea

rwgrietveld · #3 02-09-2010, 04:14 AM

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, ....)

brillig12 · #4 02-09-2010, 04:41 AM

That's exactly right in your description of what I'm trying todo.

The data I want to connect to is contained within tables on different tabs of a workbook. The ranges are fixed so I would generally always be want to take values from the same cells.

Is that enough information?

rwgrietveld · #5 02-09-2010, 04:57 AM

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)

brillig12 · #6 02-09-2010, 05:15 AM

That's near perfect!

I'm using 2003 and there was not a preferences option for updates but after testing it I was able to record the process in a macro so can automate the process as I had hoped.

Thanks

Welcome to the Excel Forum

Forum Statistics