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

Edit: Looks like I was wrong

Hello sai0449,

Please try this link: http://www.exceltip.com/summing/summ...workbooks.html

And have a look here as well: https://video.search.yahoo.com/yhs/s...p=yhs-adk_sbnt

Regards.

Presume you want the total in another excel workbook?

If so, there are a few different ways to sum/get values data from a closed WB. Can you give more info on what you are trying achieve here?

Hi all,

..... regarding the calculation (SUM) a specific row in multiple excel workbooks with out opening ......

Originally Posted by stnkynts

Edit: Looks like I was wrong

Etc....
Q . Can you do this??
This question I gave up trying to get a straight answer to some time ago.
The answer seems to be “You can usually do what you want to do.”

It would appear to be one of these things where you you need to know the answer to the question to know what to ask.

The question should probably be

Q. Can I get at what is in the Cells of a Closed Workbook.

A. Yes

If you ask if you can get Information from a closed Workbook, it is almost always answered with No. Strictly that may be the case. Hence the confusion.

It would appear that you can get the information, or rather in attempting to reference the information; you “tap into” an XML document. This would appear to be the case in the Links given by Winon and in the Links I give below.

I have “ done it“ already in answering Threads of this nature. I used the way shown in the Links I give. I call it for now “performing a reference”, for want of something better.

One last warning, but just a gut feeling. This rather useful operation to be “able to do” has been around some time, but is not well publicised. I would be careful in relying in it. Because
_a) Particularly in one of the Links from Winon, The “Expert” demonstrating it is somewhat surprised and warns that it is a strange thing that may enable someone to “”spy on you”
_b) I expect if the way Files are organised should change, or some other “thing” in the software is changed, ( not necessarily directly to do with Excel ) then maybe this way of “doing what you want” might not be possible..

Alan
Ref
http://www.excelforum.com/excel-prog...ubfolders.html
http://www.thecodecage.com/forumz/sh...post1055012583
http://www.excelforum.com/excel-prog...ubfolders.html
http://www.excelforum.com/excel-prog...ml#post4199982
Note the second code in the next Reference initially gets at a copy of some data in a closed workbook. ( In that particular code it then opens based on the criteria of what it finds )
http://www.excelforum.com/developmen...ml#post4199910


P.s.
I am the last one that would split hairs over this......

Originally Posted by FDibbins

.... 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....

Hello Doc.AElstein,

Your elaborated comments are noted.

You should however also bear in mind that when anyone post/s a link to a possible answer to a specific issue, he or she has no control of whatever follows from such. If it is further explored by the OP, it is at his or her own discretion to respond in a responsible manner.

Forgive me therefore if I have accidentally offended you, or your perception of how "things" should actually be done, or for unknowingly making available any of many malicious Code/s, without checking it out first, as offered as solutions on the Web.

I would love to see you own solution to the topic thread.

Regards.

Hi Winon

Originally Posted by Winon

....You should however also bear in mind that when anyone post/s a link to a possible answer to a specific issue, he or she has no control of whatever follows from such. If it is further explored by the OP, it is at his or her own discretion to respond in a responsible manner.
Forgive me therefore if I have accidentally offended you,......

I found the Links you gave extremely interesting and useful. I had only seen a couple Links on this way of "doing it" And they were basically the same, using the "VBA Get Reference " way. So it was very helpful to finally see some more - that is what sparked my interest in the Thread. - And why I made what was just intended as a contribution , nothing else, no criticisms - as such a Excel / VBA novice and Computer idiot it would be extremely stupid for me to criticise people with obvious experience in Excel / VBA and computing

_.......

Originally Posted by Winon

....I would love to see you own solution to the topic thread.....

I mentioned where I had done this "way" of doing it, for example here ( Code 2 - The one to "Get a Reference" - I may have called it "Performing a Reference" then ).
http://www.excelforum.com/excel-prog...ml#post4199982
http://www.excelforum.com/excel-prog...ml#post4227458
But they basically use the VBA " Get the Reference way" as shown for, example here:
http://spreadsheetpage.com/index.php...a_closed_file/

_.......................................

