Sheet Change Event VBA - Indirect Function

[Chapster5]

Hi All,

Quick question I have an Indirect formula in cell A2 on a sheet, this concatenates another workbooks name with a value in cell A1 (lets called the work book Week 1.xlsx) now the number 1 comes from cell A1 like so....

=INDIRECT("'[Week "&A1&".xlsx]Sheet1'!$A$1")

Obviously Indirect doesn't like the workbook you're referencing to, to be closed it must be open or you get #REF in the cell.


I am on a works computer so unable to install extra addons such as INDIRECT.EXT and so on so found someone posted a rough and ready work around in VBA that looks at cell A1 and when the user types a week number into there to replace the week number on the file name it runs the below VBA, which opens the file in question lets the Indirect refresh then closes it again. Trouble is it has an old class in it that is no longer relevant to Excel 2013 which is what I am using the code below is what I have I just need to know what to replace Application.FileSearch with?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    ActiveSheet.EnableCalculation = True
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationAutomatic
     
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\" ' Change the file path to your suite
        .Filename = "Week " & [A1] & ".XLSX"
         
        If .Execute(msoSortByFileName, msoSortOrderDescending, True) > 0 Then
            Workbooks.OpenText .FoundFiles(1), xlWindows
            ActiveWorkbook.Close
             
        End If
    End With
     
    ActiveSheet.EnableCalculation = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
     
End Sub

Any help much appreciated. Or is there a better way of doing it? I can insert a button called Refresh if needed to make things simpler.

Open to ideas!

Cheers

 

[Chapster5]

Just a quick bump. Anyone any ideas?

 

[Norie]

Are you looking to replace Application.FileSearch in that code?

If you are all you should need is Dir to check if the file you want actually exists.

strFileName = "C:\Week " & [A1] & ".XLSX"

If Len(Dir(strFileName)) <> 0 Then
    Workbooks.OpenText strFileName, xlWindows
End If

 

[Chapster5]

Are you looking to replace Application.FileSearch in that code?

If you are all you should need is Dir to check if the file you want actually exists.

strFileName = "C:\Week " & [A1] & ".XLSX"

If Len(Dir(strFileName)) <> 0 Then
    Workbooks.OpenText strFileName, xlWindows
End If

Great stuff thanks for the reply sorry to be a pain but what would that look like in the current code I have?

--EDIT--

Works a charm just need it to close the workbook once it's opened it?

Cheers!

 

[Chapster5]

Ignore me. All sorted now mate thank you! Just added

ActiveWindow.Close

before the EndIf and now it opens, refreshes the cells and closes :D

Happy!