Have you ever found it tedious to name and save files?
You can use “ctrl” + “S” to save an overwrite, “f12” to bring up the Save As screen, and other shortcuts, but it would be more convenient if the file name was also given automatically.
With Excel VBA, the file is automatically named by referencing the characters in the cell.
In this article, we will show you how to create a macro in Excel that saves a file with the cell’s characters as the file name.
How to create a macro in Excel to save a file with the letters of the cell as the file name.
How to create a macro in Excel to save a file with the letters of the cell as the file name.
Below is an example of saving a book with the letter in cell A2 as the file name.
Use the “Develop” tab to create macros.
If you do not have a development tab, please prepare it first as the link below will show you how to get it out.
→How to display the development tab in Excel （in Japanese)
１．Click on the “Development” tab → Click on “Insert” → Click on “Button
２．Click on “Create New
３．The code entry screen opens
４．Enter the code
Enter the code.
It means to save a file on the desktop with cell A2 as the file name.
This is the code in the red frame section.
Dim fileName As String
Dim filePath As String
‘ Get file name
fileName = Range(“A2”).Value
‘ Set file save location to desktop
filePath = Environ(“USERPROFILE”) & “\Desktop\” & fileName & “.xlsx“
‘Save File
ActiveWorkbook.SaveAs fileName:=filePath, FileFormat:=xlOpenXMLWorkbook
※Note: If “.xlsx” in red is changed to “.xlsm”, the file can be saved in a macro-enabled book.
After entering the code, close the code entry screen.
５．A button is created →Type text in cell A2 →Click on the button
The button is ready.
After placing the text in cell A2, click the button.
６．The file is saved
I was able to name and save the file with the letters in cell A2.
Examples of actual usage
I will actually use it for documents.
Let’s say you want to save a file with “slip NO company name” with the invoice shown below.
Use the TEXJOIN function to concatenate the slip NO and company name into cell A2.
(For more information on how to use the TEXTJOIN function, please click on the link below to see how to use it.
→(CONCAT TEXTJOIN function to connect characters from one cell to another in Excel)）（in Japanese）
A consolidated display of slip NO and company name appeared in cell A2.
This can be automatically displayed in cell A2 with no input required.
Next, press the macro button you just created.
A new file could be saved with “slip NO company name”.
This eliminates the need to type the same number, company name, etc., in a cell and then type it again when naming the file…twice!
Summary
How to create a macro to save a file in Excel with the letter of the cell as the file name.
１．Click on the “Development” tab → Click on “Insert” → Click on “Button
２．Click on “Create New
３．The code entry screen opens
４．Enter the code
５．A button is created →Type text in cell A2 →Click on the button
６．The file is saved
This makes it easier because I can save the file name with the text I put in the cell, so I don’t have to type it in the cell and then type it again when I name the file… twice!
To read in Japanese, click here.
→How to create a macro to save a file in Excel with the letter of the cell as the file name.（In japanese）
コメント