How To Save Excel Workbook Manually, Automatically, Or Use VBA Script

This article will tell you how to save an Excel workbook manually, automatically, and use a VBA script. It will also tell you how to change the default Excel workbook save location.

1. How To Save Excel Workbook Manually.

  1. Open the Excel workbook, and click the Save button on the top left corner of Excel to pop up the Save this file dialog.
  2. You can also press Ctrl + S to open the Save this file dialog.
  3. Click the link More options… in the above dialog to go to the Save As window.
  4. You can also go to the Save As window by clicking the File —> Save As menu item.
  5. Then you can select where to save the Excel workbook, you can save it to Microsoft OneDrive, This PC( the local computer), Add a Place( other places), Browse ( browse a network place to save the Excel workbook).

2. How To Change Excel Workbook Default Save Directory.

  1. Click File —> Options to open the Excel Options dialog.
  2. Click the Save option item on the left.
  3. Check the checkbox Save to Computer by default.
  4. Input the default save location directory in the Default local file location input text box behind the above checkbox.
  5. Then when you save an Excel workbook, it will be saved to the above default directory.

3. How To Save Excel Workbook In Different File Format.

  1. Click the File —> Save As menu item in Excel.
  2. Double-click any location item (  such as This PC ) in the middle location list to open the Save As popup dialog.
  3. Click the down arrow at the end of the Save as type drop-down list.
  4. It will show all the file formats that can be saved with the different file extensions.
  5. Choose one file format that meets your needs.
  6. Input a file name and click the Save button to save the Excel workbook in that format.

4. How To Save Excel Workbook Automatically.

  1. If you want to save your Excel workbook automatically, you can follow the below steps.
  2. Click File —> Options menu item to open the Excel Options pop-up dialog.
  3. Click the Save option item on the dialog left panel.
  4. Then check the Save AutoRecover information every checkbox.
  5. You can set the time interval to a smaller number value, the time interval unit is one minute.
  6. You should also check the checkbox Keep the last AutoRecovered version if I close without saving.
  7. You can change the AutoRecover file saved location in the AutoRecover file location input text box.
  8. Now your Excel workbook will be saved to the AutoRecover file location periodically.
  9. If you use Office365 ( Microsoft Office online version ), there is a real-time saving feature.
  10. To enable it, you should check the checkbox AutoSave OneDrive and SharePoint Online files by default on Excel in the Excel Options dialog Save option panel.

5. How To Save Excel Workbook Using VBA Programmatically.

  1. You can also use VBA to save one Excel workbook to another Excel workbook.
  2. You can use the Excel workbook’s SaveAs function to do it. Below is the example source code.
    ' This function will save the current workbook as another Excel file.
    Public Sub SaveExcelFileUseVBA()
        ' Get the current Excel workbook file name.
        workbookNname = ThisWorkbook.Name
        ' Get current date time stamp.
        saveTimeStamp = Format(Now, "ddmmmyyy-hhmmss")
        ' Invoke the current workbook's SaveAs function to copy the current Excel workbook to a new workbook.
        ' The new workbook name is time-stamp & current-workbook-name
        ThisWorkbook.SaveAs saveTimeStamp & workbookName
    End Sub

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.