VBA to pull data from dynamically named closed workbooks

[Qliky66]

I've searched through many of the other threads about VBA for retrieving data from closed workbooks but all of them refer to a static address in the VBA itself (ex. C:\folder\folder\filename.xlsx).

In my case I need to run a macro which will update all of the formulas that I have, since they will not pull data from the file I am telling them to unless that file is currently open.

What happens is: our systems automatically generate a new file everyday in the same folder, with a filename of the date that is generated (ex. 01-01-2017), and the data that we want to pull automatically is usually in cell B11 of that, however that may change, so thats the reason we have Row B which would allow us to specify where we want our formula to look.

The problem is: excel wont use this indirect formula to look at the new file since that file is not currently open.
The solution is: a macro button which will force excel to peek in the file and display the data. I just don't know how that will work. So please help me.


The user would will open this file every couple days, drag down the formulas to correspond with the days that have passed, then click the macro button and wait for the data to upload.
Here's the structure:

DATE(rowA)	CELL(RowB)	FORMULA (Row C)
01-01-2017	B11	=indirect(" 'C:\folder\[ "&A2&".xlsx]Sheet1'! "&B2)
01-02-2017	B11	=indirect(" 'C:\folder\[ "&A3&".xlsx]Sheet1'! "&B3)
01-03-2017	B11	=indirect(" 'C:\folder\[ "&A4&".xlsx]Sheet1'! "&B4)

<tbody>
</tbody>

The DATE is the file name that I need the macro to re-open (/open if its the first time).

Your thoughts would be GREATLY appreciated.
Thank you

 

[John_w]

Re: VBA to pull data from dynamiccaly named closed workbooks

An INDIRECT formula which references a cell in another workbook will only pull that cell value if that workbook is open. Otherwise the formula displays the #REF! error.

Therefore this code opens each dated workbook in column A and creates the formula in column C.

Public Sub Open_Workbooks_and_Create_Formulas2()

    Dim r As Long
    
    Application.ScreenUpdating = False
    
    With ThisWorkbook.ActiveSheet
        
        For r = 2 To .Cells(Rows.Count, "A").End(xlUp).Row

            Workbooks.Open ThisWorkbook.Path & "\" & Format(.Cells(r, "A").Value, "DD-MM-YYYY") & ".xlsx"
            
            '=INDIRECT("'C:\folder\path\["&TEXT(A2,"DD-MM-YYYY")&".xlsx]Sheet1'!"&B2)
            
            .Cells(r, "C").ClearContents
            .Cells(r, "C").Formula = "=INDIRECT(""'" & ThisWorkbook.Path & "\[""&TEXT(A" & r & ",""DD-MM-YYYY"")&"".xlsx]Sheet1'!""&B" & r & ")"
            
        Next
        
        .Activate
        
    End With
            
    Application.ScreenUpdating = True
    
End Sub

 

[Qliky66]

Re: VBA to pull data from dynamiccaly named closed workbooks

This works amazingly! Thanks so much.
As a follow-up. Is there a way to include in the VBA for the workbooks to close afterwards? Near the end of the month this can be opening 30 or some workbooks.

Thanks again?

An INDIRECT formula which references a cell in another workbook will only pull that cell value if that workbook is open. Otherwise the formula displays the #REF! error.

Therefore this code opens each dated workbook in column A and creates the formula in column C.

Public Sub Open_Workbooks_and_Create_Formulas2()

    Dim r As Long
    
    Application.ScreenUpdating = False
    
    With ThisWorkbook.ActiveSheet
        
        For r = 2 To .Cells(Rows.Count, "A").End(xlUp).Row

            Workbooks.Open ThisWorkbook.Path & "\" & Format(.Cells(r, "A").Value, "DD-MM-YYYY") & ".xlsx"
            
            '=INDIRECT("'C:\folder\path\["&TEXT(A2,"DD-MM-YYYY")&".xlsx]Sheet1'!"&B2)
            
            .Cells(r, "C").ClearContents
            .Cells(r, "C").Formula = "=INDIRECT(""'" & ThisWorkbook.Path & "\[""&TEXT(A" & r & ",""DD-MM-YYYY"")&"".xlsx]Sheet1'!""&B" & r & ")"
            
        Next
        
        .Activate
        
    End With
            
    Application.ScreenUpdating = True
    
End Sub

 

[John_w]

Re: VBA to pull data from dynamiccaly named closed workbooks

Add this after the End With:

    Dim wb As Workbook
    
    For Each wb In Application.Workbooks
        If Not wb Is ThisWorkbook Then
            wb.Close SaveChanges:=True
        End If
    Next