Thanks again for the Links. In particular I found the Video very interesting. I intend to download it and watch it later as an alternative to the Telly. I will try to dig out an old German Video as well that i have somewhere - I seem to remember there they were getting info from the web via a " XML " thing... That may tie up with what Bill Jelen was talking about getting the info from a "Cached" " XML " thing.

Alan

Hello Doc.AElstein,

Thank you for the clarification, and your valued contribution. Sometimes it helps to ruffle a bit of feathers to gain a better understanding of some issues usually not so well known.

Best Regards.

This post was a duplicate from to one above, and has been deleted.

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!!

Originally Posted by Winon

......
Thank you for the clarification, and your valued contribution. Sometimes it helps to ruffle a bit of feathers to gain a better understanding of some issues usually not so well known......

Agreed, - it would be great to get the confusion on if / what is being done here sorted!!!

The basic excel functions (vlookup, sum, count, =, plus a few I cant think of right now) have always been able to pull data from closed workbooks, and is something I have used for years and have advocated it's use for ages. As far as I know, it just "peeks" inside the file, and gets what you need - if that means it makes a temp copy, I wouldnt know.

In the past, I have had HUGE database-type excel files that ran tons of calcs, and could take ages to calc. So as not to burden management with opening those files and waiting for them to load and cal, I made summary files that simply extracted pertinent info for them, and showed them just what they needed. Those summary files where based on using the functions.

Hi
_ I have done a Summary follow up, for my later reference, if nobody else’s!!

_ It appears there are two main “ways of doing something” being discussed here. These “ways of doing something” being to get at the value in a cell ( or cells) from a closed Workbook through Excel and or Excel VBA
. ( I also have included possibly an advanced third, and a another that is sometimes used to “pseudo” do this, or do something in the spirit of it )

_ I have done a “stand alone” demo code to demo these. And I did a three similar Functions that could easily be adapted to use in a real life situation, for multiple cells. ( The final “advanced" way does this Multi cell get automatically). The Functions do a lot extra and have many extra lines and explaining 'comments, but they can easily be stripped down.

Brief Description of the "stand alone" Code

_Rem 1) The code starts by making a demo Workbook, Putting some values in it in a Worksheet named “Sheet1”........

Using Excel 2007

Row\Col	A	B	C
1	HeadingCellA1	HeadingCellB1	HeadingCellC1
2	CellA2	CellB2	CellC2
3	CellA3	CellB3	CellC3

Sheet1

________..... Then it closes the Workbook.
_...........................................................

( .... _ Rem 2) This does not really do what we want. But it is sometimes given as a solution. Here 3 main code lines 1 2 1nd 3, Set a Workbook variable to a Workbook.
The last ( Line 3 ) uses the GetObject with which the Workbook Object is created not visible. This is sometimes in the “spirit” of what we want to do, for example,.........
http://www.excelforum.com/excel-prog...-workbook.html
_______........but clearly we have opened the book....)
_.....................................................................

_ Rem 3) Get / Perform Reference
Here are some good references on this one:
Dinesh Kumar Takyar Get a value from a closed workbook automatically using VBA, You Tube.
https://www.youtube.com/watch?v=S1jUHP_CDPo
Dinesh Kumar Takyar How to get multiple values from closed workbook using VBA
http://www.exceltrainingvideos.com/h...ook-using-vba/
A Video is also in above Link.. and at You Tube
https://www.youtube.com/watch?v=Onhq...ature=youtu.be
and vbaexpress:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=454
and Excel Foru
http://www.excelforum.com/excel-prog...ml#post4227458


This is a classic way in VBA that it is done. ( uses the __= ExecuteExcel4Macro(stringLink)____ thing ) . Typically a Function would be made to get at the value from one cell, and then if necessary this would be looped for a number of cells. A nice use of this would be to do a For Each in Array( “A1”, “A4”, “FG456”.......) type thing calling each time this Function
_..........................................................................

