Working with closed workbooks

Hi all,

How do you get values from a closed workbook.

The code below works just fine when both work books are open.


Public Sub update_ws(wb)

'Declare the object variable
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim ws As Worksheet

wbunprotect wb

'Open an existing workbook

Set objApp = CreateObject("Excel.Application")
Set objBook = Workbooks.Open("Z:\AcctngBranchBudgets\Branch Profitability Model Update\BranchProfitabilityAdmin.xls", password:="sarah")
Set objSheet = objBook.Worksheets("Monthly P&L") 'Work with the first worksheet





Set wb = ThisWorkbook
Set ws = wb.Worksheets("Monthly P&L")

wbunprotect wb
wbunprotect ws
wsunprotect ws

tr = objSheet.Range("TM1DataGetCopy").Address

TM1DataGetPaste = Left(tr, 5)
objSheet.Range("TM1DataGetCopy").Copy

ws.Range(TM1DataGetPaste).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

rng = get_range(wb)

r = ws.Range(rng).Address
paste_rng = Left(r, 5)
ws.Range(rng).Copy

ws.Range(paste_rng).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=False

Range(paste_rng).Select
ws.Calculate
wbunprotect wb

wsprotect ws
wbprotect wb

'objApp.Visible = True 'Show it to the user

Set objSheet = Nothing 'Disconnect from all Excel objects (let the user take over)
Set objBook = Nothing
Set objApp = Nothing

End Sub


Please help!

Three ideas come to mind:

1. If the data on the closed spreadsheet is arranged in database fashion (i.e. data in each column is the same type, like integer, text, etc.) an elegant way is with ADO.

2. Cell formulas can directly reference cells of closed worksheets:

Code:

---

='C:\$user\Excel\[VB Forum.xls]Sheet1'!$C$7

---

3. There is an old Excel4 Macro that may still work (depending on the version of Excel you are using):

VB Code:

x = Application.ExecuteExcel4Macro("'C:\$user\Excel\[test.xls]Sheet1'!R7C4")

Thanks for you input. That did not work. What iam trying to do is to get the formula for the whole sheet