I have a closed workbook, Closed.xlsm. ( It is the same folder as an open workbook, so I can get the path to it from a code line in the open workbook like ThisWorkbook.Path )
ExecuteExcel4Macro
_a) From that closed workbook (Closed.xlsm) I can get a value, using ExecuteExcel4Macro , from a single cell like this
somevalue = application.ExecuteExcel4Macro("'C\somepath\[Closed.xlsm]SomeSheet'!R1C1")
somevalue = application.ExecuteExcel4Macro("'" & ThisWorkbook.Path & "\[Closed.xlsm]SomeSheet'!R1C1")
_b) I cannot do something similar, using ExecuteExcel4Macro , to return a 2D array from Closed.xlsm like
some2Darray() =Application.ExecuteExcel4Macro("'C\path\[Closed.xlsm]somesheet'!R1C1:R5C2")
That last code line will not work. It will error!
However, I can do something similar to _a) and _b) using a similar approach with a simple closed workbook reference.
Closed Workbook Reference
_a) A simple formula , placed in an arbitrary spare cell , say the first, A1 , will return the value from a cell in a closed workbook
So for example, to return the value from the first cell in a closed workbook into the first cell of an open workbook, a simple code line like this can be used in the open workbook:
Range("A1").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet' R1C1"
This is what the closed workbook, Closed.xlsm, looks like
Closed_xlsm.JPG : https://imgur.com/CBbun1h
Attachment 2370
If Closed.xlsm is closed and in the same folder as another workbook, macro.xlsm, then either of the following simple code lines in the following macro will return me the value from the first cell in the closed workbook, into the first cell in macro.xlsm
So after running the simple macro I will get this:
ClosedRefInCellA1.jpg : https://imgur.com/6N25bDe
Attachment 2371
Code:
'
' https://www.excelforum.com/excel-programming-vba-macros/1286750-getting-2d-array-from-a-closed-workbook-using-executeexcel4macro.html
Sub Testie()
Range("A1").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!R1C1"
Range("A1").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!$A$1"
Workbooks("macro.xlsm").Worksheets.Item(1).Range("A1").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!$A$1"
ThisWorkbook.Worksheets.Item(1).Range("A1").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!$A$1"
Let ThisWorkbook.Worksheets.Item(1).Range("A1").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!$A$1"
End Sub
_b) Lets say that I want to get the information from a "2D array" in the closed workbook. Lets say I want the information from the first 2 columns, and first 5 rows, like
R1C1:R5C2 or $A$1:$B$5
I can do this using closed workbook references. One way is to use the same fixed vector reference ** within a closed workbook reference applied across some spare range in the open workbook, macro.xlsm.
Lets say I want to put the reference into the first 2 columns and first 5 rows of the open workbook, macro.xlsm.
The fixed vector reference to apply to this range will be that, from a worksheets first cell to the worksheets first cell. In other words , A1 or R[0]C[0], ( or RC, as R defaults to R[0] etc.. ). I apply that reference to the first two columns and first 5 rows in the workbook, A1:B5
So if this is my closed workbook:
Closed_xlsm (_b).JPG : https://imgur.com/3f9U6M1
Attachment 2372
then either of the following simple code lines in the following macro will return me the range of values from the range in the closed workbook, into the first two columns and first 5 rows in macro.xlsm
So after running the macro, Sub TestieClosedArrayRangeReference() , my first worksheet in macro.xlsm , will look like this
ClosedRefsInCellsA1toB5.JPG https://imgur.com/SzvBlea
Code:
Sub TestieClosedArrayRangeReference() ' http://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing?p=11431&viewfull=1#post11431
Range("A1:B5").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!R[0]C[0]"
Range("A1:B5").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!RC"
Range("A1:B5").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!A1"
Workbooks("macro.xlsm").Worksheets.Item(1).Range("A1:B5").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!A1"
ThisWorkbook.Worksheets.Item(1).Range("A1:B5").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!A1"
Let ThisWorkbook.Worksheets.Item(1).Range("A1:B5").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!A1"
End Sub
Get 2D array from closed workbook
Finally, if I want to put that range of values into an array, then I simply use the available single line capture of a range of values using the .Value Property "Method" applied to that range in the macro.xlsm. ( After this I can delete, ( Clear ) , the temporary range of values).
As example: The following macro is in the uploaded file macro.xlsm. Download both macro.xlsm and Closed.xlsm into the same Folder.
Open just macro.xlsm. Leave Closed.xlsm closed. Run Sub Get2DArrayfromClosedWorkbook()
The values in range A1:B5 from the closed workbook, Closed.xlsm, will be placed in the array, MyArr()
Code:
Sub Get2DArrayfromClosedWorkbook()
Let ThisWorkbook.Worksheets.Item(1).Range("A1:B5").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!A1"
Dim MyArr() As Variant
Let MyArr() = ThisWorkbook.Worksheets.Item(1).Range("A1:B5").Value
ThisWorkbook.Worksheets.Item(1).Range("A1:B5").Clear
MsgBox Prompt:="As example of my array content, here is value from B4 in closed workbook:" & vbCrLf & "MyArr(4, 2) value is """ & MyArr(4, 2) & """"
End Sub
Ref
fixed vector reference ** In simple terms , this means simply a range reference in relative terms, ( so neglecting the $ in Column Letter and row number notation, or including the [ ] in row number and column number notation: https://teylyn.com/2017/03/21/dollarsigns/#comment-191
Ref
fixed vector reference ** In simple terms , this means simply a range reference in relative terms, ( so neglecting the $ in Column Letter and row number notation, or including the [ ] in row number and column number notation: https://teylyn.com/2017/03/21/dollarsigns/#comment-191
Reply With Quote
Bookmarks