Getting information from a closed excel workbook using VBA

Question

I am trying to extract information from a closed workbook using excel VBA, more specifically, the sum of a range of numbers in the closed excel workbook into my current excel workbook.

I have tried the following codes below:
I want to find the sum of values from Z8 to the last Z cell in the column.

Range("D4").Formula = "Sum('I:\Outgoing\Money Outgoing\'!$Z8:$Z)"

Set wkbk = Workbooks.Open("I:\Outgoing\Money Outgoing.xlsx\")
Range("Z7").Select
ActiveCell.FormulaR1C1 = "=SUM(Z8:Z10000)"
Range("D4").Value = GetInfoFromClosedFile(a, f, d, "Z6")

I do not wish to open the closed workbook The excel file is Money Outgoing.xlsx There is only 1 tab in the file. Tab name is Layout 1

I would avoid linking workbooks like this. Is I: a mapped network drive? What if someone renames it Money Outgoing 2019-2020.xlsx? You could probably try Sheet1.Range("D4").Formula = "=Sum('[I:\Outgoing\Money Outgoing.xlsx]Layout 1'!$Z8:$Z10000)". See avoid Select and Activate, too. Good luck! — Mathieu Guindon
Thanks Mathieu, to avoid reference to Z10000 but to the last z cell, is it $Z8:$Z ? — user12151877
No, you can't do that without opening the file AFAIK. $Z8:$Z is a syntax error as far as Excel is concerned. — Mathieu Guindon
Thanks Mathieu! that helped a lot. If possible could you take a look at my other post if you have time? — user12151877

Mehmet Canbulat Mehmet Canbulat · Accepted Answer · 2019-10-03T07:40:59

Without opening the workbook, you can use below code. Modify this with your needs.

Sub ExecuteMacroForClosedWorkbook()

Dim FldrName As String
Dim FlName As String
Dim WrkshtName As String
Dim ExecuteStringVariable As String

ExecuteStringVariable = "'" & FldrName & "\" & "[" & FlName & "]" & WrkshtName & "'!R"

ActiveCell.value = ExecuteExcel4Macro(ExecuteStringVariable & 1 & "C1") 'Execute A1 Cell and Transfer Value to Your ActiveCell

End Sub

Getting information from a closed excel workbook using VBA

1 Answers