Solved

How to apply value returned by a vba sub to the active cell in an Excel 2010 workbook

Posted on 2015-02-24
17
282 Views
Last Modified: 2016-02-11
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
Comment
Question by:qeng
  • 6
  • 6
  • 3
17 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40629789
ActiveCell.Value = ExecuteExcel4Macro(returnedValue)
0
 

Author Comment

by:qeng
ID: 40629801
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40629837
I'm confused as to what you want to do. An ActiveCell is just that - one cell, so please give me more information.
0
 

Author Comment

by:qeng
ID: 40629883
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
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 250 total points
ID: 40630030
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40630921
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
 

Author Comment

by:qeng
ID: 40653492
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 40653603
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
 

Author Comment

by:qeng
ID: 40653862
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40699922
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
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 40699923
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
 

Author Comment

by:qeng
ID: 40705359
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
 

Author Comment

by:qeng
ID: 40705927
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
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 250 total points
ID: 40744347
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40791063
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now