2

I'm working on dynamic external workbook data referencing in Excel, can someone please help considering the following:

In my workbook in A2 I have ='C:\'&A1&' Reports\[1.xls]Sheet1'!C1)

A1 will contain either

Folder A
Folder B

So depending on the value in A1, I want to point to either

D:\Folder A Reports\1.xls
D:\Folder B Reports\1.xls

How can I achieve this?

Thank you!

Community
  • 1
  • 1
Andrew
  • 148
  • 1
  • 11
  • You could use an IF statement or a VLOOKUP table. This of course assumes you just need to return a value dependent on a lookup value. – Brandon Barney Feb 26 '17 at 22:26
  • Thank you for your help! I was just hoping to have Folder A populate in the path for A2, like calling a variable. Is it not possible to do it this way? – Andrew Feb 26 '17 at 22:51
  • It is certainly possible, but I apologize as I am not completely sure what you are trying to accomplish. You can certainly have the path populate based on the value of A1, and there are a few ways of accomplishing this. As I noted, a vlookup is one way of doing this. I am getting the feeling though that you want a formula to find the path on it's own without you providing it in a table? This is possible as well, but this will enter the realm of VBA and not just excel formulas. – Brandon Barney Feb 26 '17 at 23:02
  • http://stackoverflow.com/questions/9259862/executeexcel4macro-to-get-value-from-closed-workbook – Slai Feb 27 '17 at 02:01

3 Answers3

2

Indirect is the function you need:

=INDIRECT("'C:\" & A1 & " Reports\[1.xls]Sheet1'!C1")

The problem I see though is it won't resolve if the sheet is not open.

Dan Donoghue
  • 6,056
  • 2
  • 18
  • 36
  • Thank you for your answer. If I hard code the path, I can simply open the workbook and close it,and the data will update... So I've written a macro that will open all the spreadsheets I need and close them... For the INDIRECT function, must the spreadsheets remain open, or can I use the same macro to open and close my external sources? – Andrew Feb 26 '17 at 23:01
  • Open it, refresh it, close it and it will remain with the answer. I have to ask though, why wouldn't you just copy the data from the sheet with code and put it into your target sheet as a value with no dependency at runtime? – Dan Donoghue Feb 26 '17 at 23:03
  • It's too much to do manually. A lot of tabs and sources... Can't wait to go home and try it out. Thanks again! – Andrew Feb 26 '17 at 23:17
1

As INDIRECT doesn't work on closed books and it is expensive to have to open them then the normal soutions for this are to

  1. create a dirty link with VBA.
  2. Use Indirect.Ext from the Morefunc addin.
  3. My preferred approach, use XLM which Harlan Grove has improved in his pull function below.

To use for your purpose:

=pull("'C:\"&A1&"\"&"[1.xls]Sheet1'!C1")

From https://numbermonger.wordpress.com/2012/02/11/excel-pull-function-creating-dynamic-links-to-closed-workbooks/

Pull function

Function pull(xref As String) As Variant
'inspired by Bob Phillips and Laurent Longre
'but written by Harlan Grove
'-----------------------------------------------------------------
'Copyright (c) 2003 Harlan Grove.
'
'This code is free software; you can redistribute it and/or modify
'it under the terms of the GNU General Public License as published
'by the Free Software Foundation; either version 2 of the License,
'or (at your option) any later version.
'-----------------------------------------------------------------
'2004-05-30
'still more fixes, this time to address apparent differences between
'XL8/97 and later versions. Specifically, fixed the InStrRev call,
'which is fubar in later versions and was using my own hacked version
'under XL8/97 which was using the wrong argument syntax. Also either
'XL8/97 didn't choke on CStr(pull) called when pull referred to an
'array while later versions do, or I never tested the 2004-03-25 fix
'against multiple cell references.
'-----------------------------------------------------------------

'2004-05-28
'fixed the previous fix - replaced all instances of 'expr' with 'xref'
'also now checking for initial single quote in xref, and if found
'advancing past it to get the full pathname [dumb, really dumb!]
'-----------------------------------------------------------------
'2004-03-25
'revised to check if filename in xref exists - if it does, proceed;
'otherwise, return a #REF! error immediately - this avoids Excel
'displaying dialogs when the referenced file doesn't exist
'-----------------------------------------------------------------
Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, C As Range, n As Long
'** begin 2004-05-30 changes **

'** begin 2004-05-28 changes **
'** begin 2004-03-25 changes **
n = InStrRev(xref, "\")
If n > 0 Then
If Mid(xref, n, 2) = "\[" Then
b = Left(xref, n)
n = InStr(n + 2, xref, "]") - n - 2
If n > 0 Then b = b & Mid(xref, Len(b) + 2, n)
Else
n = InStrRev(Len(xref), xref, "!")
If n > 0 Then b = Left(xref, n - 1)
End If

'** key 2004-05-28 addition **
If Left(b, 1) = "'" Then b = Mid(b, 2)
On Error Resume Next
If n > 0 Then If Dir(b) = "" Then n = 0
Err.Clear
On Error GoTo 0
End If

If n <= 0 Then
pull = CVErr(xlErrRef)
Exit Function
End If
'** end 2004-03-25 changes **
'** end 2004-05-28 changes **
pull = Evaluate(xref)

'** key 2004-05-30 addition **
If IsArray(pull) Then Exit Function
'** end 2004-05-30 changes **

If CStr(pull) = CStr(CVErr(xlErrRef)) Then
On Error GoTo CleanUp 'immediate clean-up at this point

Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro

On Error Resume Next 'now clean-up can wait

n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)

Else
For Each C In r
C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1, xlR1C1))
Next C

pull = r.Value

End If

CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End If

End Function
brettdj
  • 54,857
  • 16
  • 114
  • 177
0

External references to closed files in Excel formulas can't be dynamic, so maybe something like this:

=IF(A1="Folder A", 'C:\Folder A Reports\[1.xls]Sheet1'!C1,
                   'C:\Folder B Reports\[1.xls]Sheet1'!C1)
Slai
  • 22,144
  • 5
  • 45
  • 53