How to open and close other workbooks from one workbook?

[cdude911]

Hello I’m trying to create a macro that would open 4 other workbooks, close them, and then save the current workbook I’m in. I’m doing this as a way to update the original workbook that has formulas referencing these other workbooks but the values wont update unless those workbooks have been opened.

 

[dmt32]

Hi,
see if this code will do what you want

Sub OpenMyWorkBooks()
    Dim FileNames   As Variant, FileName As Variant
    Dim wb          As Workbook
   
    Const FolderPath As String = "C:\My Documents\My Files\"
   
    FileNames = Array("MyFlile1.xlsx", "MyFlile2.xlsx", "MyFlile3.xlsx", "MyFlile4.xlsx")

   
    Application.ScreenUpdating = False
    For Each FileName In FileNames
       
        Set wb = Workbooks.Open(FolderPath & FileName, 0, False)
       
        'do stuff here
       
        wb.Close False ' < change to true to save any changes
        Set wb = Nothing
    Next FileName
   
    'save workbook changes
    ThisWorkbook.Save
   
End Sub

I have assumed that the files are all in the same Folder?
Change names & folder path where shown in BOLD also in the Open line, Change parameter 0 to 3 if your workbooks have links that need updating.

Dave

 

[Dan_W]

The best (and really, the only) approach is to assign a variable to each of the workbooks, and make sure that you properly reference each of them whenever you do some kind of operation. So for example, you want to copy from Sheet1 in Workbook1 to Sheet 1 in Workbook2. You're going to need to fight the temptation to just 'skip' the full reference (e.g., by using the terms ActiveWorkbook or ActiveSheet.).
You can assign these variables when you're opening the workbooks,even. For example:

Dim WB1 As Workbook, WS1 As WorkSheet
Dim WB2 As Workbook, WS2 As WorkSheet
Dim WB3 As Workbook, WS3 As WorkSheet
Dim MasterWB As Workbook, MasterWS As WorkSheet

Set MasterWB = Application.ThisWorkbook
Set MasterWS = MasterWB.Sheets("Master")

Set WB1 = Application.Workbooks.Open("D:\SalsaDancing.xlsm")
Set WS1 = WB1.Sheets("Lesson Fees")

Set WB2 = Application.Workbooks.Open("D:\TangoDancing.xlsm")
Set WS2 = WB2.Sheets("Student Tuition")

Set WB3 = Application.Workbooks.Open("D:\TapDancing.xlsm")
Set WS3 = WB3.Sheets("Accounts Payable")

WS1.Move MasterWS
WS2.Move MasterWS

WB1.Close True   ' <- Closes the WB and Saves and changes
WB2.Close True   ' <- Closes the WB and Saves and changes
WB3.Close False  ' <- Closes the WB and Saves and [B]does not[/B] save changes

MasterWB.Save

Does that make sense? I hope it helps.

 

[cdude911]

Hi,
see if this code will do what you want

Sub OpenMyWorkBooks()
    Dim FileNames   As Variant, FileName As Variant
    Dim wb          As Workbook
  
    Const FolderPath As String = "C:\My Documents\My Files\"
  
    FileNames = Array("MyFlile1.xlsx", "MyFlile2.xlsx", "MyFlile3.xlsx", "MyFlile4.xlsx")

  
    Application.ScreenUpdating = False
    For Each FileName In FileNames
      
        Set wb = Workbooks.Open(FolderPath & FileName, 0, False)
      
        'do stuff here
      
        wb.Close False ' < change to true to save any changes
        Set wb = Nothing
    Next FileName
  
    'save workbook changes
    ThisWorkbook.Save
  
End Sub

I have assumed that the files are all in the same Folder?
Change names & folder path where shown in BOLD also in the Open line, Change parameter 0 to 3 if your workbooks have links that need updating.

Dave

Thank you for the help. Yes I should have specified that all the workbooks are in different sub folders within a main folder.

 

[cdude911]

The best (and really, the only) approach is to assign a variable to each of the workbooks, and make sure that you properly reference each of them whenever you do some kind of operation. So for example, you want to copy from Sheet1 in Workbook1 to Sheet 1 in Workbook2. You're going to need to fight the temptation to just 'skip' the full reference (e.g., by using the terms ActiveWorkbook or ActiveSheet.).
You can assign these variables when you're opening the workbooks,even. For example:

Dim WB1 As Workbook, WS1 As WorkSheet
Dim WB2 As Workbook, WS2 As WorkSheet
Dim WB3 As Workbook, WS3 As WorkSheet
Dim MasterWB As Workbook, MasterWS As WorkSheet

Set MasterWB = Application.ThisWorkbook
Set MasterWS = MasterWB.Sheets("Master")

Set WB1 = Application.Workbooks.Open("D:\SalsaDancing.xlsm")
Set WS1 = WB1.Sheets("Lesson Fees")

Set WB2 = Application.Workbooks.Open("D:\TangoDancing.xlsm")
Set WS2 = WB2.Sheets("Student Tuition")

Set WB3 = Application.Workbooks.Open("D:\TapDancing.xlsm")
Set WS3 = WB3.Sheets("Accounts Payable")

WS1.Move MasterWS
WS2.Move MasterWS

WB1.Close True   ' <- Closes the WB and Saves and changes
WB2.Close True   ' <- Closes the WB and Saves and changes
WB3.Close False  ' <- Closes the WB and Saves and [B]does not[/B] save changes

MasterWB.Save

Does that make sense? I hope it helps.

Thank you very much. I will try this out when I get to work.

 

[dmt32]

Thank you for the help. Yes I should have specified that all the workbooks are in different sub folders within a main folder.

no worries, you can specify the Main Path in the FolderPath constant & add the sub folders in the filenames array

Const FolderPath As String = "C:\My Documents\Main Folder\"
   
    FileNames = Array("Sub Folder1\MyFlile1.xlsx", "Sub Folder2\MyFlile2.xlsx", "Sub Folder3\MyFlile3.xlsx", "Sub Folder4\MyFlile4.xlsx")

Dave