Thisworkbook saveas vba excel

VBA Save File – 20 Easy Examples

In this Article

This VBA Tutorial covers how to save a file using the Save and Save As commands in VBA.

Save Workbook – VBA

The VBA Save command saves an Excel file similarly to clicking the Save icon or using the Save Shortcut (CTRL + S).

Save a Specified Workbook

To save a workbook, reference the workbook object and use the Save command.

Save the Active Workbook

Note: This is the current active workbook from with in the VBA code, which is different from ThisWorkbook which contains the running code.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Save the Workbook Where the Code is Stored

Save all Open Workbooks

This will loop through all open workbooks, saving each one.

Save all open workbooks that were not opened ReadOnly

Note: opening a workbook in ReadOnly mode prevents the file from being saved.
To save the file you will need to use Save As and save the file with a different name.

Save a workbook defined by a variable

This will save a workbook that was assigned to a workbook object variable.

Save a workbook defined by a string variable

This will save a workbook that’s name was saved to a string variable.

Save a workbook defined by the order it was opened.

Note: The first workbook opened would have 1, the second 2, etc.

Save a workbook based on a cell value

This will save a workbook that’s name is found in a cell value.

Save As – VBA

The VBA Save As command saves an Excel file as a new file, similar to clicking the Save As icon or using the Save As Shortcut (Alt > F > A).
Above, we identified all the ways to specify which workbook to save. You can use those exact same methods to identify workbooks when using Save As.

Save As behaves similarly to Save, except you also need to specify the name of the new file.
In fact, Save As has many potential variables to define:

SaveAs Syntax:

A full description of all of the SaveAs arguments is included below. For now we will focus on the most common examples.
Note: These arguments can be entered as string with parenthesis or as defined variables.

Источник

How to: Programmatically save workbooks

Applies to: Visual Studio Visual Studio for Mac Visual Studio Code

There are several ways to save a workbook. You can save a workbook without changing the path. If the workbook has not been saved before, you should save the workbook by specifying a path. Without an explicit path, Microsoft Office Excel saves the file in the current folder with the name it was given when it was created. You can also save a copy of the workbook without modifying the open workbook in memory.

Applies to: The information in this topic applies to document-level projects and VSTO Add-in projects for Excel. For more information, see Features available by Office application and project type.

Save a workbook without changing the path

To save a workbook associated with a document-level customization

Call the Save method of the ThisWorkbook class.

To save the active workbook in a VSTO Add-in

Call the Save method to save the active workbook. To use the following code example, run it in the ThisAddIn class in a VSTO Add-in project for Excel.

Save a workbook with a new path

You can save the specified workbook to a new location or with a new name, optionally specifying a file format, a password, an access mode, and more.

You might want to set the DisplayAlerts property to False before saving the workbook with a new path because saving in some formats requires interaction. Setting this property to False causes Excel to use all defaults.

To save a workbook associated with a document-level customization

Call the SaveAs method of the ThisWorkbook class. To use the following code example, run it in the ThisWorkbook class.

To save the active workbook in a VSTO Add-in

Call the SaveAs method to save the active workbook to a new path. To use the following code example, run it in the ThisAddIn class in a VSTO Add-in project for Excel.

Save a copy of the workbook

You can save a copy of the workbook to a file without modifying the open workbook in memory. This is useful when you want to create a backup copy without modifying the location of the workbook.

To save a workbook associated with a document-level customization

Call the SaveCopyAs method of the ThisWorkbook class. To use the following code example, run it in the ThisWorkbook class.

To save the active workbook in a VSTO Add-in

Call the SaveCopyAs method to save a copy of the active workbook. To use the following code example, run it in the ThisAddIn class in a VSTO Add-in project for Excel.

To verify that the save was successful

You can use some of the DocumentProperties like the Last Save Time to verify that the save was successful.

Robust programming

Interactively canceling any of the methods that save or copy the workbook raises a run-time error in your code. For example, if your procedure calls the SaveAs method but does not disable prompts from Excel, and your user clicks Cancel when prompted, Excel raises a run-time error.

Источник

Workbook.SaveAs method (Excel)

Saves changes to the workbook in a different file.

Interested in developing solutions that extend the Office experience across multiple platforms? Check out the new Office Add-ins model. Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.

Syntax

