qeng
asked on
How to apply value returned by a vba sub to the active cell in an Excel 2010 workbook
Using: Excel 2010
I have a subroutine which correctly displays the value I need through the following command:
Sub SomeName()
...
MsgBox ExecuteExcel4Macro(returne
end sub
Instead of this value being displayed in (of course) a Message Box, how can I assign the value returned by 'ExecuteExcel4Macro(return
Another related piece of info:
At the moment (I'm still developing), I'm hard coding the external workbook name in the sub which is passed to ExecuteExcel4Macro. I just wanted to see if I could get the sub to return a cell value from a closed workbook on a network drive. It does, but as it's coded, it's returning the value in a msgBox as noted above. I need it to go into a cell in the workbook.
I have a master workbook that calls this sub, which creates a table by pulling in several hundred workbook filenames (let's say they are in Column A and the pulled-in workbooks are called: extWB1.xlsx, extWB2.xlsx, etc.) from a folder on the network drive and hyperlinks the filenames. The workbooks all have identical structures but each contain different data.
Now, and this is where I'm stuck, I would like for example cell B2 in my master workbook to use the workbook name in cell A2, say 'extWB1.xlsx', and to fetch a value, say in cell X1, of that workbook (keep in mind that extWB1.xlsx is a closed workbook) and assign that value to cell B2.
I've figured out how to strip the URL text from the hyperlinked filename.
I've figured out how to get a sub to return the value in cell X1, but as mentioned above, I only know how to display that value in msgBox, not apply it to a cell.
I also don't know how to pass the URL text to the sub which fetches the value from X1 in extWB1.xlsx.
I'm wondering if my sub should instead be converted to a function, say, Function GoFetch(argument1) where I pass the URL text through 'argument1' and using =GoFetch (URLtext) in cell B2 of the master workbook returns the value which is presently being sent back in the msgBox by my existing sub.
The idea of course is to use the Table properties in the master workbook to use the same function in B3, B4, B5 etc to return the X1 values of the workbooks named in A3, A4, A5, etc.
I have a subroutine which correctly displays the value I need through the following command:
Sub SomeName()
...
MsgBox ExecuteExcel4Macro(returne
end sub
Instead of this value being displayed in (of course) a Message Box, how can I assign the value returned by 'ExecuteExcel4Macro(return
Another related piece of info:
At the moment (I'm still developing), I'm hard coding the external workbook name in the sub which is passed to ExecuteExcel4Macro. I just wanted to see if I could get the sub to return a cell value from a closed workbook on a network drive. It does, but as it's coded, it's returning the value in a msgBox as noted above. I need it to go into a cell in the workbook.
I have a master workbook that calls this sub, which creates a table by pulling in several hundred workbook filenames (let's say they are in Column A and the pulled-in workbooks are called: extWB1.xlsx, extWB2.xlsx, etc.) from a folder on the network drive and hyperlinks the filenames. The workbooks all have identical structures but each contain different data.
Now, and this is where I'm stuck, I would like for example cell B2 in my master workbook to use the workbook name in cell A2, say 'extWB1.xlsx', and to fetch a value, say in cell X1, of that workbook (keep in mind that extWB1.xlsx is a closed workbook) and assign that value to cell B2.
I've figured out how to strip the URL text from the hyperlinked filename.
I've figured out how to get a sub to return the value in cell X1, but as mentioned above, I only know how to display that value in msgBox, not apply it to a cell.
I also don't know how to pass the URL text to the sub which fetches the value from X1 in extWB1.xlsx.
I'm wondering if my sub should instead be converted to a function, say, Function GoFetch(argument1) where I pass the URL text through 'argument1' and using =GoFetch (URLtext) in cell B2 of the master workbook returns the value which is presently being sent back in the msgBox by my existing sub.
The idea of course is to use the Table properties in the master workbook to use the same function in B3, B4, B5 etc to return the X1 values of the workbooks named in A3, A4, A5, etc.
Martin Liss
ActiveCell.Value = ExecuteExcel4Macro(returne
ASKER
Hello again and thx Martin.
Logically, I can see that this will work.
How can I set ActiveCell to start at say NamedRange (say I called D2 'NamedRange') and step downward to D3, D4, etc. only as long as there are filenames in column A?
Logically, I can see that this will work.
How can I set ActiveCell to start at say NamedRange (say I called D2 'NamedRange') and step downward to D3, D4, etc. only as long as there are filenames in column A?
I'm confused as to what you want to do. An ActiveCell is just that - one cell, so please give me more information.
ASKER
I might be anywhere in the workbook but wanted the macro to start populating at a specific named cell (this way it will keep its reference even if columns are later added), then go down one one row, and execute the sub again, then go down one, etc. until it runs out of records in the table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Why would you need to execute the macro multiple times since the macro could be written to affect all the rows in the range at the same time like this.
Sub MyMacro()
Dim cel As Range
Dim intCounter As Integer
intCounter = 1
For Each cel In Range("MyNR") ' MyNR is a Named Range
cel.Value = intCounter
intCounter = intCounter + 1
Next
End SubASKER
Folks,
My problem is that I don't know the range ahead of time (I know how many columns but not how many rows).
I have Macro1 which loads a list of Excel filenames (this list is dynamic, depending on the number of workbooks in one or more folders). All of those Excel files have the same structure but different data in them. The value i'm after in each one of those files is always in the same cell, say X5.
After running Macro 1, beside the Excel filename, for each row of the table populated by Macro1, I want a second macro to run, say Macro2, which using the filename, places a value of cell X5 from that unopended Excel file.
I'm able, without using the second Macro in the spreadsheet, to manually enter on a single row Excel's EXTERNAL built-in function to correctly pull in the value of X5 for 'filenameonrow1.xls', without opening filenameonrow1.xls (I have hundreds of such workbooks to process). My problem is that if can't get that built-in EXTERNAL function to be replicated for each row in the dynamic table.
I'll post this Comment before I lose it and add the syntax of the EXTERNAL function I'm using.
My problem is that I don't know the range ahead of time (I know how many columns but not how many rows).
I have Macro1 which loads a list of Excel filenames (this list is dynamic, depending on the number of workbooks in one or more folders). All of those Excel files have the same structure but different data in them. The value i'm after in each one of those files is always in the same cell, say X5.
After running Macro 1, beside the Excel filename, for each row of the table populated by Macro1, I want a second macro to run, say Macro2, which using the filename, places a value of cell X5 from that unopended Excel file.
I'm able, without using the second Macro in the spreadsheet, to manually enter on a single row Excel's EXTERNAL built-in function to correctly pull in the value of X5 for 'filenameonrow1.xls', without opening filenameonrow1.xls (I have hundreds of such workbooks to process). My problem is that if can't get that built-in EXTERNAL function to be replicated for each row in the dynamic table.
I'll post this Comment before I lose it and add the syntax of the EXTERNAL function I'm using.
Did you try my suggestion as the macro which i gave you will work from row-2 in a column till the last row of A Column and will exceute this macro for each value which it found on the cell of A Column...
ASKER
Thx Saurabh, I will check your suggestion out. I am going back through my files from a few weeks ago to pull out the example of the function I'm trying to copy.
I've requested that this question be closed as follows:
Accepted answer: 0 points for qeng's comment #a40629801
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Accepted answer: 0 points for qeng's comment #a40629801
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
The solution which i provided takes care of what the author is looking for as in in executing the macro for each cell in a row which is essentially what he is looking for...
ASKER
Folks, my apologies, I have been swamped on other work. I will return to this question today and provide appropriate feedback. Thank you Saurabh and Martin. I will get back to this today.
ASKER
Hi Martin,
(Saurabh, I'll come back to yours once I've figured out the tweak with Martin's)
I can get 'ActiveCell.Value = ExecuteExcel4Macro(returne
Method 'Range' of object "_Global" failed
which traces to the following line where cellRef is used:
Ret = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(Tru
It's not clear to me why I can use an explicit cell reference (in this case "X100") and it works, but I can't use the rangeName associated with the same cell
Thx
--------------------------
Sub GetTotal()
Dim wbPath As String, wbName As String
Dim wsName As String, cellRef As String
Dim Ret As String
wbPath = "C:\Users\my.name\Desktop\
wbName = "MyWorkbook.xlsx"
wsName = "MyWorksheet"
cellRef = "X100"
Ret = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(Tru
ActiveCell.value = ExecuteExcel4Macro(Ret)
End Sub
(Saurabh, I'll come back to yours once I've figured out the tweak with Martin's)
I can get 'ActiveCell.Value = ExecuteExcel4Macro(returne
Method 'Range' of object "_Global" failed
which traces to the following line where cellRef is used:
Ret = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(Tru
It's not clear to me why I can use an explicit cell reference (in this case "X100") and it works, but I can't use the rangeName associated with the same cell
Thx
--------------------------
Sub GetTotal()
Dim wbPath As String, wbName As String
Dim wsName As String, cellRef As String
Dim Ret As String
wbPath = "C:\Users\my.name\Desktop\
wbName = "MyWorkbook.xlsx"
wsName = "MyWorksheet"
cellRef = "X100"
Ret = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(Tru
ActiveCell.value = ExecuteExcel4Macro(Ret)
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.