To save a .xml spreadsheet in a distinct folder of a local drive


To be very precise of my concern that the workbook developed by me can incorporate 7 payment particulars comprising of 8 columns and 7 rows. In a day around 35-40 entries/rows are required to send to our banker. So each time a set of 7 is ready (A4 size paper), it is required to save in a distinct manner to recover the information later when/if required.

To ease this process we include (i)/(ii)/(iii) as required after the file name say RTGS BANK TEMPLATE - (?) 

I am including underneath what I have tried.

    *Sub SaveAsDialog()

    On Error Resume Next

    With Application.FileDialog(msoFileDialogSaveAs)

     .    Title = "Please choose a location and file name to save"

     .    ButtonName = "SAVE AS"

     .    InitialFileName = "E\Backup\SWARNALI\RTGS TEMPLATE(PARTY)\" & Range("").Value

        If .Show = 0 Then

        MsgBox "The file did not save.", vbCritical

        Exit Sub

    End If

        Application.DisplayAlerts = False

    .   Execute

        Application.DisplayAlerts = True

    End With

    End Sub*




I'm not sure I fully understand what you want to do but please try the code below anyway. It's necessary that the referenced Range("A1") should hold a valid file name, including the extension.

Sub SaveACopy()
    ' 052

    Dim Fn      As String           ' file name

    Fn = "E\Backup\SWARNALI\RTGS TEMPLATE(PARTY)\" & Range("A1").Value
'    Debug.Print Fn
    ActiveWorkbook.SaveCopyAs Fn
End Sub

This code will save a copy of the ActiveWorkbook in a folder specified in the code, by a name specified in cell A1 of the ActiveSheet. The action isn't the same as you seemed to attempt with your own code because the above will create an independent copy, a snapshot as it were. You can continue working with the original workbook and make more changes and then save the newly changed workbook again under another name.

Of course, the file name must be correct. Therefore I left the line of code to print it to the Immediate pane in the procedure. You can reactivate it when you want to check the name.

BTW, on this site code tags are

. You can add them manually or click the "CODE" button in the menu bar above the posting window to insert a pair. If you write or paste the code first, you can select the code and then press the button to insert tags around your selection.

Answer the Question

You must create an account to use the forum. Create an Account or Login