Referencing to cells in other workbooks

staffie007

Board Regular
Joined
Oct 21, 2005
Messages
92
Hi...

Can someone help...
Im trying to create a workbook that puts a value in range a1 from a different workbook without opening the file?

I was hoping to use something like range("a1").formulaR1C1 = "c:\my docs\temp.xls" & range("c1")

I dont want to write the formula "='temp'c1" that it will ask to update it everytime I open the file.

Please help,
Thanks
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
Well, in order for it to stay current, it has to check everyonce in a while to see if the value has changed (it cannot check constantly, like cells behave in a single sheet) So upon open is a good time for it to try to update. One idea is to write a macro that activates upon opening of the spread sheet that opens the second spread sheet quietly (ie you don't see it) and grabs the value.
 

staffie007

Board Regular
Joined
Oct 21, 2005
Messages
92
That would work normally, but Im drawing reports for that period of time, so never want it to update...

Currently I have the macro open all the files individually, and copies the information into memory and then writes them into the workbook.add.

So am looking for another way to do in without opening them.

But for other instances, Im curious about the open quietly story you are talking about. So could you please help firstly on the problem as it stands now and then also tell me about what you are talking about please.

Thanks so much
 

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
You just want to write the value 1 time? If I understand you right...

Workbooks("Book1").Worksheets("Sheet1").Range("A1")
= Workbooks("temp").Worksheets("Sheet1").Range("C1")

This will not be a formula, it will simply copy the value, and place a hardcoded number so you won't be able to see where that number came from.

Another option is to turn off remoted updating all together
tools -> options -> calculations -> "Update Remote Refrences"
 

staffie007

Board Regular
Joined
Oct 21, 2005
Messages
92

ADVERTISEMENT

Sorry for only coming back now...

It says subscript out of range when I run the macro...?

I see you havent specified a folder under Workbooks... Do the workbooks have to be in the same folder?

I tried:

Workbooks.add
activecell.formular1c1 = workbooks("c:\temp\temp.xls").worksheets("Sheet1").range("A1").formula1c1

Then get the error

Can you help?
 

KevinOReillyIWK

New Member
Joined
Feb 2, 2006
Messages
15
Try this:

ActiveCell.Value = "='C:\temp\[temp.xls]Sheet1'!$A$1"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
 

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
looks like you have a typo:

activecell.formular1c1 = workbooks("c:\temp\temp.xls").worksheets("Sheet1").range("A1").formula1c1

missing an 'r'
 

staffie007

Board Regular
Joined
Oct 21, 2005
Messages
92
It didnt help for what ever reason and I dont know where my reply went to that AGES ago... Sorry!

Still using :

ActiveCell.Value = "='C:\temp\[temp.xls]Sheet1'!$A$1"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Unless someone can offer something else
 

Watch MrExcel Video

Forum statistics

Threads
1,118,328
Messages
5,571,564
Members
412,405
Latest member
DutchMonkey
Top