I am trying to deal with some closed workbooks and tried to use ExecuteExcel4Macro approach and here's some hints This is the normal formula
=SUMIF('[Example.xlsx]Sheet1'!$B:$B,C3,'[Example.xlsx]Sheet1'!$C:$C)
I used s string to store the address of column B and t to store the address of column t of the closed workbook
Code: Select all
s = "'" & myDir & "[" & fn & "]" & "Sheet1" & "'!" & Columns(2).Address(True, True, -4150)
Then I tried to use
Code: Select all
Debug.Print ExecuteExcel4Macro("SUMIF(" & s & "," & Range("C3").Address(True, True, -4150) & "," & t & ")")
Another point how to get the worksheet name without the need to know the name by referring for example: Worksheets(1).Name in the variable s?
I noticed that the formula is working when the workbook is open but it throws an error #VALUE when the workbook is closed.
I also tried to use SUMPRODUCT as an alternative to SUMIF (to deal with closed workbooks) but with no success too
Code: Select all
Debug.Print ExecuteExcel4Macro("SUMPRODUCT((" & s & "=" & c.Offset(3 - c.Row, n).Address(True, True, -4150) & ")*(" & t & "))")
Finally, the only solution that has worked is to put the string as a formula then to convert the result to a value
Thanks advanced for any ideas.