Alternatively, put the above code in the Workbook_BeforeClose event handler in the ThisWorkbook module to close the dated workbooks when the main workbook is closed.

 

[Qliky66]

Re: VBA to pull data from dynamiccaly named closed workbooks

Something I forgot to mention... would it be possible that if the macro cannot find a specific file for it to put in "NO DATA", then move onto the next date/file?
Right now it is getting caught up if the Column A has a date in it but there's no corresponding file for that date. (This would happen on holidays, for example).

Other than that, this works perfectly! Thanks so much.

Add this after the End With:

    Dim wb As Workbook
    
    For Each wb In Application.Workbooks
        If Not wb Is ThisWorkbook Then
            wb.Close SaveChanges:=True
        End If
    Next

Alternatively, put the above code in the Workbook_BeforeClose event handler in the ThisWorkbook module to close the dated workbooks when the main workbook is closed.

 

[John_w]

Re: VBA to pull data from dynamiccaly named closed workbooks

Something I forgot to mention... would it be possible that if the macro cannot find a specific file for it to put in "NO DATA", then move onto the next date/file?
Right now it is getting caught up if the Column A has a date in it but there's no corresponding file for that date. (This would happen on holidays, for example).

Have a look at the Dir function.

 

[Qliky66]

Re: VBA to pull data from dynamiccaly named closed workbooks

Have a look at the Dir function.

Doesn't seem to be working for me

Public Sub Open_Files_and_Create_Formulas()

    Dim r As Long
    
    Application.ScreenUpdating = False
    
    With ThisWorkbook.ActiveSheet
        
        For r = 7 To .Cells(Rows.Count, "A").End(xlUp).Row


            If Dir(ThisWorkbook.Path & "\" & Format(.Cells(r, "A").Value, "YYYY-MM-DD") & ".xls") <> "" Then
            
            Workbooks.Open ThisWorkbook.Path & "\" & Format(.Cells(r, "A").Value, "YYYY-MM-DD") & ".xls"
            .Cells(r, "C").ClearContents
            .Cells(r, "C").Formula = "=INDIRECT(""'" & ThisWorkbook.Path & "\[""&TEXT(A" & r & ",""YYYY-MM-DD"")&"".xls]Sheet1'!""&B" & r & ")"
        
       Else
       .Cells(r, "C").ClearContents
        .Cells(r, "C").Formula = "NO DATA"
        End If
        Next
        
        .Activate
        
    End With
    Dim Z As Range, X As Range
    Set Z = Range("C7:C400")
    For Each X In Z
        If X.HasFormula And X.Value > 0 Then
            X.Value = X.Value
        End If
    Next X
    
     
     Dim wb As Workbook
    
    For Each wb In Application.Workbooks
        If Not wb Is ThisWorkbook Then
            wb.Close SaveChanges:=True
        End If
    Next
    Application.ScreenUpdating = True
    
End Sub

 

[John_w]

Re: VBA to pull data from dynamiccaly named closed workbooks

Your code looks correct. In what way isn't it working? As written, the code will always execute either the true part of the If Dir line or the false part (the Else part).

Make sure the date string in the Format function matches the date string in the file names.

 

[Qliky66]

Re: VBA to pull data from dynamiccaly named closed workbooks

Thanks for your help so far John.
The code works perfectly for the part that you wrote and works until it gets down to a date that has no corresponding file. It gets hung up at:

Workbooks.Open ThisWorkbook.Path & "\" & Format(.Cells(r, "A").Value, "YYYY-MM-DD") & ".xls"

When it reaches the date that is missing -- I'm assuming because of the DIR part that I added myself must not be working properly. The error code is:

Run-time error '1004'. Method 'Open' of object 'Workbooks' failed

It seems like the <> "" part of the dir function didn't cause it to go to the ELSE statement when it should have. Any ideas?

Your code looks correct. In what way isn't it working? As written, the code will always execute either the true part of the If Dir line or the false part (the Else part).

Make sure the date string in the Format function matches the date string in the file names.

 

[John_w]

Re: VBA to pull data from dynamiccaly named closed workbooks

The Dir function call looks correct and the Workbooks.Open uses the same argument value so the Workbooks.Open should only be executed if the file exists. Try some basic debugging, stepping through the code with the F8 key and add the following code before the Dir call:

            Dim fn As String
            fn = Dir(ThisWorkbook.Path & "\" & Format(.Cells(r, "A").Value, "YYYY-MM-DD") & ".xls")
            MsgBox ThisWorkbook.Path & "\" & Format(.Cells(r, "A").Value, "YYYY-MM-DD") & ".xls" & vbNewLine & "Dir returned """ & fn & """"