VBA To Open & Close Files from a specified Folder

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

HelloHerbiec09,

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

Quote:

---

Originally Posted by Leith Ross

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

Hello Herbert,

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

Quote:

---

Originally Posted by Leith Ross

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

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:D" have been opened and closed.

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

Code:

---

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

---