[Solved] Saving: Recording a Macro to Save a File As 'X

  • In the excel worksheet, I want to be able to record a macro so that everytime I hit 'control a' it saves the file as 'Cancellation Filing RR ###'. The ### are where I would like it to go to the next number. The file name is not found any where in the worksheet.

    The folder location is y:/Dellserver/Newdell d/Users/rhagos

    Oh by the way, what's the easiest way to learn VBA on my own so I don't have to keep relying on you guys in the forum. Ya'll do an excellent job, but I'd like to do this myself. Thanks

    -Reese Hopkins

  • Hi Reese,

    You have a number of options here for use as your workbook counter:
    1. A cell in the workbook.
    2. A text file.
    3. A name.

    I've opted for the latter and attached an example workbook. For those unable to download workbooks the code is as follows:

    As regards learning VBA, how about trying some of Dave's training? (see this link : http://www.ozgrid.com/Training…icrosoftExcelTraining.htm ) Or you can just try and pick it up for yourself (don't be afraid to make full use of the VBE Help files and also the Macro Recorder - both are great tools and are often overlooked). Feel free to keep asking questions here as your learning develops.


  • Below is modified from code that I use. It checks a directory for the next available number. If file xxx0001 and xxx0002 exist then it will save the existing file as xxx0003. (I haven't checked this mod for bugs). This allows multiple users to create sequenced filenames in the same directory.

    Sub SaveMacro()

    aReg = "Cancellation Filing RR "
    aDir = "y:\whatever\"
    aRegLen = Len(aReg) + 1

    mLargest = 0
    folderspec = aDir

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.Files
    For Each f1 In fc
    s = s & f1.Name
    s = s & vbCrLf
    If Right(f1.Name, 4) = ".xls" Then
    m = Val(Mid(f1.Name, aRegLen, 4))
    If m > mLargest Then
    mLargest = m
    End If
    End If

    mLargest = mLargest + 1
    L = Len(mLargest)
    Select Case L
    Case 1
    aFile = "000" & mLargest & ".xls"
    Case 2
    aFile = "00" & mLargest & ".xls"
    Case 3
    aFile = "0" & mLargest & ".xls"
    Case 4
    aFile = mLargest & ".xls"
    End Select

    aSaveAs = aDir & aReg & aFile

    Msg = "File will be saved as:" & Chr(13) _
    & aSaveAs & Chr(13) & Chr(13) & "If this is incorrect then Cancel"
    Style = vbOKCancel + vbQuestion
    Title = "File to be saved and numbered via automatic sequencing"
    response = MsgBox(Msg, Style, Title)

    If response = vbOK Then
    ActiveWorkbook.SaveAs Filename:=aSaveAs
    End If
    End Sub

  • I'm very new to VBA and I don't know how exactly to take the code you've written and make it work for my excel worksheet. Thanks.


  • Ok Reese, I made some minor modifications to the code above and it does work.

    1) Open the Visual Basic Editor (Tools-Macro-Visual Basic Editor)
    2) Find & click on your VBAProject (your file name)
    3) Copy the sub SaveMacro code to a module (ie Module1) within your project - if you don't see a module then you can create one by clicking on Insert-Module.
    4) change the aReg= and aDir= lines in the SaveMacro code to be your file name prefix and your save-directory location.
    5) If you go back to your spreadsheet you can invoke the macro by clicking on Tools-Macro-Macros-SaveMacro-Run
    (it should work - crosses fingers :))
    6) you can make an "ALT-a" short-cut by clicking on Tools-Macro-Macros-SaveMacro-Options then enter the "A" or "a" - remember -it is case sensitive.
    Good luck. Let me know how it turns out.

  • THANK YOU! amazing... I don't understand exactly how it works, but I'm going to read those tutorials when I have some time. Thanks again for your help.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!