Drawing Button With VBA To Rename File And Save

data808 · #1 10-14-2022, 01:17 AM

Is it possible to make a button that I draw with the mouse and assign it to a macro that can name the file based on a content control plain text box with the current date and time, also in the file name, then save it to a specific folder location? Could someone help me with the VBA to get this done? The plain text box will have a name so the file name would look something like this for example:

Smith,John_10-13-2022_102036.docm

The content control plain text box will have "John Smith" then date will pull from the computer and "102036" is the current time the file is saved which represents 10:20:36PM since I don't think colons are accepted in a file name.

I have been able to do this with Excel but I am not familiar with Word as I am very much a beginner in Word.

Thanks for the help.

Guessed · #2 10-16-2022, 08:04 PM

Quote:

"102036" is the current time the file is saved which represents 10:20:36PM

This is not good practice. If you ignore the am/pm part then there are two possible times a day. You should either use the 24hr clock or include am/pm info.

This code should get you most of the way there.

Code:

Sub SaveMeYeezy()
  Dim sName As String, aCC As ContentControl, iSpacePos As Integer
  Dim sFilename As String, sNow As String, sPath As String
  
  sPath = "C:\Users\username\Desktop\"
  Set aCC = ActiveDocument.ContentControls(1)
  sName = Trim(aCC.Range.Text)
  iSpacePos = InStr(sName, " ")
  If iSpacePos > 0 Then
    sName = Mid(sName, iSpacePos) & "," & Left(sName, iSpacePos)
    sName = Replace(sName, " ", "")
  End If
  sNow = Format(Now, "dd-mm-yy_HHMMSS")
  sFilename = sName & "_" & sNow & ".docm"
  
  ActiveDocument.SaveAs2 FileName:=sPath & sFilename, FileFormat:=wdFormatXMLDocumentMacroEnabled
End Sub

data808 · #3 10-20-2022, 10:09 PM

Thanks for the help. I tried your suggestion with the code you provided and I just adjusted some of it to fit my situation. So far nothing is happening. I recorded a macro with a button and forgive me as I am still very new to using Word macros but it now seems that the button follows every single Word document since it appears in the Quick Access part of the Ribbon. So with that being, is there a way to only keep this button specific to the document that I created it for? I don't want users accidentally clicking that button for other documents that do not need that feature.

Here is the code you provided with my adjustments:

Sub SaveAs()
'
' SaveAs Macro
'
'

Dim sName As String, aCC As ContentControl, iSpacePos As Integer
Dim sFilename As String, sNow As String, sPath As String

sPath = "C:\Users\data8\Desktop\TEST FOLDER FOR WORD SAVE AS MACRO"
Set aCC = ActiveDocument.ContentControls("Subject")(1)
sName = Trim(aCC.Range.Text)
iSpacePos = InStr(sName, " ")
If iSpacePos > 0 Then
sName = Mid(sName, iSpacePos) & "," & Left(sName, iSpacePos)
sName = Replace(sName, " ", "")
End If
sNow = Format(Now, "dd-mm-yy_HHMMSS")
sFilename = sName & "_" & sNow & ".docm"

ActiveDocument.SaveAs2 FileName:=sPath & sFilename, FileFormat:=wdFormatXMLDocumentMacroEnabled

End Sub

I'm not sure if I did everything correctly but I used the title "Subject" for the content control that I want the file name to be created with. I of course adjusted the path to that test folder. Those were the only obvious things to me that needed to be filled in. So after I did that, I click the button and nothing happens. Can you help me with this?

Thank you for your time.

gmayor · #4 10-20-2022, 11:58 PM

For the macro to apply only to the document, move the macro to the document and redirect the button to its new location. It would probably be better to save the document with macro as a template and create new documents from the template to be saved as docx.

Sticking with what you propose I suggest the following. Note that if TEST FOLDER FOR WORD SAVE AS MACRO was actually part of the path, then that would have to exist or be created for it to work. (See the create folders function on my web site at Useful VBA Functions) I have removed it and the macro will save to the user's desktop. I have also added rudimnentary error handling.

Code:

Option Explicit

Sub SaveAs()
'
' SaveAs Macro
'
'

Dim sName As String, aCC As ContentControl, iSpacePos As Integer
Dim sFilename As String, sNow As String, sPath As String

    sPath = Environ("USERPROFILE") & Chr(92) & "Desktop\"
    For Each aCC In ActiveDocument.ContentControls
        If aCC.Title = "Subject" Then
            If aCC.ShowingPlaceholderText = True Then
                MsgBox "Complete the 'Subject' field!", vbCritical
                aCC.Range.Select
                Exit Sub
            End If
            sName = Trim(aCC.Range.Text)
        End If
        iSpacePos = InStr(sName, " ")
        If iSpacePos > 0 Then
            sName = Mid(sName, iSpacePos) & "," & Left(sName, iSpacePos)
            sName = Replace(sName, " ", "")
        End If
        sNow = Format(Now, "dd-mm-yy_HHMMSS")
        sFilename = sName & "_" & sNow & ".docm"

        ActiveDocument.SaveAs2 FileName:=sPath & sFilename, FileFormat:=wdFormatXMLDocumentMacroEnabled
    Next aCC
    Set aCC = Nothing
