VBA to to open and close list of files

[olliesouth]

Hello - I am hitting the common INDIRECT snag where a #REF error is generated if the file is not open - and I have to use an INDIRECT because there are a great number of dynamic variables in the file paths in question.

I am hoping the fix here is to have excel open all the files in question (it's a lot - currently 364 CSV files) so that the INDIRECT formulas all populate a result, and then have excel close them all again when done (I've noticed if you open the file then close it, the INDIRECT result will remain in place until the next time you update a cell and it attempts to re-calculate).

At the moment the files to open are all in a sheet called FilePaths (range D3:P30 but that range should be easily expanded / reduced), and each cell looks something like this :

Formula : ="'C:\Sales"&D$2&"_excel"&$B3&"\["&$C3&"_"&TEXT($A3+1,"yyyymmdd")&".csv]"&$C3&"_"&TEXT($A3+1,"yyyymmdd")&"'!$A$1"
Result : 'C:\Sales\Client1_excel\wraps\[wrap_20190716.csv]wrap_20190716'!$A$1

Then on a second sheet called Results is just an INDIRECT formula linking to the same cell on the FilePaths sheet. For example, Results!D3 contains =INDIRECT(FilePaths!D3)

Hope that's clear! Would really appreciate your help on this!

 

[Yongle]

try this
- it will take several minutes to run especially if underlying files are complex in any way

Sub OpenListOfFiles()
    Dim List As Range, fPath As Range, wb As Workbook, problems As String
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set List = Sheets("FilePaths").Range("D3:P30")
    
    For Each fPath In List
        On Error Resume Next
        If Dir(fPath) <> "" Then
            Set wb = Workbooks.Open(fPath)
            DoEvents
            wb.Close False
        Else
            If fPath <> "" Then problems = problems & vbCr & fPath.Address & vbTab & fPath
        End If
        On Error GoTo 0
    Next fPath
    
    Application.Calculation = xlCalculationAutomatic
    If problems <> "" Then MsgBox problems, vbExclamation, "Bad file paths"
End Sub

Presumably there are no password restrictions on opening the workbooks etc

range D3:P30 but that range should be easily expanded / reduced

- can last entry in column D be used to determine last row and last entry in row 3 determine last column ?

 

[olliesouth]

Thanks Yongle! It took some messing with the way I had the file paths generating but this now runs perfectly!

To your two questions about column D and row 3, the answers are yes and yes the last entry in D and 3 will always be the last one that needs to be checked.

 

[Yongle]

the last entry in D and 3 will always be the last one that needs to be checked.

add 2 variables

Dim c As Long, r As Long

and replace

Set List = Sheets("FilePaths").Range("D3:P30")

with

    With Sheets("FilePaths")
        c = .Cells(3, .Columns.Count).End(xlToLeft).Column
        r = .Cells(.Rows.Count, "D").End(xlUp).Row
        Set List = .Range("D3", .Cells(r, c))
    End With