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....
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?
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
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....
Code:
=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?
Code:
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