_Rem 4) The straight forward Link of type “...=[book1.XLSB]Sheet1!A4….“ suggested by FDibbins in Post #10 is considered in a VBA adaption here for comparison with the way of Rem 3) . . It writes in the Link Formula based on a given cell, then takes the value of it. That is the basic idea used here:
http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm
This could probably be developed to Paste out as a CSE type Formula over a multi Cell Range.
This would then be taken into a VBA Array and that returned..... which is what i did in the last way ( returning an Array I mean ) .....
_.................................................................................

_Rem5) Just for fun, this was an attempt at a VBA code based on a get data from another Excel file using a manual way: ( Microsoft Query )
Here is a reference on this: Get External Data from another Excel File. Excel VBA Training Videos
http://www.exceltrainingvideos.com/g...er-excel-file/
Video is also in that above Link (Dinesh Kumar Takyar )
I did a macro recording while following that described way of doing it. Then I attempted to modify it. It is not very good as i do not quite understand exactly what is going on. I think briefly it “looks” somehow for things resembling tables in the closed Workbook. You have to give it the Sheet and table heading to choose from. It then makes a List Object table of that and then inserts that into a sheet. I fiddled around with it empirically until I got it to insert a column, from which you can take all values in a continual list from.
After then adding a bit to put those values in an Array, I delete / remove all the List object table stuff. All that I put in a Similar Function to that I did for Rem 3) and Rem 4). The thing is a bit messy as I do not understand it enough to simplify it further. But it is an interesting alternative allowing a whole column to be got at one go.

_..............

So the main code is here:
Sub StandAkaLoneTestCodesGetvalueFromClosedWonkBook()

http://www.excelforum.com/showthread...t=#post4320946

Copy and paste it all in a Normal Macro Module or Sheet Module, It message boxes out all the values it gets from the Closed book. ( Rem 4) in the above code ) part will also paste the formula in the current active cell )

You need to copy the following Functions also into the same Code Module

http://www.excelforum.com/showthread...t=#post4320948

http://www.excelforum.com/showthread...t=#post4320950

http://www.excelforum.com/showthread...t=#post4320953

_.......................................................................................................


Ref
' http://www.excelforum.com/excel-prog...ml#post4227458
' http://www.excelforum.com/developmen...ml#post4213824
' http://www.excelforum.com/excel-prog...ml#post4227458
' http://www.excelforum.com/developmen...ml#post4213824
' http://spreadsheetpage.com/index.php...a_closed_file/
' http://www.thecodecage.com/forumz/sh...post1055012583

Hi Alan,
You said referring to the “ Put a Link in a Cell” way ( Rem 4 ) )

Originally Posted by Doc.AElstein

.... This could probably be developed to Paste out as a CSE type Formula over a multi Cell Range....... This would then be taken into a VBA Array and that returned ......

.. i did it while waiting for the Forum to be accessible

Based again on the same Input Sheet from a closed Workbook ( Called “myFileToClose.xlsm” and if the same Folder as The Workbook that you have the codes in ).................

Using Excel 2007

Row\Col	A	B	C
1	HeadingCellA1	HeadingCellB1	HeadingCellC1
2	CellA2	CellB2	CellC2
3	CellA3	CellB3	CellC3

Sheet1

_.....
_............
___________________then the Function I wrote will CSE Paste in Cells ( staring at a Top Left Cell you pass to the Function at the calling line ) to give the following CSE Formulas:...........

Using Excel 2007

Row\Col	A	B	C
1	='H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\[myFileToClose.xlsm]Sheet1'!$A$1:$C$3	='H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\[myFileToClose.xlsm]Sheet1'!$A$1:$C$3	='H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\[myFileToClose.xlsm]Sheet1'!$A$1:$C$3
2	='H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\[myFileToClose.xlsm]Sheet1'!$A$1:$C$3	='H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\[myFileToClose.xlsm]Sheet1'!$A$1:$C$3	='H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\[myFileToClose.xlsm]Sheet1'!$A$1:$C$3
3	='H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\[myFileToClose.xlsm]Sheet1'!$A$1:$C$3	='H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\[myFileToClose.xlsm]Sheet1'!$A$1:$C$3	='H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\[myFileToClose.xlsm]Sheet1'!$A$1:$C$3

