Update remote references without opening the second sheet

Oaktree

MrExcel MVP

Joined

Jun 20, 2002

Messages

8,120

Office Version

1. 365

• Feb 27, 2008

• #2

1) Yes, in edit --> links --> update values

2) Not with INDIRECT, but the INDIRECT.EXT function that's part of the morefunc.dll add-in works like INDIRECT but allows the referencing of closed workbooks.

 

Upvote 0

G

goofy78270

Well-known Member

Joined

May 16, 2007

Messages

555

• Feb 27, 2008

• #3

I am sorry, let me be a little clearer.

I do a comparison of current rates vs yesterday rates in workbook a.
When woorkbook a is updated, I would like workbooks b,c, and to pull in the new values of workbook a without having to open them. Is this possible through a macro or something to force the values to update without having to open the workbook?

 

Upvote 0

Oaktree

MrExcel MVP

Joined

Jun 20, 2002

Messages

8,120

Office Version

1. 365

• Feb 27, 2008

• #4

When you open b or c, you should be asked whether you want to update links. This is the same as going to edit --> links --> update values.

If you're sick of the prompt (or not getting the prompt), look at the options in edit --> links --> Startup prompt.

 

Upvote 0

G

goofy78270

Well-known Member

Joined

May 16, 2007

Messages

555

• Feb 27, 2008

• #5

I understand this, but I do not want to have to open books b, c, or d. I just want to make an update to book a and have book b, c, and d update themselves.

 

Upvote 0

Oaktree

MrExcel MVP

Joined

Jun 20, 2002

Messages

8,120

Office Version

1. 365

• Feb 27, 2008

• #6

Yes and no. There is no (easy) way to do a reverse link trace, where you could click on a button like "update the links in the files that reference this file". You'd basically need to write a macro to cycle through your dependent files and update them (by opening them and updating the values).

Then again, is there an order dependency? e.g. a's updates flow into b, b needs to recalculate with the new values from a before it flows into c, etc.?

If not, and b, c, and d are independent from each other (but dependent on a) why would it matter if b updates when a changes vs. when b is opened?

 

Upvote 0

F

Fazza

MrExcel MVP

Joined

May 17, 2006

Messages

9,368

• Feb 27, 2008

• #7

Yes, goofy. Workbooks can be updated without opening them.

Critically, I'm assuming the data is in tables - set up like database tables. For an example, I'll assume the data table has a defined name, but this is not essential. A worksheet name is fine too. Example uses late binding.

Code:

dim objRecordset as object
set objRecordset = createobject("ADODB.Recordset")
objRecordset.open "UPDATE mytable SET cost = 100 WHERE productcode = 'abc'", _
  "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\subdirectory\file.xls;Extended Properties=""Excel 8.0"""
set objRecordset = nothing

Or depending in what you are doing, instead of the SQL UPDATE you might use SQL INSERT.

HTH, Fazza

 

Upvote 0

G

goofy78270

Well-known Member

Joined

May 16, 2007

Messages

555

• Feb 27, 2008

• #8

So will I need to step through every value that is updated?
Instead of using the update line, is it possible to set a range within a worksheet?

 

Upvote 0

F

Fazza

MrExcel MVP

Joined

May 17, 2006

Messages

9,368

• Feb 27, 2008

• #9

Looping through every record will work. Set up correctly , it is very quick. I now realise my example is not the best set up for multiple updates. Better to open a connection and then loop through all the updates. So,

Code:

dim objConnection as object
set objConnection = createobject("ADODB.Connection")
with objConnection
  .open strConnectionAsAbove

  'now loop through doing the updates
  ...
     .execute "UPDATE whatever"
  ...

  .close
end with
set objConnection = nothing

You can insert a whole range, use INSERT instead of UPDATE. Note this is not an update, but an insert. Depends on what you want to do.

I'll have a google for some links & post again soon.

regards, Fazza

 

Upvote 0

F

Fazza

MrExcel MVP

Joined

May 17, 2006

Messages

9,368

• Feb 27, 2008

• #10

PS

Or instead of the ADODB.Connection you could use ADODB.Command
Nearly the same as I posted for the connection object

 

Upvote 0