SumIf Cells From Closed Workbook

[brorick]

:confused: This formula works as is, provided the workbook is open. Is there a way to use the following code regardless if the referenced workbook is open or closed?

Range("o27").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF([projectpoints.xls]exportprojectpointstoexcel!R2C2:R5C2,""= _
December"",[projectpoints.xls]exportprojectpointstoexcel!R2C7:R5C7)"

I appreciate any assistance. Thank you in advance.

[Zack Barresse]

Hello brorick,

No, sorry. If using VBA, I'd suggest to (test first) open the file, perform calculation, then close the workbook. Other than that, I lined out some methods for doing this here: http://www.mrexcel.com/board2/viewtopic.php?t=123529.


HTH

[Jacob Hilderbrand]

Usually you can use formulas in a cell to evaluate data from another closed workbook no problem, but SumIf seems to go nuts if it tries to work with a closed workbook.

[Jacob Hilderbrand]

In cases like this SUMPRODUCT proves once again why it is my favorite formula.


Dim Path As String
Path = "'C:\[projectpoints.xls]exportprojectpointstoexcel'!"
Range("O27").Value = _
"=SUMPRODUCT((" & Path & "R2C2:R5C2=" & """" & "December" & """" & _
")* (" & Path & "R2C7:R5C7))"


Note that when you want to put an actual quote (") in a string you need to use four quotes.

So for example:


MsgBox "The file name is " & """" & "MyFile.xls" & """" & "."

This would read as:

[brorick]

FireFytr and DRJ, thank you for your responses and advice. I will give it a try. Have a great day.

[Zack Barresse]

Btw, in the link I provided above, Method 4 works very well for me. Although it is somewhat difficult to get started and understand, once mastered it is quite easy. Beware the memory sacrifice though. :yes