VBA Excel Working with Multiple Workbooks
If your data is saved on several different files then you are going to have to work with multiple workbooks. You can also download all the codes for this article here.
Jump To:
Contents
Creating Workbooks:
The code below creates a new workbook modifies cell A1 in sheet1:
Sub Example1()
'the workbook object
Dim objWorkbook As Workbook
'creates a new workbook. Adds it to the workbooks collection
'and stores the reference to the workbook in objWorkbook
Set objWorkbook = Workbooks.Add
'Note that the worksheets collection starts at the index "1"
objWorkbook.Worksheets.Item(1).Cells(1, 1) = "Some data"
End Sub
Opening Workbooks:
The code below opens a file dialog asking the user to select a file. If the user selects a file it will open it and modify cell A1 in sheet1. For more information regarding file dialogues you can click here.
Sub Example2()
'the workbook object
Dim objWorkbook As Workbook
Dim objFileDialog As FileDialog
'the full path of the workboo
Dim strPath As String
Set objFileDialog = Application.FileDialog(msoFileDialogOpen)
objFileDialog.Show
If objFileDialog.SelectedItems.Count <> 0 Then
strPath = objFileDialog.SelectedItems.Item(1)
'opens the file at the given address and assigns it to the variable objWorkbook
Set objWorkbook = Workbooks.Open(strPath)
objWorkbook.Worksheets.Item(1).Cells(1, 1) = "Some Data"
End If
End Sub
Saving Workbooks:
The code below asks the user to select a workbook to open. After opening the workbook, it will modify the cell A1 and save the file at its current location. For more information regarding file dialogues you can click here.
Sub Example3()
Dim objWorkbook As Workbook
Dim objFileDialog As FileDialog
Dim strPath As String
Set objFileDialog = Application.FileDialog(msoFileDialogOpen)
objFileDialog.Show
If objFileDialog.SelectedItems.Count <> 0 Then
strPath = objFileDialog.SelectedItems.Item(1)
Set objWorkbook = Workbooks.Open(strPath)
objWorkbook.Worksheets.Item(1).Cells(1, 1) = "Some Data"
'saves the file at the current location
objWorkbook.Save
End If
End Sub
Saving Workbooks (Save As):
The code below asks the user to select a workbook to open. After opening the workbook, it will modify the cell A1. It will then open a save file dialog asking the user for a new path to save the file. For more information regarding file dialogues you can click here.
Sub Example4()
Dim objWorkbook As Workbook
'the open file dialog object
Dim objOpenFileDialog As FileDialog
'the path to find the workbook
Dim strPathOpen As String
'the save file dialog object
Dim objSaveFileDialog As FileDialog
'the path to save the workbook
Dim strPathSave As String
Set objOpenFileDialog = Application.FileDialog(msoFileDialogOpen)
objOpenFileDialog.Show
If objOpenFileDialog.SelectedItems.Count <> 0 Then
strPathOpen = objOpenFileDialog.SelectedItems.Item(1)
Set objWorkbook = Workbooks.Open(strPathOpen)
objWorkbook.Worksheets.Item(1).Cells(1, 1) = "Some Data"
'creates a dialog for choosing the save path
Set objSaveFileDialog = Application.FileDialog(msoFileDialogSaveAs)
objSaveFileDialog.Show
If objSaveFileDialog.SelectedItems.Count <> 0 Then
strPathSave = objSaveFileDialog.SelectedItems.Item(1)
objWorkbook.SaveAs (strPathSave)
End If
End If
End Sub
Closing Workbooks:
When you are closing a workbook, you have several different options:
- Closing the workbook and waiting for the default response from Excel
- Closing the workbook without saving
- Closing the workbook and saving the document
- Closing the workbook and saving the document at a predefined location.
The 4 cases are illustrated below:
Sub Example5()
'the workbook object
Dim objWorkbook As Workbook
Set objWorkbook = Workbooks.Add
objWorkbook.Worksheets.Item(1).Cells(1, 1) = "Some data"
'a dialog will appear asking what to do
Call objWorkbook.Close
Set objWorkbook = Workbooks.Add
objWorkbook.Worksheets.Item(1).Cells(1, 1) = "Some data"
'changes will be discarded
Call objWorkbook.Close(False)
Set objWorkbook = Workbooks.Add
objWorkbook.Worksheets.Item(1).Cells(1, 1) = "Some data"
'A window will appear to select the save location
Call objWorkbook.Close(True)
Set objWorkbook = Workbooks.Add
objWorkbook.Worksheets.Item(1).Cells(1, 1) = "Some data"
'Will automaticaly close and save the workbook at "C:" under the filename "Test"
Call objWorkbook.Close(True, "C:Test")
End Sub
One thought on “VBA Excel Working with Multiple Workbooks”