__________________............which you see in the sheet as the values as in the first screen shot. These values are then returned in an Array() within a Variant – this Array() is what this function returns.

Here is a “stand alone” Demo code just for calling the Function


Sub TestFuPutACSElinkInGetValue()

http://www.excelforum.com/showthread...t=#post4320955

and here is the Function that calls

http://www.excelforum.com/showthread...t=#post4320957

@ FDibbins

Originally Posted by FDibbins

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

Hi Ford,
Thanks to that i had another good look look and have learnt a lot ( as you can see from the last two posts!! :eek: )

One very small thing.... I think ( and i could well be wrong again !! )... that the above is not quite right .....
You made the mistake i keep making ( maybe ) ....
I expect you got the
____ ... ..... =[book1.XLSB]Sheet1!A4........
From when you had both Workbooks open,
_ . to get it you maybe put ___ = ___ in a cell in the receiving book and then clicked on cell A4 in the “book to be closed” ( book1.XLSB ) and then hit Enter..

That actual formula will only work when you have the “book to be closed” still open-

To make it work on the “book to be closed” when it is closed you need to add some bits to it. –

_ a) add a reference to the File Name ( Full Path to the Folder the “book to be closed” File )

_ b) tack that refference on with a path separator between ( usually a __ \ __ )

_c) add a __ '__ from the start to the end of the string to the Range

So it should look like this

"=" & "'"& FullPath & "\" & "[book1.XLSB.xlsx]Sheet1" & "'" & "!A4”

Where FileName looks something of this form
Filename=”C:\Users\Elston\Desktop”

So finally you have

='C:\Users\Elston\Desktop\[book1.XLSB.xlsx]Sheet1'!A4

_...............................
(I think... )
Alan

But I NOTE
_(i) in my Excel when I close book1.XLSB, then Excel automatically changes the Formula to look correct
And
_(ii) If the File exists, then it also changes the Formula to the correct version, sometimes.

So the problem just comes in if you put the short formula in before you get hold of on your computer that file.

In short..
=[book1.XLSB]Sheet1!A4
Don’t “work”_... but usually you don’t notice as XL converts it to something like
='C:\Users\Elston\Desktop\[book1.XLSB.xlsx]Sheet1'!A4

Hi sai0449,

One approach should be the following

_1) Investigate and learn how to use the VBA Dir Function to loop through all Files in a Folder ( If you do a simple Google search using “VBA Dir Function to loop through all Files in a Folder” you will come across a lot of information. )

_2) write a code to do that in a Master Workbook

_3) In each loop you would then apply what we have explained to you in this Thread to perform operations on each Workbook within this Loop
Those operations would be to
either
_3a) Bring the data from each Workbook into a master Workbook, do the calculations you wish
Or
_3b) Apply a formula to do those calculations on the closed book

Without precise information about you data I do not think anyone can suggest an exact formula

_4) Having done those operations you resulting Sum would be then stored somewhere and in some way as you wish

_5 The loop would then continue for each Workbook.

_ I suggest you attempt that, and then if you have problems , then ask for further help

Alan

EDIT Sept 2016
Hi
_ I used this Thread a lot yesterday.
_ I noticed an error. It is too late for me to Edit, and a bit complicated to explain to a Mod and ask to do it for me.. so.,..
_ So I am just being responsible for my post for anyone else using the Thread in the future:

_ In post #14 of this Thread_....
http://www.excelforum.com/excel-prog...ml#post4321000
_.......I did and referenced a Function to get a cell value from a closed Workbook using the = ExecuteExcel4Macro( way.
http://www.excelforum.com/showthread...t=#post4320948

_..
There is a typo in the Function towards the end:

This:_.....

Please Login or Register  to view this content.

_.....should be

Please Login or Register  to view this content.

_..............or this

Please Login or Register  to view this content.

_.....
I redid the Function anyway yesterday here:
http://www.excelforum.com/showthread...t=#post4483417

Alan
P.s. Another similar code to some of those in this Thread which allows you to get a Range of values from a closed workbook, with the addition of being able to do that even if you do not know the Worksheet name can be found here
http://www.excelforum.com/tips-and-t...heet-item.html