Message from a website visitor:
I have a question in Microsoft Excel VBA. I want to be able to save my Excel file based on worksheet cell data. That way I don’t have to go through many clicks, enter the file name and then save my file. For example, I have ‘price’ in cell A1 and ‘data’ in cell B1. I have a folder called inventory on my hard disk C. I wish to save my file as ‘price-data’ on click of a button in folder ‘inventory’. Please reply with a simple code or give me some tips to find a solution. Regards.
We can solve this problem quickly and easily by creating a command button activex control on our Excel worksheet and attaching the following macro to it:
Private Sub CommandButton1_Click()
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Path = “C:\inventory\”
FileName1 = Range(“A1”)
FileName2 = Range(“B1”)
ActiveWorkbook.SaveAs Filename:=Path & FileName1 & “-” & FileName2 & “.xls”, FileFormat:=xlNormal
How does this Excel VBA code work:
First we define three variables Path, FileName1 and FileName2 as Strings because we’ll use these variables to handle text data. We next assign our vraiable Path to the exact path of our directory ‘inventory’. This means we must have a directory ‘inventory’ on our Hard Disk partition C.
Next we assign FileName1 and FileName2 with data from the relevant worksheet cells.
We finally use the ‘SaveAs’ code to save the active workbook i. e. the workbook in which we are working.
As you can see in the training video below the file is saved on click of the command button automatically with the filename ‘price-data’ as desired by the user.