VBA to to open and close list of files

olliesouth

New Member
Joined
Aug 1, 2019
Messages
2
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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
try this
- it will take several minutes to run especially if underlying files are complex in any way

Code:
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 ?
 
Upvote 0
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.
 
Upvote 0
the last entry in D and 3 will always be the last one that needs to be checked.


add 2 variables
Code:
Dim c As Long, r As Long

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

with
Code:
    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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top