expression.SaveAs (FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

expression A variable that represents a Workbook object.

Parameters

Name	Required/Optional	Data type	Description
FileName	Optional	Variant	A string that indicates the name of the file to be saved. You can include a full path; if you don’t, Microsoft Excel saves the file in the current folder.
FileFormat	Optional	Variant	The file format to use when you save the file. For a list of valid choices, see the XlFileFormat enumeration. For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.
Password	Optional	Variant	A case-sensitive string (no more than 15 characters) that indicates the protection password to be given to the file.
WriteResPassword	Optional	Variant	A string that indicates the write-reservation password for this file. If a file is saved with the password and the password isn’t supplied when the file is opened, the file is opened as read-only.
ReadOnlyRecommended	Optional	Variant	True to display a message when the file is opened, recommending that the file be opened as read-only.
CreateBackup	Optional	Variant	True to create a backup file.
AccessMode	Optional	XlSaveAsAccessMode	The access mode for the workbook.
ConflictResolution	Optional	XlSaveConflictResolution	An XlSaveConflictResolution value that determines how the method resolves a conflict while saving the workbook. If set to xlUserResolution, the conflict-resolution dialog box is displayed.

If set to xlLocalSessionChanges, the local user’s changes are automatically accepted.

If set to xlOtherSessionChanges, the changes from other sessions are automatically accepted instead of the local user’s changes.

If this argument is omitted, the conflict-resolution dialog box is displayed. AddToMru Optional Variant True to add this workbook to the list of recently used files. The default value is False. TextCodepage Optional Variant Ignored for all languages in Microsoft Excel.

NOTE: When Excel saves a workbook to one of the CSV or text formats, which are specified by using the FileFormat parameter, it uses the code page that corresponds to the language for the system locale in use on the current computer. This system setting is available in the Control Panel > Region and Language > Location tab under Current location. TextVisualLayout Optional Variant Ignored for all languages in Microsoft Excel.

NOTE: When Excel saves a workbook to one of the CSV or text formats, which are specified by using the FileFormat parameter, it saves these formats in logical layout. If left-to-right (LTR) text is embedded within right-to-left (RTL) text in the file, or vice versa, logical layout saves the contents of the file in the correct reading order for all languages in the file without regard to direction. When an application opens the file, each run of LTR or RTL characters are rendered in the correct direction according to the character value ranges within the code page (unless an application that is designed to display the exact memory layout of the file, such as a debugger or editor, is used to open the file). Local Optional Variant True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).

Remarks

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don’t mix these elements.

• Strong password: Y6dh!et5
• Weak password: House27

Use a strong password that you can remember so that you don’t have to write it down.

Example

This example creates a new workbook, prompts the user for a file name, and then saves the workbook.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Источник

Объект Workbook (Excel)

Представляет книгу Microsoft Excel.

Замечания

Объект Workbook является членом коллекции Workbooks . Коллекция Книги содержит все объекты Workbook , открытые в настоящее время в Microsoft Excel.

Свойство ThisWorkbook объекта Application возвращает книгу, в которой выполняется код Visual Basic. В большинстве случаев это то же самое, что и активная книга. Однако если код Visual Basic является частью надстройки, свойство ThisWorkbook не вернет активную книгу. В этом случае активной книгой является книга, вызывающая надстройку, тогда как свойство ThisWorkbook возвращает книгу надстройки.

Если вы создаете надстройку на основе кода Visual Basic, следует использовать свойство ThisWorkbook для определения инструкции, которая должна выполняться в книге, которую вы компилируете в надстройку.

Пример

Используйте workbooks (index), где index — это имя книги или номер индекса, чтобы вернуть один объект Workbook . В следующем примере активируется одна книга.

Номер индекса обозначает порядок открытия или создания книг. Workbooks(1) — первая созданная книга, а Workbooks(Workbooks.Count) — последняя созданная. Активация книги не изменяет ее номер индекса. Все книги включаются в число индексов, даже если они скрыты.

Свойство Name возвращает имя книги. Нельзя задать имя с помощью этого свойства; Если необходимо изменить имя, используйте метод SaveAs , чтобы сохранить книгу под другим именем.

В следующем примере выполняется активация Sheet1 в книге с именем Cogs.xls (книга уже должна быть открыта в Microsoft Excel).

Свойство ActiveWorkbook объекта Application возвращает книгу, которая сейчас активна. В следующем примере задается имя автора для активной книги.

В этом примере вкладка листа из активной книги отправляется по электронной почте, используя указанный адрес электронной почты и тему. Для выполнения этого кода активный лист должен содержать адрес электронной почты в ячейке A1, тему в ячейке B1 и имя листа, отправляемого в ячейку C1.

События

Методы

Свойства

См. также

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Источник