Save Excel Files Automatically Using Cell data

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
End Sub

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.


9 thoughts on “Save Excel Files Automatically Using Cell data

  1. Gajanan Ashok Pujari

    Hi,

    I have seen VBA videos, which had very helpfull. as i learnt some thing new. personaly would like to thank you for uploading a such nice video.

    Regards.
    Gajanan

    Reply
  2. Pingback: Automaticcally Hide Workbook View User Form | Excel VBA Training Videos

  3. chander shekhar

    what is the vba code to saveas automatically from cell range if already prepared file has some name as my personal file and it is macroenabled file instread of normal excel.please let me know.thanks in advance

    Reply
  4. Ana Par

    Dr Takyar,
    I came across your site on you tube and I must say that is a very useful tool.
    I am not good in programming VBA but your videos and tutorials help me a lot.
    I have two questions:
    1- is related to this particular tutorial: I am trying to give the saved file a name with dates (starting and end), but the date is formatted as dd/mm/yy. Although I have tried to change it as ddmmmyy, in the final name seems to appear as dd/mm/yy and cannot to be changed. Do you know if there is any way of saving this without having to input the date manually?

    2- the other question, is related to option forms. Is there any way to link a radio button/ option button to a .txt file. I have a form with 3 option buttons and I would like that each option would be linked to a different .txt file.
    Thank you very much for your valuable help.
    My best regards
    Ana

    Reply
  5. Teju

    ActiveWorkbook.SaveAs Filename:=Path & FileName1 & “-” & FileName2 & “.xls”, FileFormat:=xlNormal

    The above code is not work. How can i use this code please any idea ? i have a problem in saving file with cell data.

    Reply
  6. govind

    I WANT THE RESULT AS SIMILAR TO THAT,I WANT TO PASTE ALL SIMILAR NAME SHEETS OF A FOLDER IN A WORKBOOK WHEN I ENTER A NAME IN THAT MESSAGE POP- UP BOX

    Reply

Leave a Reply

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