Is it possible to sum entire column with out opening excel ?

P.s.
I am the last one that would split hairs over this......
Quote Originally Posted by FDibbins View Post
.... there are a few different ways to sum/get values data from a closed WB. .........
An expert might disagree, particularly with the “from a closed WB”
I expect it should be
“there are a few different ways to sum/get values data that is in a closed WB”
It would appear you actually get that information from a copy, and that copy due to its ease of access could be a bit of a security hole that is not always too keenly talked about....

If you know the WB name, and it's structure, it IS entirely possible (and a fairly simple matter, depending in the complexity of the requirement), to extract values from a closed workbook. A simple =SUM([book1.XLSB]Sheet1!$A$4:$A$8) is a perfect example, as is =[book1.XLSB]Sheet1!A4.
SUMPUDUCT will also extract data from a closed workbook

I suspect you are thinking of the XXXIF() family of functions which do not work on closed workbooks?

Hi Ford
Thanks for coming back. This is very useful in clearing something up that clearly confuses bigger Excel Brains than mine!!

Originally Posted by FDibbins

If you know the WB name, and it's structure, it IS entirely possible (and a fairly simple matter, .... to extract values from a closed workbook. A simple ..... =[book1.XLSB]Sheet1!A4.
......

I agree that it appears to get “what you want”. I just tried. It does as you suggest. I am very confused

Originally Posted by FDibbins

......
I suspect you are thinking of the XXXIF() family of functions which do not work on closed workbooks?

No I was not. I have simply been reading everywhere that you cannot directly do it.
_ - Even though, in some articles that I have read this , they then seem to go on and do just that!!!
For example:
http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm
So I am very confused...
Can you explain why they say in such an article that it cannot be done. In that particular article they seem to be going on to do exactly what you have done.??


So I was assuming that somehow you are doing something that gets the data ( possibly a copy there of ) from somewhere or something, but not directly from the Workbook ??

Very confusing!
I know that you can get what you want. But it appears to confuse more experienced people than me and many will still tell me “.....it's not possible to pull in data from a closed Workbook (without opening it)....”
Alan.


P.S. Just for clarity....In the Bill Jelen Link
https://www.youtube.com/watch?v=-0cDSt0Io40
he is suggesting if you do something similar to your example, but doing a VLOOKUP on a large table, that it would mean the following.
If you saved your file and passed it on to someone somewhere else in the world and they have no access to the closed File from which the VLOOKUP referenced, then they could still get all the information from the complete range used in the VLOOKUP formula as the File you gave them included some XML "thing" that had all the info from the last reference made to the closed Workbook.
As long as they do not update the link on opening, or by referencing cells in that range by modifying the VLOOKUP formula ( or using a (different formula referencing the same cells ) then they would still be privy to the entire values from the entire range, even if your original VLOOKUP had just selected a few of them.
So you see that was what was confusing me
I thought
_- you use your formula once with the "closed file" open and a "XML" thing has that info.
_. You close the "Closed file" and that particular reference works still ( getting info from......I thought a "XML" thing...."Cached".... )
_- But i then assumed a reference to a new cell from a range not used in the first formula would now not work
_ -But I just confirmed that it does. So you see the reason for my confusion!!