Results 1 to 2 of 2

Thread: Macro for saving file to folder with specified name and date

  1. #1

    Exclamation Macro for saving file to folder with specified name and date


    I am new to using Macros. I would like to have a form with a submit button that will save a file to a specified folder using a specific file name with current date and time in the file name. Is this possible? I have forms that are filled out daily by my employees and would like them automatically saved to a folder on Teams with an automated file name containing the date and time. Any help would be appreciated.


  2. #2
    VBAX Mentor
    Sep 2019
    you add code Close event of the Document (on VBA->ThisDocument, past this code):
    Private Sub Document_Close()
    Dim strFile As String
    ' Change strFile to the Path and document name.
    ' the Extension should be (.docm, macro-enabled document)
    ' on this demo, i am saving it as "new.docm" on user's Document folder
    strFile = Environ$("userprofile") & "\documents\new.docm"
    ' save it
    Me.SaveAs2 saveToFileName(strFile), wdFormatXMLDocumentMacroEnabled
    End Sub
    Private Function saveToFileName(ByVal strFile As String) As String
        Dim file As String
        Dim ext As String
        Dim i As Integer, s As String
        ' get the extension
        i = InStrRev(strFile, ".")
        If i <> 0 Then
            ext = Mid$(strFile, i)
        End If
        ' get only the path+filename (without extension)
        file = Replace$(strFile, ext, "")
        ' new file has date on it
        s = file & Format$(Date, "_dd_mm_yyyy") & ext
        i = 1
        ' loop until we get "New" file
        Do While Len(Dir$(s)) <> 0
            s = file & Format$(Date, "_dd_mm_yyyy") & "(" & i & ")" & ext
            i = i + 1
        saveToFileName = s
    End Function

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts