VBA To Open & Close Files from a specified Folder

[Herbiec09]

Hi All,

I was wondering if someone could assist me with a code that opens files from a specified location and then closes them again without saving any changes (to allow refreshing of links).

We run a consolidation model that requires the opening of a lot of files for the final file to update, and it would be great if we had a code to automatically open all files and then go back and close them again.

I have attached a spreadsheet with all the file paths, folders and file names that need to be opened and closed.

Any assistance would be greatly appreciated.

Thank you

Herbz

[Leith Ross]

HelloHerbiec09,

Are the files to be opened in column "A"? Will these update the links in column "E"?

[Herbiec09]

HelloHerbiec09,

Are the files to be opened in column "A"? Will these update the links in column "E"?

Hi Leith,

Sorry for the confusion, I need a code that only opens the links in column E. The name of the file that needs to be opened is in column D. The code will also need to close these files without saving changes once it has opened them.

Please ignore the rest for now.

Many Thanks

Herbert

[Leith Ross]

Hello Herbert,

Thanks for clarifying what is to be opened. Are any of the file paths in column "C" mapped drives?

[Herbiec09]

Hello Herbert,

Thanks for clarifying what is to be opened. Are any of the file paths in column "C" mapped drives?

Hi Leith,

No, none of these are mapped drives, which is where I guess I got a bit lost, cause because I have always had file paths that were mapped, so wasn't really sure what the syntax for these sort of paths would look like.

Thanks

[Leith Ross]

Hello Herbert,

This is untested but I believe it should do what you want. This will first open the workbook in columns "A:B" as the main workbook. This will remain open until all the associated workbooks in columns "C" have been opened and closed.

Try this macro and let me know the results. It probably will need a little adjusting.

Sub UpdateWorkbookLinks()

    Dim Cell    As Range
    Dim File    As Variant
    Dim MainWkb As Workbook
    Dim Path    As String
    Dim Rng     As Range
    Dim RngEnd  As Range
    Dim SubRng  As Range
    Dim Wkb     As Workbook
    Dim Wks     As Worksheet
    
        Set Wks = Worksheets("Group iAL")
        Set Rng = Wks.Range("B6:E6")
        Set RngEnd = Wks.Cells(Rows.Count, "D").End(xlUp)
        
        If RngEnd.Row < Rng.Row Then Exit Sub
        
        Set Rng = Rng.Resize(RowSize:=RngEnd.Row - Rng.Row + 1)
        
            Set Cell = Rng.Columns(1).Cells.Find("*", , xlValues, xlWhole, xlByRows, xlNext, False, False, False)
            
            If Cell Is Nothing Then Exit Sub
            
                Set FirstCell = Cell
                
                Do
                    Path = Cell.Offset(0, 1).Hyperlinks(1).Address
                    Path = IIf(Right(Path, 1) <> "\", Path & "\", Path)
                    
                    Set MainWkb = Workbooks.Open(Filename:=Path & Cell.Text, UpdateLinks:=True)
                        Set SubRng = Intersect(Cell.CurrentRegion, Rng).Columns(3)
                        
                        For Each File In SubRng.Cells
                            Path = File.Offset(1, 0).Hyperlinks(1).Address
                            Path = IIf(Right(Path, 1) <> "\", Path & "\", Path)
                            
                            Set Wkb = Workbooks.Open(Filename:=Path & File.Text, ReadOnly:=True)
                            Wkb.Close SaveChanges:=False
                        Next File

                    MainWkb.Close SaveChanges:=True
                    
                    Set Cell = Rng.Columns(1).Cells.FindNext(Cell)
                    
                    If Cell Is Nothing Then Exit Do
                    If Cell.Address = FirstCell.Address Then Exit Do
                Loop
            
End Sub