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:

• Creating Workbooks
• Opening Workbooks
• Saving Workbooks
• Saving Workbooks (Save As)
• Closing Workbooks

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:

1. Closing the workbook and waiting for the default response from Excel
2. Closing the workbook without saving
3. Closing the workbook and saving the document
4. 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