Loop through closed workbooks without opening them

[YasserKhalil]

Hello everyone
Is there a way to loop through closed workbooks without opening them ...
I have to sum just specific cells in those closed workbooks (the specific cells that should be summed separately is D14 and F20)
That's to have the total of D14 of all the closed workbooks and put the result in the Main workbook in A10
And do the same for F20 and put the result in the Main workbook in B10

If possible I need flexible way without opening the workbooks
Thanks advanced for help

[HansV]

Do you want to do this for all workbooks in a folder? Or some workbooks in a folder - if so, which ones? Or workbooks in multiple folders?

[YasserKhalil]

The workbooks are in one folder and the desired workbooks has the string "record" in their names (I can solve that point later)
The most important point for me is to deal with the workbooks without opening them

[Rudi]

Here are pages with sample code that can be adapted (see links below). The samples show ways to get data from a workbook without physically opening it. You just need to place it into a loop and adapt the range of source data to collect.

- https://www.extendoffice.com/documents/ ... kbook.html
- https://www.encodedna.com/excel/copy-da ... pening.htm

In addition: A formula driven way to get data (this is an 'old' example!)

- http://www.vbaexpress.com/kb/getarticle.php?kb_id=454

Above code slightly adapted (here)

Code: Select all

Sub test() 
GetValuesFromAClosedWorkbook "C:\folder\anotherfolder", "Book1.xls","Sheet1", "A1" 
End Sub
 
Sub GetValuesFromAClosedWorkbook(fPath As String, _ 
fName As String, sName, cellRange As String) 
With ActiveSheet.Range(cellRange) 
.FormulaArray = "='" & fPath & "\[" & fName & "]" _ 
& sName & "'!" & cellRange 
.Value = .Value 
End With
End Sub

[YasserKhalil]

Thanks a lot Mr. Rudi
As for the first two links depend on the Open method .. but the last link and your code is ok for my request
I will try to adapt it to suit my issue
Best Regards

[Doc.AElstein]

