• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

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(returnedValue)
end sub

Instead of this value being displayed in (of course) a Message Box, how can I assign the value returned by 'ExecuteExcel4Macro(returnedValue)' to the active cell.

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.
0
qeng
Asked:
qeng
  • 6
  • 6
  • 3
2 Solutions
 
Martin LissRetired ProgrammerCommented:
ActiveCell.Value = ExecuteExcel4Macro(returnedValue)
0
 
qengAuthor Commented:
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?
0
 
Martin LissRetired ProgrammerCommented:
I'm confused as to what you want to do. An ActiveCell is just that - one cell, so please give me more information.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
qengAuthor Commented:
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.
0
 
Saurabh Singh TeotiaCommented:
Let's Say you want to start from A2 and go till the last filled row of the a column then you can use this...

sub pop

dim rng as range,cell as range,lrow as long

lrow=cells(cells.rows.count,"A").END(XLUP).ROW

SET rng=range("A2:A" & lrow)

for each cell in rng

if cell.value<>"" then cell.value = ExecuteExcel4Macro(returnedValue)

next cell

end sub

Open in new window


Saurabh...
0
 
Martin LissRetired ProgrammerCommented:
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 Sub

Open in new window

0
 
qengAuthor Commented:
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.
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
qengAuthor Commented:
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.
0
 
Martin LissRetired ProgrammerCommented:
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.
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
qengAuthor Commented:
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.
0
 
qengAuthor Commented:
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(returnedValue)' to work in the following code if I explicitly define     cellRef = "X100" (as below), but if I try to use a named range instead, such as cellRef = "MyNamedRange", I get the error:

Method 'Range' of object "_Global" failed

which traces to the following line where cellRef is used:
    Ret = "'" & wbPath & "[" & wbName & "]" & _
          wsName & "'!" & Range(cellRef).Address(True, True, -4150)

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(True, True, -4150)

    ActiveCell.value = ExecuteExcel4Macro(Ret)

End Sub
0
 
Martin LissRetired ProgrammerCommented:
On a sheet I created a Named Range (for cell X100) and called it MyNamedRange.

Executing the Test macro puts "Hello" in X100.

Sub test()
Range("MyNamedRange") = x
End Sub

Function x() As String
    x = "Hello"
End Function

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 6
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now