PDA

View Full Version : VBA To Open & Close Files from a specified Folder



Herbiec09
10-26-2015, 08:17 AM
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
10-26-2015, 09:14 AM
HelloHerbiec09,

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

Herbiec09
10-26-2015, 09:29 AM
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
10-26-2015, 09:36 AM
Hello Herbert,

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

Herbiec09
10-26-2015, 10:10 AM
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
10-26-2015, 03:10 PM
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.



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