Why doesn’t VBA Evaluate(“ “) work on the reference to a closed File
Hi I have seen statements of “ Evaluate(“ “) doesn't work on the reference to a closed File “ , and have just experienced it myself.
I am asking here
_ 1 ) why
and
_2 ) If it really is true.
No rush on this one. I can live for now with that “It don’t work”.
Any insight into this could help me generally into a few other things I have been looking at recently.
To elaborate ( as briefly as I can ). Code Lines relate to the demo code below
This File: ( “myFileToClose.xlsm” ) IS CLOSED
https://app.box.com/s/8x5bugdnha4c2ybzrj9jlav503jn277y" onclick="window.open(this.href);return false;
I have CLOSED ( initially ). This is what Worksheet item 1 ( First Tab ) looks like:
Using Excel 2007 32 bit
Row\Col | A | B |
1 | HeadingCellA1 | HeadingCellB1 |
2 | CellA2 | CellB2 |
This file: ( “ClosedWorkbook.xlsm” ) IS OPEN
https://app.box.com/s/ffx8yquyouvox2ih2j5m5jwpex507t4c" onclick="window.open(this.href);return false;
This File has the demo code shown below and in the first cell of Worksheet “BracketWonk” has_...
Row\Col | A |
1 | SomeFink |
_..in it. So this is OPEN
So based on the above screen shots and File names….( “myFileToClose.xlsm” is CLOSED, and is in the same Folder as “ClosedWorkbook.xlsm” which is OPEN )_.....this is what my demo code does:
Open Workbook referencing: ( just for comparison )
Rem 2 As far as my Open workbook is concerned I can use various versions of the Worksheets Range Property ** to “get at” , for example the first Cell in the open workbook, for example
= Ws1.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1")
= Evaluate("=[ClosedWorkbook.xlsm]BracketWonk!A1")
= Evaluate("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1")
The third one here I suspect is changed “internally” by Excel to the second… and note:
_** the last two will work in a different Class Module to BracketWonk as Evalute somehow “puts” the Formula where it should go ??
_ I can get a Range Object or a string ( “SomeFink” ) returned back depending on how I declare the receiving variable.
Rem 3 Similar to Rem 2 , using instead the Application Range Object . As example , I can use the closed workbook reference on the open Workbook,
= Application.Range("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1") and once again VBA will probably I suspect be changing that reference string “internally” to the ““open” copy” File reference, so it “sees” this
= Application.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1")
Closed Workbook referencing
Rem 4 This is actually attempting the Closed workbook referencing. On the closed workbook, “myFileToClose.xlsm”
230 If I drop this string formula in a Cell,
"='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1"
then all is well and I get my closed workbook cell value of “HeadingCellA1” returned.
220 But this
Evaluate("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1")
Does not work.
( That same non working code line will work if I open that closed workbook,
290 once again I suspect VBA to be changing “internally” to the ““open” copy” File reference )
_.................
The point of all that is…
_ Evaluate seems to know ** very well what particular cell it is referring to. One of the Evaluates functions, or its simplest definitions is, that it “does” what would be done by that Cell.
_ If we take this argument a little further, … I think when Excel sees a = it goes off and looks for an appropriate macro . Which one depends on what it recognizes after the = . This ties up with this code line possibly
260 = ExecuteExcel4Macro("'" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!R1C1")
You see we don’t need the = . In fact it error if you include that – which makes sense , - As I understand it this “does” one of these macros, so it assumes you put something in the brackets to be done, so you do not need to include the = to be an indication of it to do something. I think this version of the ExecuteExcel4Macro( ) is called getting a reference, which all ties up.
So my suggestion is that maybe in principal Evaluate does work on the reference to a closed File, but does not get a chance as someone forgot not to disable for the case of an evaluate the code that changes a closed workbook reference string to an “open” workbook reference string.
I can see the reasoning for changing a closed workbook reference string to an “open” workbook reference string for Range(“ “) as there may be good reasons to prevent getting a Range Object from a Closed Workbook. But the Evaluate should be allowed to do its job of “performing a reference”
No rush on this one. I can live for now with that “It don’t work”.
I straightened out a few things in my mind about Range Objects, Properties and Referrences in posting anway :)
But any enlightenment as to why VBA Evaluate(“ “ ) doesn’t work on the reference to a closed File might reveal something to help me in my other work.
Thanks
Alan
_...
Demo Code:
Sub RangeObjectRefEileensLounge()
Code: Select all
Sub RangeObjectRefEileensLounge() ' http://www.eileenslounge.com/viewtopic.php?f=30&t=25213&p=195481#p195465 http://www.eileenslounge.com/viewtopic.php?f=30&t=25213&p=195481#p195481
10 Dim vTemp As Variant ' Variant so as to catch Errors or se what it gives Bach when it has a choice
20 Rem 1 Worksheets referencing
30 Dim Ws1 As Worksheet, Wb As Workbook '
40 Set Wb = ThisWorkbook: Set Ws1 = Wb.Worksheets("BracketWonk")
50 Dim rng As Range ' variable assigned to Range Object
60 Rem Refs "What does Excel do after it sees a =" "Getting at cell values"
70 Rem 2 Range Objects of a Worksheet. Worksheets Range Property
80 Dim GetTheStringSBach As String
90 Let GetTheStringSBach = Ws1.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1").Value 'works returns String
100 Set rng = Ws1.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1") 'works returns Returns Range Object
110 Let vTemp = Evaluate("=[ClosedWorkbook.xlsm]BracketWonk!A1") 'works returns String
120 Set rng = Evaluate("=[ClosedWorkbook.xlsm]BracketWonk!A1") 'works returns Returns Range Object
130 Set rng = Evaluate("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1") 'works returns Returns Range Object
140 Rem 3 Excel.Application Range (Object) ..referrences .. and stuff ;). Range Object
150 '3a) Open Workbook Range Object
160 Let GetTheStringSBach = Application.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1")
170 Let GetTheStringSBach = Application.Range("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1") 'Works - String probably defaults to that for the opened Workbook
180 Let vTemp = Evaluate("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1") 'Works - String probably defaults to that for the opened Workbook
190 Let vTemp = Evaluate("=[ClosedWorkbook.xlsm]BracketWonk!A1") 'Works - String probably defaults to that for the opened Workbook
200 Set rng = Application.Range("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1") 'Works - String probably defaults to that for the opened Workbook
210 Rem 4 Closed Workbook
220 Let vTemp = Evaluate("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1") 'Errors
230 Let Range("A2").Value = "='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1" 'Works in cell ( Application Range object ( ActiveSheet ) referrenced in Normal Code Module, or Range Property of Worksheet of, referrenced in Worksheet Class Module
240 ' vTemp = Evaluate("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1")
250 'Let GetTheStringSBach = Range("='" & ThisWorkbook.Path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!R1C1")'Error syntax at complie
260 Let GetTheStringSBach = ExecuteExcel4Macro("'" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!R1C1")
270 '4b Closed workbook File myFileToClose.xlsm Open ( which must be in the same Folder as This Workbook, ClosedWorkbook.xlsm, and ...
280 Workbooks.Open Filename:=ThisWorkbook.path & "\" & "myFileToClose.xlsm" '
290 Let GetTheStringSBach = Application.Range("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1") '...will...
300 Let vTemp = Evaluate("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1") 'Works, I expect the string reverts to thhe following
310 Let GetTheStringSBach = ExecuteExcel4Macro("'" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!R1C1")
320 ' close the workbook myFileToClose.xlsm ......
330 Workbooks("myFileToClose.xlsm").Close SaveChanges:=False '
End Sub
'
' Rem Ref
' http://excelmatters.com/referring-to-ranges-in-vba/#comment-185793
' http://www.excelforum.com/showthread.php?t=1154829&page=2&p=4483347&posted=1#post4483344
' '1. http://www.excelforum.com/excel-programming-vba-macros/1156725-executeexcel4macro-pulling-data-from-a-cell-in-closed-workbook-slow-for-large-file.html
' '2. http://www.excelforum.com/excel-programming-vba-macros/1161798-read-closed-wb.html
' http://www.excelforum.com/excel-programming-vba-macros/1126860-is-it-possible-to-sum-entire-column-with-out-opening-excel-2.html#Post4321006
' http://www.excelforum.com/showthread.php?t=1154829&page=2&p=4483347&posted=1#post4483344
' http://listenonrepeat.com/watch/?v=ivFYVAntpw0#Skid_Row_-_I_Remember_You
' http://www.excelforum.com/excel-programming-vba-macros/1141369-evaluate-and-differences-evaluated-array-return-needs-extra-bracket-for.html
' ' Rem Ref
' http://www.excelforum.com/excel-programming-vba-macros/1126860-is-it-possible-to-sum-entire-column-with-out-opening-excel-2.html#Post4321006
' http://www.excelforum.com/showthread.php?t=1154829&page=2&p=4483347&posted=1#post4483344
' http://listenonrepeat.com/watch/?v=ivFYVAntpw0#Skid_Row_-_I_Remember_You
' http://www.excelforum.com/excel-programming-vba-macros/1141369-evaluate-and-differences-evaluated-array-return-needs-extra-bracket-for.html
Sub RangeObjectRefMacroModul5() ' http://www.excelforum.com/showthread.php?t=1154829&page=2&p=4483347&posted=1#post4483344
Dim vTemp As Variant ' To catch Errors
30 Rem 1 Worksheets referencing
40 Dim Ws1 As Worksheet, Wb As Workbook ' , Ws2 As Worksheet '
50 Set Wb = ThisWorkbook '
60 Set Ws1 = Wb.Worksheets("BracketWonk")
70 '
80 Rem Refs "What does Excel do after it sees a =" "Getting at cell values"
90 Rem 2 Range Objects of a Worksheet
100 Dim GetTheStringSBach As String
110 Let GetTheStringSBach = Ws1.Range("A1").Value
120 Let GetTheStringSBach = Ws1.Range("A1")
130 Let GetTheStringSBach = Ws1.Range("BracketWonk!A1")
140 Let GetTheStringSBach = Ws1.Range("=BracketWonk!A1")
150 Let GetTheStringSBach = Ws1.Range("[ClosedWorkbook.xlsm]BracketWonk!A1")
160 Let GetTheStringSBach = Ws1.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1")
162 Let vTemp = Evaluate("=[ClosedWorkbook.xlsm]BracketWonk!A1") 'works
170 '
180 Rem 3 Excel Application Range ( Object )
181 '3a) Open Workbook Range Object
183 Let GetTheStringSBach = Range("A1")
186 Let GetTheStringSBach = Range("BracketWonk!A1")
190 Let GetTheStringSBach = Range("=[ClosedWorkbook.xlsm]BracketWonk!A1")
200 Let GetTheStringSBach = Excel.Application.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1")
205 Dim rng As Range: Set rng = Excel.Application.Range("=[ClosedWorkbook.xlsm]BracketWonk!A1")
210 Let GetTheStringSBach = Range("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1")
212 Let vTemp = Evaluate("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1") 'Works - String probably defaults to that for the opened Workbook
215 Set rng = Excel.Application.Range("='" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!A1")
216 '3b) Closed Workbook
217 'Let GetTheStringSBach = Range("='" & ThisWorkbook.Path & "\" & "[myFileToClose.xlsm]Sheet1'!A1") 'Error
218 Let vTemp = Evaluate("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1") 'Errors
219 Let Range("A2").Value = "='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1" 'Works in cell
220 'Let GetTheStringSBach = Range("='" & ThisWorkbook.Path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!R1C1")'Error syntax at complie
230 Let GetTheStringSBach = ExecuteExcel4Macro("'" & ThisWorkbook.path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!R1C1")
240 'Let GetTheStringSBach = ExecuteExcel4Macro("='" & ThisWorkbook.Path & "\" & "[ClosedWorkbook.xlsm]BracketWonk'!R1C1")' Error syntax at Compile
250 ' Open File myFileToClose.xlsm ( which must be in the same Folder as This Workbook, ClosedWorkbook.xlsm, and ...
260 Workbooks.Open Filename:=ThisWorkbook.path & "\" & "myFileToClose.xlsm" ' ... if you put a new value in worksheet Sheet1, Cell A1 then this...
270 Let GetTheStringSBach = Range("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1") '...will...
272 Let vTemp = Evaluate("='" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!A1") 'Works, I expect the string reverts to thhe following
280 '... return that Value to the variable GetTheStringSBach, and so will the next line...
290 Let GetTheStringSBach = ExecuteExcel4Macro("'" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!R1C1")
300 ' close the workbook myFileToClose.xlsm ......
310 Workbooks("myFileToClose.xlsm").Close SaveChanges:=False '...without saving, then the next line....--
320 Let GetTheStringSBach = ExecuteExcel4Macro("'" & ThisWorkbook.path & "\" & "[myFileToClose.xlsm]Sheet1'!R1C1")
330 ' --... will now fill GetTheStringSBach with the previous value
340 '
350 Rem Range Member Address Wonks "Cell Addressing"
360 ' Range Member Address Property https://msdn.microsoft.com/en-us/library/office/ff837625.aspx
370 Dim strAddress As String
380 Let strAddress = Range("A1").Address(RowAbsolute:=True, ColumnAbsolute:=True, ReferenceStyle:=xlR1C1, External:=False, RelativeTo:=Cells(32664, 32)) 'Format required. A link with RC for cell format. All Address arguments are demonstrated, but the last is ignored due to the first two. External:=True is required in other uses to allow acces to external Workbooks. We need to build the referrence to include the two ' ' and the Full Workbook Path. False is needed. First two and forth argumenrts are the default, and fifth is not needed here. Hence typically seen as (, , xlR1C1)
390 Debug.Print strAddress ' Returns R1C1, what we want
400 Let strAddress = Range("A1").Address(RowAbsolute:=True, ColumnAbsolute:=True, ReferenceStyle:=xlR1C1, External:=True, RelativeTo:=Cells(32664, 32)) 'A link with RC for cell format. All Address arguments are demonstrated, but the last is ignored due to the first two. External:=False is required in other uses to allow access to other open Workbooks. Here that would error and Falkse is needed. First two and forth argumenrts are the default, and fifth is not needed here. hence typically seen as (, , xlR1C1)
410 ' Returns R1C1, what we want
420 Let strAddress = Ws1.Range("A1").Address(RowAbsolute:=True, ColumnAbsolute:=True, ReferenceStyle:=xlR1C1, External:=True, RelativeTo:=Cells(32664, 32))
430 '
Dim FullPath As String: Let FullPath = ThisWorkbook.path
Dim FullFileName As String: Let FullFileName = "ClosedWorkbook.xlsm"
Dim wsBgAcsName As String: Let wsBgAcsName = "ASheetToIgnoor"
Dim RefClsdws As String: Let RefClsdws = "'" & FullPath & "\" & "[" & FullFileName & "]" & wsBgAcsName & "'" & "!": Debug.Print RefClsdws ' In Immediate Window (Ctrl+G) is seen 'H:\ALERMK2014Marz2016\NährwerteTabelle\Sept2016\Sept2016\[NutritionalValues2016.xlsx]NutritionalValues'!
Let GetTheStringSBach = ExecuteExcel4Macro(RefClsdws & Range("J" & 1 & "").Address(, , xlR1C1))
End Sub
' Rem Ref
' http://excelmatters.com/referring-to-ra ... ent-185793" onclick="window.open(this.href);return false;
' http://www.excelforum.com/showthread.ph ... ost4483344" onclick="window.open(this.href);return false;
' '1. http://www.excelforum.com/excel-program ... -file.html" onclick="window.open(this.href);return false;
' '2. http://www.excelforum.com/excel-program ... ed-wb.html" onclick="window.open(this.href);return false;
' http://www.excelforum.com/excel-program ... ost4321006" onclick="window.open(this.href);return false;
' http://www.excelforum.com/showthread.ph ... ost4483344" onclick="window.open(this.href);return false;
' http://listenonrepeat.com/watch/?v=ivFY ... member_You" onclick="window.open(this.href);return false;
' http://www.excelforum.com/excel-program ... t-for.html" onclick="window.open(this.href);return false;