End Sub

data808 · #5 10-22-2022, 12:34 PM

Forgive me as this may be a slow process as I only have experience with Excel when it comes to VBA and Macros. I am still learning how Word functions. It's shocking how different the UI and options are when comparing Word and Excel. I originally figured it would be a walk in the park if I am familiar with Excel but so far it's not the case. It seems very different.

Ok so I'm reading your instructions and I want to focus on the first part of your suggestion which talks about applying a macro for only one document. I assume you mean the VBA code to be moved to "This Document" on the left side in the VBA code window correct?

Then how exactly do I move the button to it's new location? Is that simply dragging the macro button from quick access area above the ribbon to somewhere on the document itself where the user will be typing? Like, just find an open blank spot on the document and drag the button there so the user can just click on it when finished filling out the form?

I also have never created a template file in Word but I do see something about templates in the Ribbon at the top under the Developer tab. I have worked on a lot of things on this .docm document and if possible, would not want to start over again. Would it be hard to convert it to a template document if that is what turns out to be the best solution? I do like the idea of saving the document with a macro as a .docx file because after they fill out the form, there is no need for the document to be macro enabled any longer. At least I don't think so at the moment.

Thanks for your patience and help on this. I really appreciate it.

gmayor · #6 10-22-2022, 09:18 PM

No that's not what I meant. See Installing Macros

With the document open, the code should be in a new module of the document project. If the button is on the QAT (Quick Access Toolbar) then you will have to delete that button and create a new one.

To create a template simply save the document as a macro enabled template. This will not overwrite your document, but create a new template from that document.

data808 · #7 10-26-2022, 12:56 AM

Ok I moved the VBA code from Normal / Modules to Project / Modules so I think now the VBA is switched to only this document. I then removed the button from the QAT and created a new one and set it for just this document that I am working on and it now seems to show up for only this document.

Now I'm going to start testing the code and get back to you on what results I get. Thanks for your help.

data808 · #8 10-26-2022, 01:23 AM

Ok I just tested it and nothing happens. Not even your line:

If aCC.ShowingPlaceholderText = True Then
MsgBox "Complete the 'Subject' field!", vbCritical

I assume this is a prompt for the user to fill in the subject field if it's blank? I tested it by leaving the field blank and clicking the macro button in the QAT and nothing happens. No prompt or saving of the file to the desktop. Do I need to change the sPath? It looked like you may have set it to a universal username/desktop so I just left the line of code as is. In fact, I left your entire code as is. So let me know if I was supposed to change anything.

If not, one other thing I can think of that may be causing this problem is that the Subject title was not entered by me typing it into the properties but rather me doing an Insert / Quick Parts / Document Properties / Subject selection from a drop down list because I had another text box later in the document that I wanted to repeat whatever I typed in that first subject field. So maybe because of that, the code you provided doesn't register that text box as an official "Subject" titled text box?

gmayor · #9 10-26-2022, 04:30 AM

The snippet of code tests to establish if the field is showing its placeholder text (Click or tap here to enter text), which if empty is what the control should display. However there must actually be a content control in the document with the title "Subject", or nothing happens.

You can test for that:

Code:

Sub SaveAs()
'
' SaveAs Macro
'
'

Dim sName As String, aCC As ContentControl, iSpacePos As Integer
Dim sFilename As String, sNow As String, sPath As String
Dim i As Integer
    sPath = Environ("USERPROFILE") & Chr(92) & "Desktop\"
    For Each aCC In ActiveDocument.ContentControls
        If aCC.Title = "Subject" Then
            i = i + 1
            Exit For
        End If
    Next aCC
    If i = 0 Then
        MsgBox "The 'Subject' Content Control is missing", vbCritical
        Exit Sub
    End If
    For Each aCC In ActiveDocument.ContentControls
        If aCC.Title = "Subject" Then
            If aCC.ShowingPlaceholderText = True Then
                MsgBox "Complete the 'Subject' field!", vbCritical
                aCC.Range.Select
                Exit Sub
            End If
            sName = Trim(aCC.Range.Text)
        End If
        iSpacePos = InStr(sName, " ")
        If iSpacePos > 0 Then
            sName = Mid(sName, iSpacePos) & "," & Left(sName, iSpacePos)
            sName = Replace(sName, " ", "")
        End If
        sNow = Format(Now, "dd-mm-yy_HHMMSS")
        sFilename = sName & "_" & sNow & ".docm"

        ActiveDocument.SaveAs2 FileName:=sPath & sFilename, FileFormat:=wdFormatXMLDocumentMacroEnabled
    Next aCC
    Set aCC = Nothing
