[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
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
Medium Priority
?
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 3
17 Comments
 
LVL 49

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 49

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 1000 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 49

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
 
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 49

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 49

Assisted Solution

by:Martin Liss
Martin Liss earned 1000 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 49

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

650 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