Dynamic File Save as (EXCEL 97, Win 98)

Topic

New Reply

I would like to code in vba to automatically file save the workbook using the name in a certain cell. Say if cell, A1 has the value of CD-0001, that is what the filename I would like to be saved. I also want to use the same directory in which the current workbook was opened from. A1 is formula driven and will change each time the workbook is opened. I am very new to VBA and have used the macro recorder to record my actions doing the above, but now can not figure out how to make it “dynamic” to always use the name in cell A1 and use the same directory which stored the original workbook when opened. My intention is to only use the macro for the first time to save the workbook, but what happens when I run the macro and it has already been saved? Is there a way to trap for this or have the file save without prompting the usual over write message input box? I am so new to this process, is there something else I should consider in this macro? It seemed like this was going to be fairly straight forward when I started this process this morning. THANKS.

Reply | Quote

Replies

There are two additional questions that you need to answer before this can be completed. What do you want the macro to do if this is a new workbook that has never been saved, and therefore there is no path to the directory. What do you want the macro to do if cell A1 is empty, and therefore there is no file name?

The following macro needs to have those filled in:

Public Sub WBSave()
    If Worksheets("Sheet1").Range("A1").Value = "" Then
'       Cell A1 is empty, what do you want to do now?
        Exit Sub
    End If
    If ActiveWorkbook.Path = "" Then
'       The workbook has never been saved, what do you want to do here?
        Exit Sub
    End If
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "" & Worksheets("Sheet1").Range("A1").Value
    Application.DisplayAlerts = True
End Sub

Reply | Quote

Since the initial workbook opened was my “master” work book, I want to use the same path to save the “dynamic” filename that was used to open the “master” workbook. For example, if the “master” workbook is contained in ” c:mydocumentsexcel ” , I would want to save the excel file to the same directory with the “dynamic” name. I didn’t want to “overwrite” the “master” excel workbook. The other question is more difficult as I obviously made the assumption that I or the person working with me will NEVER erase the formula is cell A1—thus having it blank when the “filesave” macro is executed. I guess I could “protect” the cell and I’m really struggling with other alternatives. One alternative my be is if the cell is blank, the macro could stop execution and display a text box. To be honest, if the formula in A1 is inadvertently erased, the current excel file should just be close without saving and the “master” workbook re-open as I’m using a counter in cell A1. Thanks for your patience with a newbie like me–its appreciated.

Reply | Quote

Although, as Legare notes elsewhere, Excel is not highly secure, you could place data called only by the code, such as your proposed workbook name and counter, in unprotected cells in a password-locked hidden worksheet in the master WB. That way any overwriting of data you don’t wish modified has to be done very deliberately.

Reply | Quote