Updating a different sheet through a closed workbook

[ophir_hz]

Hi There!

I got into some trouble trying to help someone at work:
He uses Excel 2007. He has 2 workbooks, lets say wb1 and wb2. wb1 has 2 sheets, sheetA and sheetB, while wb2 has sheetZ. What needs to happen is that when he updates sheetA, it updates some cells in sheetZ, which updates some cells in sheetB. This works fine when wb2 is open, but when wb2 is closed, sheetB doesn't update "online"... This is problem because he has about 15 files like this, and he doesn't want to open all of them all the time.
I tried to play with the settings and writing something in VBA (tried to open the second file when a relevant cell changes - did not update the file), but didn't find a solution.

Any solution will help, in the settings or in VBA.

Thanks in advance,
Ophir

p.s. if I open the wb2 manually when wb1 is open, everything gets updated correctly

 

[ravishankar]

Hi
You can pull data from closed books in 2003 and earlier versions of XL. but you can't update data into a closed file. you may choose to open it programmatically, modify, save and close it. Need more details of what happens between sheet A, Z and B
Ravi

 

[ophir_hz]

It's a shame it works only in earlier versions...
So here is the content of the relevant cells:
(workbook - sheet - cell: content)
wb1.xslx - sheetA - A1: 10
wb2.xslx - sheetZ - A1: =[wb1.xlsx]sheetA!A1+10.1 (should be valuated to 20.1)
wb1.xslx - sheetB - A2: =[wb2.xlsx]sheetZ!A1+10.01 (should be valuated to 30.11)

And what I need is that if I change the value of the first line to 20, even if wb2 is closed, that the value shown in the last row will update to 40.11

Thanks,
Ophir

 

[ravishankar]

Hi
Paste the following codes in the macro window (Alt F11) of wb1.xlsx and save it inside the folder containing 15+ files. Enter a value in Cell A1 of sheetA and run the macro.

Sub Ophir()
Dim z  As Long, e As Long, g As Long
Dim f As String, J As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Sheet1").Select
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xlsx")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
If Cells(e, 1) <> ActiveWorkbook.Name Then
J = Cells(e, 1)
Workbooks.Open Filename:=Cells(1, 2) & J
Sheets("Sheetz").Range("A1") = Workbooks("wb1.xlsx").Worksheets("sheetA").Cells(1, 1) + 10.1
Workbooks("wb1.xlsx").Worksheets("sheetB").Cells(e, 1) = Workbooks(J).Worksheets("Sheetz").Range("A1") + 10.01
ActiveWorkbook.Close True
End If
Next e
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "complete."
End Sub

It lists all excel files in col A and opens each of them, tranfers A1 value to sheetz ,the sum is tranferred back to A1 of Sheetb, saves and closes it.
Give it a try on a copy of your files, since changed data will be saved.
Ravi