Linking to many closed files...

[dominicwellsuk]

Hi,

Looking for a bit of help on something. I have a list of files, listed using a directory search macro. This shows file path, file name etc.

In a column next to each file, I need to show a value in cell A1 of this file.

Can I just use a direct link to the cell, and if so, how do I reference the file I am getting the information from? Or is there a long winded method I will need to use? VBA is ok, but it would have to be spelt out to me, as I'm a beginner.

Your help is much appreciated as I've come so far in development of this sheet, only to be scuppered at the last hurdle.

Dom

 

[MrKowz]

Welcome to the forums!



This would require one of two methods:

1. Downloading and installing the Morefunc addin that you can find here, and using the [URL=http://xcell05.free.fr/morefunc/english/indirect.ext.htm]INDIRECT.EXT[/URL] function.
2. Creating a macro to loop through your column and create a formula next to the cells.

 

[dominicwellsuk]

Thanks MrKowz,

I'm hesitant to use an addin as this would have to run on other people's PCs as well. If I went for the Macro option, what formula would I use to link to a closed file, based on the file path as a text string?

Thanks,

Dom

 

[MrKowz]

Really, you would just have to directly link the cell.

For example, say the filepath is "C:\Excel Files\" and the filename is "foo.xls", and you are referencing cell A1 in "Sheet1" in that file, the formula would be:

='C:\Excel Files\[foo.xls]Sheet1'!A1

If you can provide an example as to how your data looks and is laid out, I can throw together a quick macro for you.

 

[dominicwellsuk]

Good stuff. My problem may actually be very simple then. In your example:

='C:\Excel Files\[foo.xls]Sheet1'!A1

... how do I make the path and filename dependent on a text string in a cell, i.e. change this path for each line in my sheet?

Layout is approximately: (Assume entries start in cell A1)

Full File Path Value I Need
N:\DST Estimations\C1000.xls ? (from cell A1 of the file shown)
N:\DST Estimations\C1005.xls ? (as above)
N:\DST Estimations\C2010.xls ? (as above)

There are 1899 files in total, and they will obviously all be closed at the time of lookup if that makes a difference.

Thanks!

 

[MrKowz]

Try:

Public Sub CreateReference()
Dim i   As Long, _
    j   As Long, _
    LR  As Long, _
    str As Variant, _
    tmp As String
    
LR = Range("A" & rows.Count).End(xlUp).row
For i = 1 To LR
    tmp = ""
    str = Split(Range("A" & i).Value, "\")
    For j = LBound(str) To UBound(str) - 1
        tmp = tmp & str(j) & "\"
    Next j
    tmp = tmp & "[" & str(UBound(str)) & "]Sheet1'!A1"
    Range("B" & i).Formula = "'=" & tmp
Next i
End Sub

 

[MrKowz]

Ignore the above code - I messed up in one spot. Use this:

Public Sub CreateReference()
Dim i   As Long, _
    j   As Long, _
    LR  As Long, _
    str As Variant, _
    tmp As String
    
LR = Range("A" & rows.Count).End(xlUp).row
For i = 1 To LR
    tmp = "'"
    str = Split(Range("A" & i).Value, "\")
    For j = LBound(str) To UBound(str) - 1
        tmp = tmp & str(j) & "\"
    Next j
    tmp = tmp & "[" & str(UBound(str)) & "]Sheet1'!A1"
    Range("B" & i).Formula = "=" & tmp
Next i
End Sub

 

[dominicwellsuk]

Tip top MrKowz, we seem to be getting there.

I now get what seems to be a link to the correct file path in column B for all the filepaths shown in column A, however they are shown as a text string and the value I am after is not showing. The make it show, I have to change the format of the formula slightly, then physically link to the other file from a file structure.

Should your example whow the value in A1 automatically?

Thanks.

 

[MrKowz]

Tip top MrKowz, we seem to be getting there.

I now get what seems to be a link to the correct file path in column B for all the filepaths shown in column A, however they are shown as a text string and the value I am after is not showing. The make it show, I have to change the format of the formula slightly, then physically link to the other file from a file structure.

Should your example whow the value in A1 automatically?

Thanks.

Aye, I removed a single quote in the wrong place in the code. Try the one I just posted.

 

[dominicwellsuk]

It still seems to be entering it as a text string, but I can't see why it would do this from your code. Any ideas?