Hello Yasser,
A few comments ..
_1 ) I think if you don’t want to “open” the workbooks, or rather you don't want the workbooks to be opened, then the words “loop through” is probably a bit misleading. You would not usually do any “looping” if you did not want the workbooks to be opened. ( That is probably why Rudi included the codes that open the workbooks)
Excel allows you to get at values in closed workbooks in a similar way that you can get values from a range reference in an open book. The range reference is just a bit more complicated for the case of a closed workbook as it needs the extra information to get to where the closed workbook is. The syntax is a bit nasty** but _....
_2) _.... The basic information you need is similar to that given here: http://www.eileenslounge.com/viewtopic.php?f=30&t=28616" onclick="window.open(this.href);return false; http://www.eileenslounge.com/viewtopic. ... 16#p221503" onclick="window.open(this.href);return false;
_3) You probably do not need VBA for what you want. Often VBA is used in these sort of things just because the syntax of closed workbook references can be a bit tricky** to get correct manually.
_4) Most formulas work with closed workbook range references.
So you just use a simple formula , but replace the usual simple range references with the nasty long complicated closed workbook range references
( _5 The last articles ( http://www.vbaexpress.com/kb/getarticle ... ?kb_id=454" onclick="window.open(this.href);return false; https://www.mrexcel.com/forum/excel-que ... ost1558533" onclick="window.open(this.href);return false; ) and code from Rudi are a bit mixed up : The code here http://www.vbaexpress.com/kb/getarticle.php?kb_id=454" onclick="window.open(this.href);return false; is an old way using some strange “ExecuteExcel4Macro” thingy, the code Rudi gave which comes from here https://www.mrexcel.com/forum/excel-que ... ost1558533" onclick="window.open(this.href);return false; is probably doing something along the lines of what I will be suggesting. But it is not clear as some variables are not defined. Apparently the code comes from a Nimrod. A Nimrod is an old Hebrew Hunter or an old English RAF plane )

I will give a simple example for a formula working with closed workbook range references:
I will just consider for the purposes of this simple example, that you have only two record files which have something only in cell D14 ,
Clsd2.xlsx : https://app.box.com/s/9pio38brzc3cawgk3vwkul5wlu6z3h02" onclick="window.open(this.href);return false; : This has a 2 in D14 of the first worksheet
Clsd1.xlsx : https://app.box.com/s/9ubup1nj2j6vel2frxaactudqy1zbsc1" onclick="window.open(this.href);return false; : This has a 1 in D14 of the first worksheet
And here is your main Master workbook:
Masturbkt.xlsx : https://app.box.com/s/pjbxnmc3g1uf23icdrnd5yyxc9glf9r7" onclick="window.open(this.href);return false; : This is empty. A file of great nothingness :)

Download those three files, ( and put them all in the same folder )
Now open just Masturbkt.xlsx
Masturbkt.xlsx is a non macro file. But never mind about that. Ignore that fact for now. Put this code in any code module in Masturbkt.xlsx

Sub MasturSUMthing()
Let ThisWorkbook.Worksheets.Item(1).Range("A10").Value = "=SUM('" & ThisWorkbook.Path & "\[Clsd1.xlsx]Tabelle1'!$D$14,'" & ThisWorkbook.Path & "\[Clsd2.xlsx]Tabelle1'!$D$14)"
End Sub

Code: Select all

Sub MasturSUMthing()
  Let  ThisWorkbook.Worksheets.Item(1).Range("A10").Value = "=SUM('" & ThisWorkbook.Path & "\[Clsd1.xlsx]Tabelle1'!$D$14,'" & ThisWorkbook.Path & "\[Clsd2.xlsx]Tabelle1'!$D$14)"
End Sub

Now run that code.
The only purpose of this code is to put the formula that you need in cell A10.
( Instead of running the code , you could type the formula manually in. The formula will have the form that you should recognise as similar to the formulas you had in this thread: http://www.eileenslounge.com/viewtopic.php?f=30&t=28616" onclick="window.open(this.href);return false; )
The formula is similar to this sort of form:
=SUM('C:\DeskStrop\YassersFolder\[Clsd1.xlsx]Sht1'!$D$14,'C:\DeskStrop\YassersFolder\[Clsd2.xlsx]Sht1'!$D$14)
which is basically similar to the more typical form seen of something like
=SUM(Sheet1!$D$14,Sheet2!$D$14)
or like
=SUM($D$14,$D$17)
or like
=SUM(D14,G13,Z20)
etc....
You should see a formula of that first form, =SUM('C:\DeskStrop\YassersFolder\[Clsd1.xlsx]Sht1'!$D$14,'C:\DeskStrop\YassersFolder\[Clsd2.xlsx]Sht1'!$D$14) , up in the formula bar after running the code. In the cell you should see the result for the Sum of 2 and 1 which should be 3 : https://imgur.com/MtI0WOw" onclick="window.open(this.href);return false;

A10FormulaAndvalue.JPG

( If you use the code to put the formula in, but then save and / or close the workbook, Masturbkt.xlsx , then you will lose the code, but the formula will remain. The formula should work with Clsd2.xlsx or Clsd1.xlsx open or closed. If you change the values in D14, then the value in A10 of Masturbkt.xlsx should change automatically to the correct sum. )

Alan

[YasserKhalil]

Thanks a lot Mr. Alan for this detailed explanation .. I appreciate that a lot
I used the term "Loop through" because in the desired folder there are a lot of workbooks .. so I won't use the workbook names by myself and I suppose the VBA code to do that ..That's the main point

[Doc.AElstein]

Hi Yasser

.. used the term "Loop through" because in the desired folder a lot of workbooks .. I won't use the workbook names by myself

Ah, OK , I think I see your point now.

I suppose one way to do that would be to have a loop in which the VBA Dir Function is used to get the file names of the files you want. ( https://docs.microsoft.com/en-us/office ... r-function" onclick="window.open(this.href);return false; ) . The Dir function returns the filename based on a search string which you must give it in the first argument Here of
Dir(Here, ).
This search string Here must be the full path and file name for the file that you are looking for. If it finds it, then it returns a string text, which is just the last File name part. If it does not find it, then it returns an empty string, "" . But you can also use wildcards, such as * in that full path and file name search string, so , for example , a search string such as this sort of form,
Here = “C:\DeskStrop\YassersFolder\*record*”
, will result in the Dir Function returning you a File Name which has the word “record” in it , if it finds one in the folder , “YassersFolder” . In other words, if there was a File "MyrecordFile.jpg" in your folder, "YassersFolder" , at this full path and file name , “C:\DeskStrop\YassersFolder\MyrecordFile.jpg" then a code line something similar to like this ,
myFileName = Dir(“C:\DeskStrop\YassersFolder\*record*”, )
, would result in the string "MyrecordFile.jpg" being put into the variable myFileName.
Then you could use the closed workbook reference in that loop using that found File Name.
You could put the closed workbook reference formula in any spare cell, for example A1. Then, in the loop, you could have a simple variable to keep the total of adding the cell values you want.
At the end of the loop, you use Dir again, but don't give it a search string. What it does then is looks again for the next file using the same search criteria as the last time it was used. ( I expect it probably starts the search again, but starts from just after the last file that it found ). So typically you keep doing that until you get returned a "" , which is what the Dir function returns if it finds nothing like what it searched for. ( As far as I know, the Dir function only goes once through the entire folder, ( I expect it probably starts at the top of the folder and works down until the end, after which it returns the "" if it gets to the end of the folder without finding anything) , so you don't need any checks to make sure it does not start looking again from the top once it has been through the entire folder)

As example: Once again a simplified example just considering the sum of all D14 values in workbooks in your folder which have the word “record” in the filename
Code: https://pastebin.com/b9wrcPYe" onclick="window.open(this.href);return false; , http://www.excelfox.com/forum/showthrea ... #post10799 https://tinyurl.com/y9om7v3r" onclick="window.open(this.href);return false;

Code: Select all

Sub SUMfromD14inClsdWkBksInFolder()
' Use Dir function with wildcards in full path and file name search string to find file names you want
Dim FileName As String ' This will be used each time in a loop for the file name part of a closed workbook reference. ..."=C:\..\.kj\dgh.\JossersJockStrap\[ .." & FileName & "..*!?%$§"!..shhfhit.$.ftang.$.farrrrpbelle..!$.Bolllox :-) .."
 Let FileName = Dir("C:\Users\Elston\Desktop\YassersFolder\*record*", vbNormal) ' The Dir function used  for the first time here, it will find the first file with "record" in its file name in the folder , "YassersFolder". If it does not find one,  it will return "". If it finds one, then variable FileName will be given its name, ( just the name,  like "DOGSrecordPlops.xyjpig" ,  not the entire file path and name, "C:\DeskStrop\JossersJockStrap\DOGSrecordPlops.xyjpig" )
'Do do Looping while you find the file names you want =========
    Do While Not FileName = "" ' Dir Function will return "" if it finds no new File names of the ones looking for. If it does find a File name, then we use that filename in the closed workbook reference in the next line, which we put in a spare cell, for example, A1
     Let ThisWorkbook.Worksheets.Item(1).Range("A1").Value = "=" & "'" & "C:\Users\Elston\Desktop\YassersFolder\" & "[" & FileName & "]Tabelle1'!$D$14"
    Dim SomeTotal As Double ' A variable to hold the Sum total so far
     Let SomeTotal = SomeTotal + ThisWorkbook.Worksheets.Item(1).Range("A1").Value
     Let FileName = Dir ' an unqualified Dir will look again using the last search criteria, so the first time this line is used, Dir Function  will try to find a second file with the string part "record" in its file name
    Loop '  do while you find the file names you want ==========
 Let ThisWorkbook.Worksheets.Item(1).Range("A10").Value = SomeTotal
End Sub 

You will need to know the Worksheet name , ( in my code above it is “Tabelle1” , usually it is “Sheet1” for the first worksheet using default Names in English Excel). If you don’t know the worksheet name , there is a way to get the worksheet names from a workbook without opening the workbook, but that is a bit more complicated.. ( https://www.excelforum.com/tips-and-tut ... -item.html" onclick="window.open(this.href);return false; )

I tested the code after making this Folder, ( on my Desktop ) : https://imgur.com/dt7ZxAt" onclick="window.open(this.href);return false; , https://imgur.com/IlThWVY" onclick="window.open(this.href);return false;

YassersFolder.JPG

resizeYassersFolder .jpg

The code added the numbers in D14 from the files Clsdrecord1.xlsx and Clsdrecord2.xlsx, and finally it put the total in cell A10 of the first worksheet in the workbook in which the code was ran from. ( The code works whether the files Clsdrecord1.xlsx and Clsdrecord2.xlsx are open or closed. One or the other can be open, the other closed, or both open or both closed etc.. )-

Alan

[YasserKhalil]

Hello Mr. Alan
Thank you very very much. I really appreciate your detailed explanation .. It is totally clear and very useful for me and for others
Thanks a lot for great help and for this awesome and clear solution
Best Regards

[YasserKhalil]

Sorry for disturbing again

As for the desired cell which is D14 is different in some files ... so the results are different from expected
But there's a string with "Total" in cell and to the right of this cell there is the desired value which needed to be accumulated ..
Can we rely on this string and deal with the cell next to it ..?

[HansV]

I think the only way to do that would be to open each workbook in turn and search for the word "Total", but you don't want to open the workbooks...

[YasserKhalil]

If there is no way to do so except is to open the workbooks so NO Problem .. Just guide me to the part where to find the string 'Total' and how to add the value to the accumulator
Thanks advanced for help

[HansV]

Something like this:

Code: Select all

    Dim wbk As Workbook
    Dim wsh As Worksheet
    Dim rng As Range

    …

    Do While Not FileName = ""
        ' Open the workbook
        Set wbk = Workbooks.Open("C:\MyFolder\" & FileName)
        ' Refer to the first sheet
        Set wsh = wbk.Worksheets(1)
        ' Find the cell containing 'Total'
        Set rng = wsh.Cells.Find(What:="Total", LookAt:=xlWhole)
        ' Add the value of the cell next to it to the running total
        SomeTotal = SomeTotal + rng.Offset(0, 1).Value
        ' Close the workbook
        wbk.Close SaveChanges:=False
        ' Get the next filename
        FileName = Dir
    Loop

    …

[Doc.AElstein]

...for the desired cell which is D14 is different in some files ... so the results are different from expected
...there's a string with "Total" in cell and to the right of this cell there is the desired value which needed to be accumulated ..
Can we rely on this string and deal with the cell next to it ..?

If you use the basic closed workbook reference formula in its relative form ( that is to say in a form where you don’t have the $s on the cell address, then you can use that relative formula across a range in your main file to bring in a temporary range of values instead of the single temporary value.

To explain:
Lets say this is a typical record file of yours:-
https://imgur.com/HS6XjSH" onclick="window.open(this.href);return false;

HelloYasser.JPG

Lets say that file is “Clsdrecord2.xlsx”
In the previous code I gave you can use this slightly modified code line to bring in all those values into your main File.

Code: Select all

Sub ClsdRangeIn()
 Let ThisWorkbook.Worksheets.Item(1).Range("A1:B2").Value = "=" & "'" & "C:\Users\Elston\Desktop\YassersFolder\" & "[" & "Clsdrecord2.xlsx" & "]Tabelle1'!D14"
End Sub

Note that I use D14 and not $D$14

Then, instead of having a temporary cell, A1, you now have a temporary range, A1:B2 , in your main file like this:-
https://imgur.com/7t4cZbc" onclick="window.open(this.href);return false;

HelloYasserInMainFile.JPG

You can then search that temporary range to find the value next to “Total”


_._____________

If you prefer to open the workbooks , then that will be a different code approach.
Edit: I see Hans is looking at that for you

Alan

[Doc.AElstein]

This would be a combination of my code and Han's code. (This does not open the file)

Code: Select all

Sub SUMfromRangeinClsdWkBksInFolder()
Dim FileName As String:
 Let FileName = Dir("C:\Users\Elston\Desktop\YassersFolder\*record*", vbNormal) ' 
'Do do Looping while you find the file names you want =========
    Do While Not FileName = ""
     Let ThisWorkbook.Worksheets.Item(1).Range("A1:I9").Value = "=" & "'" & "C:\Users\Elston\Desktop\YassersFolder\" & "[" & FileName & "]Tabelle1'!D14"
     Let ThisWorkbook.Worksheets.Item(1).Range("A1:I9").Value = ThisWorkbook.Worksheets.Item(1).Range("A1:I9").Value ' Need this extra line or the Range.Find will not work - This code line changes the formula in the cell  to its value
    Dim SomeTotal As Double '
    Dim rng As Range: Set rng = ThisWorkbook.Worksheets.Item(1).Range("A1:I9").Find(What:="Total", LookAt:=xlWhole)
     Let SomeTotal = SomeTotal + rng.Offset(0, 1).Value
     Let FileName = Dir '
    Loop '  do while you find the file names you want ==========
Let ThisWorkbook.Worksheets.Item(1).Range("A10").Value = SomeTotal
End Sub

You would probably need to change the ranges a bit to suit your actual data layout in your record files , and you might want to use a spare worksheet for your temporary range - that would be tidier ........
This code brings in a much bigger temporary range, and uses the second worksheet for that temporary range.

Code: Select all

Sub SUMfromRangeinClsdWkBksInFolder2()
Dim FileName As String:
 Let FileName = Dir("C:\Users\Elston\Desktop\YassersFolder\*record*", vbNormal) ' 
'Do do Looping while you find the file names you want =========
    Do While Not FileName = ""
     Let ThisWorkbook.Worksheets.Item(2).Range("A1:M45").Value = "=" & "'" & "C:\Users\Elston\Desktop\YassersFolder\" & "[" & FileName & "]Tabelle1'!A1"
     Let ThisWorkbook.Worksheets.Item(2).Range("A1:M45").Value = ThisWorkbook.Worksheets.Item(2).Range("A1:M45").Value ' Need this extra line or the Range.Find will not work - This code line changes the formula in the cell  to its value
    Dim SomeTotal As Double '
    Dim rng As Range: Set rng = ThisWorkbook.Worksheets.Item(2).Range("A1:M45").Find(What:="Total", LookAt:=xlWhole)
     Let SomeTotal = SomeTotal + rng.Offset(0, 1).Value
     Let FileName = Dir '
    Loop '  do while you find the file names you want ==========
Let ThisWorkbook.Worksheets.Item(1).Range("A10").Value = SomeTotal
End Sub

By the way, I use this "temporary range from a closed workbook" approach a lot in my own work. I have found it to be a lot more effecient then many more "professional" ways of getting at data and searching for data in data files....

[YasserKhalil]

Thanks a lot. I am trying to understand the code
In this line
Let ThisWorkbook.Worksheets.Item(1).Range("A1:I9").Value = "=" & "'" & "C:\Users\Elston\Desktop\YassersFolder\" & "[" & FileName & "]Tabelle1'!D14"

Will the range be equal to just one cell which is D14??

[HansV]

No, Excel will adjust the reference dynamically. The formula in A1 will refer to D14, that in A2 to D15, that in B1 to E14 etc.

If it had been

Let ThisWorkbook.Worksheets.Item(1).Range("A1:I9").Value = "=" & "'" & "C:\Users\Elston\Desktop\YassersFolder\" & "[" & FileName & "]Tabelle1'!$D$14"

then the formula would have referred to $D$14 in all cells.

[Doc.AElstein]

..
Will the range be equal to just one cell which is D14??

No it will have the same size as A1:I9 - 9x9
So it will be D14:L22

I am off now, I will try to explain it a bit better later... Edit : or Han's has / will explaint it better probably :)

[YasserKhalil]

Thank you very much for both of you. You have helped a lot
This is the final version of the working code

Code: Select all

Sub Test()
    Dim ws          As Worksheet
    Dim rng         As Range
    Dim sumTotal    As Double
    Dim spath       As String
    Dim sRng        As String
    Dim fileName    As String
    Dim count       As Long

    Application.ScreenUpdating = False
        Set ws = ThisWorkbook.Worksheets("Sheet1")
        spath = "D:\2017 - prechange\"
        sRng = "M1:Y100"
        fileName = Dir(spath & "Ebay*", vbNormal)
        
        Do While Not fileName = ""
            ws.Range(sRng).Value = "=" & "'" & spath & "[" & fileName & "]Invoice'!A1"
            ws.Range(sRng).Value = ws.Range(sRng).Value
    
            Set rng = ws.Range(sRng).Find(What:="TOTAL", LookAt:=xlWhole)
            If Not rng Is Nothing Then sumTotal = sumTotal + rng.Offset(0, 1).Value
            ws.Columns("M:Y").ClearContents
            count = count + 1
            ws.Range("A" & count).Value = fileName
    
            fileName = Dir
        Loop
    
        ws.Range("E12").Value = sumTotal
    Application.ScreenUpdating = True
End Sub

[Doc.AElstein]

I am sometime surprised that this simple way of referencing data from a closed workbook is not more often used in preference to much more advanced ways of data analysis. I need to access and search very big files constantly and I spent many months learning and experimenting and comparing all the advanced ways. In the end I found the combination of closed workbook references and efficient VBA search codes , ( which often use the Range.Find Method) , to be far more efficient.
Possibly the reason for this lack of more closed reference usage is
_the difficult syntax of the closed reference,
and possibly also
_a lack of a good understanding of some basic range referencing theory..

Here is a small explanation relevant to the range referencing used in this Thread. But it is basic theory and not specific to closed workbook referencing……
I believe Excel has two basic ways of holding internally the information for a range reference. You can instruct Excel on which way to use. One way you have to instruct Excel which way to use is by including or omitting those $s

Take a couple of simple demo ranges like these: : https://imgur.com/RtUryad" onclick="window.open(this.href);return false; . Lets say I am interested in having a look at the information from approximately around or near C3 . I want to bring that information into a spare temporary range , say, A1:B2

Consider two possibilities, with and without the $s
_1) I use the $s
Range(“A1:B2”).Value = “=$C$3”
I think Excel is instructed by the use of the $s to hold the range on the RHS in a fixed co ordinate system: My guess is that somewhere lost in the internal workings of Excel that everyone forgot about is now some “help matrix” of this form : https://imgur.com/yG282q5" onclick="window.open(this.href);return false; , or maybe this: https://imgur.com/td23d5S" onclick="window.open(this.href);return false;. That is the information presented to all ranges including our spare temporary range A1:B2
So the results for Range(“A1:B2”).Value = “=$C$3” are : https://imgur.com/jDyG3gx" onclick="window.open(this.href);return false;

_2) I don’t use $s
Range(“A1:B2”).Value = “=C3”
It is a guess from me that Excel is instructed to hold the range on the RHS as some sort of fixed vector. The next screenshot is my attempt to show the corresponding “help matrix” presented to all cells. I am sorry my graphics are a bit bad, but all the arrows should be the same length and angle. I call it a fixed vector for want of a better explanation. That fixed vector is “attached “ to all cells in my theoretical “help matrix” like this: https://imgur.com/aEhegxM" onclick="window.open(this.href);return false;
That screenshot is not very clear , so I will just show the 4 fixed vectors which are relevant for our demo spare temporary range A1:B2 : https://imgur.com/yKp2BtI" onclick="window.open(this.href);return false;
Once again apologies for the bad graphics: those 4 arrows should be exactly the same length and angle: they are the same fixed vector .. well sort of…It is all a bit more complicated than that**, but that’s probably about as much as I will get away with here, :)
So Excel uses those vectors as and when required. For our demo spare temporary range A1:B2, it uses just the 4 arrows shown in the last screenshot. It uses just those 4, because they are the ones “connected” / “attached” to the cells in our demo spare temporary range A1:B2
So those 4 connecting arrows “bring in” the 4 different values: These are the results for Range(“A1:B2”).Value = “=C3”
: https://imgur.com/oQ8rJwq" onclick="window.open(this.href);return false; , and here the results again showing the “attached” fixed vectors : https://imgur.com/FY53Gwy" onclick="window.open(this.href);return false;

_3) The mixed case.
For a combination of $s and not $s needs a book to explain**. The second reference below touches on it a bit**. It is related a bit to the mysterious phenomena of Implicit Intersection …

Alan

Refs:
https://teylyn.com/2017/03/21/dollarsigns/#comment-191" onclick="window.open(this.href);return false;
** http://www.excelfox.com/forum/showthrea ... nd-VLookUp" onclick="window.open(this.href);return false;
_.____________________________

P.s. @ Yasser, I use a code like your last one a lot. Sometimes a single code line like this one is useful to tidy up the temporary range , your Range(sRng)

Code: Select all

Let ws.Range(sRng).Value = Evaluate("=IF(ISERR(" & ws.Range(sRng).Address & ")," & """""" & ",IF(" & ws.Range(sRng).Address & "=0," & """""" & "," & ws.Range(sRng).Address & "))")

You would put that code line after this one:

Code: Select all

ws.Range(sRng).Value = ws.Range(sRng).Value