Vlookup into Large Data Range in Closed File

[AHB37]

My problem is this:

I have a large-ish .xls data file (~10k rows, 10 columns) that I download every week and save on my hard drive.

I have a template file that then runs vlookups into this file and summarizes certain information. I would like to be able to dynamically reference this outside data file (be able to change one cell in the template file, and have it update all the file references to a different week's external data file.) All of this while none of the external files are open.

There are about 50 cells that would have to be coded this way.

A couple things I have tried.

-Indirect.ext (through the morefunc addin) cannot do vlookups

-Harlan Grove's Pull Function: Works, but seems prohibitly expensive in terms of calculation time. Each cell is taking more than 5 minutes to calculate, and doing a bunch of cells at once locks up my xls. Does this sounds right for a vlookup onto a range of more than 10k rows or am I doing something dumb?

I wouldn't mind just copying and pasting each file into a tab in the template workbook each week, but the problem is that I have between around 50 of these "template" workbooks that I would like to have updated every time I get a new data file.

Any thoughts? Please let me know if any of the above is unclear, or if you need any more info. Sorry if this has been posted, I did search of google and this forum and didn't spot anything.

Cheers!

 

[GlennUK]

Why don't you just do Edit/Links and point to the new data every week?

 

[AHB37]

Boss wants 'push button' simplicity. So pretty mcuh all he is willing to do is change the date in one cell of the spreadsheet, and wants to see all data updated accordingly.

I agree that what you suggest is the most practical, just curious if what he wants can be done even if it requires a bit more work on my end.

Thanks for the response.

 

[GlennUK]

Well, just have a Worksheet_Change event in that sheet and change the links to point at the required file in VBA then?

 

[AHB37]

Thanks Glen. I had not heard of a Worksheet_change event.

If understand correctly, all I would need would be a code that did the following.

When date input cell changes ($E$1),

-Update all links to new data file (per new date in cell $E$1)

(I have a cell, $E$3, that contains the full file path of the correctdata file, based on the date in $E$1)


I tried recording a macro that involves updating links, but I'm not sure how to edit it so that it use the value in $E$3 to determine the new file to link to. I also don't know how to use the Worksheet_change functionality.

How time consuming would it be to create this VBA code?

Worksheet_change is not a functionality in excel 2002 correct?

Thank you for humoring my ignorance.

Cheers!

 

[GlennUK]

... Worksheet_change is not a functionality in excel 2002 correct?

...

... what makes you say that?

What happens when you right-click the sheet tab? Do you get a context menu appear ... and is there a menu option of View Code there? And what happens if you choose that?

 

[AHB37]

Nvm. Misread something while looking into Worksheet_change.

 

[GlennUK]

In the sheet code area ( as mentioned in my last post ), put this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$1" Then
        If Dir(Me.Range("$E$3").Value) = "" Then
            ' file does not exist
            ' show a message
            MsgBox "Latest Data File not found. Links not changed."
        Else
            alinks = ThisWorkbook.LinkSources
            ThisWorkbook.ChangeLink Name:=alinks(1), NewName:= _
                Me.Range("$E$3").Value _
                , Type:=xlExcelLinks
        End If
    End If

End Sub

 

[AHB37]

Thanks so much.

What format the value of cell E3 be? Full path? Or just new file name?

 

[AHB37]

Answered my own question. Full Path.

Thanks again, it is working great.