Referencing to cells in other workbooks

[staffie007]

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

 

[goldfish]

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]

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]

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]

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]

Try this:

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

 

[goldfish]

looks like you have a typo:

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

missing an 'r'

 

[staffie007]

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