End Sub

The path as written is fine. It will save to the user's desktop.

You can repeat the content of a content control by inserting a second content control and map it to the first. Both operations are easily achieved using my content control tools - Insert Content Control Add-In

data808 · #10 10-27-2022, 02:31 AM

Ok so I tested this code and I got it to do something. Right now it's saving multiple files. First time I ran it, it saved 3 files. The second time I ran it, it nows saves 5 files. 4 our of 5 files have the person's last name then a comma and then first name, then a bunch of numbers. Now that I'm looking at the pattern it looks like the date is our of sequence with this format dd-mm-yy and then the rest of the numbers represents the time which looks to be in the correct format. Let me try and tweak your code a bit and see if I can figure out the date format. Also the last file that does not contain a name is just the date and time. The date and time is in the same format pattern as the rest.

Would you know why this code is saving multiple files?

gmayor · #11 10-27-2022, 04:33 AM

Sorry. My fault.

Change the end of the macro as follows:

Code:

        sFilename = sName & "_" & sNow & ".docm"
    Next aCC
    ActiveDocument.SaveAs2 FileName:=sPath & sFilename, FileFormat:=wdFormatXMLDocumentMacroEnabled
    Set aCC = Nothing
End Sub

data808 · #12 10-27-2022, 05:35 PM

Your code is working great! Thank you very much.

I just realized that sometimes the subject will be a company name and so the format we have with the comma won't work out too well. I'm going to make another copy of this file to use for companies only but instead could you adjust the VBA code so that it keeps the subject field in tact without a comma and reversing the first and last name? Also keeping the company name without spaces between each word? Example:

If user types "XYZ Construction LLC" into the subject field the file name would be saved as:

XYZConstructionLLC_10-27-2022_143225.docx (I was able to figure out how to change the file format from .docm to .docx)

Thanks for the help.

gmayor · #13 10-27-2022, 09:10 PM

That would be simpler

Code:

Option Explicit

Sub SaveAs()
'
' SaveAs Macro
'
'

Dim sName As String, aCC As ContentControl
Dim sFilename As String, sNow As String, sPath As String
Dim i As Integer
    sPath = Environ("USERPROFILE") & Chr(92) & "Desktop\"
    For Each aCC In ActiveDocument.ContentControls
        If aCC.Title = "Subject" Then
            i = i + 1
            Exit For
        End If
    Next aCC
    If i = 0 Then
        MsgBox "The 'Subject' Content Control is missing", vbCritical
        Exit Sub
    End If
    For Each aCC In ActiveDocument.ContentControls
        If aCC.Title = "Subject" Then
            If aCC.ShowingPlaceholderText = True Then
                MsgBox "Complete the 'Subject' field!", vbCritical
                aCC.Range.Select
                Exit Sub
            End If
            sName = Trim(aCC.Range.Text)
            sName = Replace(sName, Chr(32), "")
        End If
        sNow = Format(Now, "dd-mm-yy_HHMMSS")
        sFilename = sName & "_" & sNow & ".docx"
        ActiveDocument.SaveAs2 FileName:=sPath & sFilename, FileFormat:=wdFormatXMLDocument
    Next aCC
    Set aCC = Nothing
End Sub

data808 · #14 10-27-2022, 10:24 PM

Thanks. Had to change your "Next aCC" line to above the ActiveDocument.SaveAs2 line and then it worked. It was saving multiple files or if I didn't fill in the subject then it would have the prompt pop up but it would also save the file without a name of the company in the file name since subject wasn't filled out.

Thank you very much for your help with this. You just made life a lot easier for my coworkers. Take care.

data808 · #15 10-28-2022, 12:41 PM

Sorry have one more question. I wanted to change the sPath from the desktop to our network G

rive in one of the folders. When I replaced the entire sPath to this:

sPath = "G:\WP61"

The file saved only as far as the root of the G

rive and also it did something weird to the file name. It put the G

rive folder name that I wanted the file to be saved in, in front of the person's name. I did a test with the name John Doe and this is the file name it generated:

WP61Doe,John_10-28-2022_093118.docx

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to save Word file by field name as filename using VBA button?	TribeBuckeyeFan	Word VBA	4	02-02-2017 05:58 AM
How to save Word file by field name as filename using VBA button?	chemtoli	Word VBA	5	01-25-2017 08:06 AM
Save and rename attachments from ZIP FILE	AndyDDUK	Outlook	1	03-03-2016 12:32 AM
Have to rename file every time to save workbook	intelli	Excel	3	03-27-2014 11:53 PM
Rename Document & Save	d4okeefe	Word VBA	4	05-23-2013 09:35 AM