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

Using INDIRECT in Excel 2010 linking to another workbook

Good Morning,

I am wondering if anyone can help me please.
I have successfully been able to use the INDIRECT function to access a worksheet within the same workbook using the below syntax:

=SUM(INDIRECT("'"&A1&"'!F73:F82"))

where A1 contains the name of the worksheet in question.

I would now like to access a worksheet within a different workbook and am trying to use INDIRECT for the same reason.
I have been on a number of sites, including Microsoft's site but the syntax that I am trying to use does not work:

This is my latest syntax:

=INDIRECT("'["&A1&"]'"&A2&"!"&"A3")

A1 contains "\\ipaddress\subfolder\file.xlsx"
A2 contains "Sheet1"
A3 contains "B3"

Any help would be most appreciated.

Many thanks
0
gcz
Asked:
gcz
2 Solutions
 
Elton PascuaCommented:
Is the target workbook open?

If not, below is a workaround to get it from closed workbooks.

http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

Open in new window

0
 
SteveCommented:
The following Function may do the task you need using ADO...

Function GetValueFromWorkbook(strSource As String, strSheet As String, strRange As String) As String
    Dim adoConnection       As Object
    Dim rcdSource           As Object
    Dim lngLoop             As Long
    
    Set adoConnection = CreateObject("ADODB.Connection")
    Set rcdSource = CreateObject("ADODB.Recordset")
    
    adoConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strSource & ";Extended Properties=""Excel 12.0 XML;HDR=NO"";"
    rcdSource.Open "SELECT * FROM [" & strSheet & "$" & strRange & ":" & strRange & "]", adoConnection
    GetValueFromWorkbook = rcdSource(0).Value
    rcdSource.Close
    
    Set rcdSource = Nothing
    Set adoConnection = Nothing

End Function

Open in new window


See example file attached.
GetCellFromFile.xlsm
0
 
gczAuthor Commented:
Thanks everyone for the replies.

My target workbook is not open so I will try both options that you suggest and see how I get on.

Thanks again for yoru replies
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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