Put cell value from another workbook into a variable

[rdshadden]

I want my visual basic code to get the value of a cell in another
workbook and put into a variable. Here is an example that does not
work:

Fund1Shares = "'E:\Finance\[Fund.xls]Fund'!$B$3"

When I write the value of Fund1Shares to a message box I get
'E:\Finance\[Fund.xls]Fund'!$B$3 and the not the value in $B$3

Any help would be appreciated.

 

[David Coleman]

Hi

The following should do what you're after.

All the best

David

=======

Sub extlink()
Dim extvalue As String 'change this to integer / long / double /
whatever

'remove the next line if the fund spreadsheet is always open when you
want to get the value
Application.Workbooks.Open ("e:\finance\fund.xls") 'open the file to
get the value

'the next line gets the required value
extvalue = Workbooks("fund.xls").Worksheets("fund").Range("C3").Value

'remove the next line if you don't want to close the file when you've
got the value
Workbooks("fund.xls").Close 'close the newly opened file as it's not
needed

'the next line displays the retrieved value
MsgBox (extvalue)

End Sub

=====

 

[Nigel]

To get an external workbook value you must open it first, get the value(s)
then close it. .... try this

Sub getvalue()

Dim aWB As Workbook, bWB As Workbook, Fund1Shares
Set aWB = ActiveWorkbook

Workbooks.Open Filename:="E:\Finance\Fund.xls"
Set bWB = ActiveWorkbook

Fund1Shares = bWB.Sheets("Fund").Range("B3")

bWB.Close

MsgBox Fund1Shares

End Sub

 

[Dave Peterson]

John Walkenbach has a routine that can get values from a closed workbook:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

And you could also just build a formula in an empty cell, retrieve the value and
then clean up that helper cell.

I want my visual basic code to get the value of a cell in another
workbook and put into a variable. Here is an example that does not
work:

Fund1Shares = "'E:\Finance\[Fund.xls]Fund'!$B$3"

When I write the value of Fund1Shares to a message box I get
'E:\Finance\[Fund.xls]Fund'!$B$3 and the not the value in $B$3

Any help would be appreciated.