Solved Command Button on Word Doc to Save As (1 Viewer)

[Weekleyba]

I'm trying to do the following:
From Access create a Word document and save it as a .docx.

What I have so far:
From Access I have a command button to create a Word Document based off a query.

Private Sub Command47_Click()

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Q_PlanningAgreementforMakeTable", acViewNormal, acEdit
    DoCmd.SetWarnings True
    ' This opens the Word file.  The 1 at the end makes sure it is visible.
    Shell "winword ""C:\Users\brweekley\Documents\Databases\Project Database SFC\PA_Template.docm""", 1
    
End Sub

Then once the Word template opens you click "Yes" to allow the command to run.

The Word doc opens with the title of the template. "PA_Template.docm"
I want a button on the Word doc to Save As to a .docx and not save the command button.
How do it to that?

The code in my MS Word command button is this:

Private Sub CommandButton1_Click()

'Application.FileDialog(msoFileDialogSaveAs).Show

    With Application.FileDialog(msoFileDialogSaveAs)
        .FilterIndex = 2 '.docm file format
        .Show
        
        If .SelectedItems.Count <> 0 Then
            ThisDocument.SaveAs2 .SelectedItems(1), wdFormatXMLDocumentMacroEnabled
        End If
    End With

End Sub

 

[The_Doc_Man]

It is generally the case that you cannot DISABLE or REMOVE a command button when you are still servicing an event for it. You COULD try to make the button invisible (.Visible = FALSE) without disabling it (after the FileDialog selection has been made) and THEN execute your save action. That way, the command button would technically be saved in the document but you would not see it. I'm not clear on whether it would remain active in such a context. It is possible that it would. You might do better to define a key-based macro that involves something like ALT+CTRL+SHIFT+ some letter. That way, there is no clickable hot spot. Granted, with all of the hot-keys already in place for Word, you might have to look for an unused combination involving ALT and/or CTRL as part of the deal.

 

[Weekleyba]

Neither will work for me.
I was thinking maybe delete the command button, run a Save As code, then recreate the command button.
Is that possible?

As a beginner VBA programmer, I've managed to piece this together from researching it online.
I think I'm close but it does not work.

Can you help?

Private Sub CommandButton1_Click()

    CommandButton1.Select
    Selection.Delete
    
    With Application.FileDialog(msoFileDialogSaveAs)
        .FilterIndex = 1
        .Show
        
        If .SelectedItems.Count <> 0 Then
            ThisDocument.SaveAs2 .SelectedItems(1), wdFormatXMLDocument
        End If
    End With   
    
'Add a command button to a new document
Dim shp As Word.InlineShape
Set shp = ActiveDocument.Content.InlineShapes.AddOLEControl(ClassType:="Forms.CommandButton.1")
shp.OLEFormat.Object.Caption = "Click Here"

'Add a procedure for the click event of the inlineshape
'**Note: The click event resides in the This Document module
Dim sCode As String
sCode = "Private Sub " & shp.OLEFormat.Object.Name & "_Click()" & vbCrLf & _
        "   MsgBox ""You Clicked the CommandButton""" & vbCrLf & _
        "End Sub"
ActiveDocument.VBProject.VBComponents("ThisDocument").CodeModule.AddFromString sCode

End Sub

Attached is what I have so far in the Word document.

 

[The_Doc_Man]

To be honest, I don't know that you can do that because you would have to delete it while the button-click event is active, and I don't think you can do that. The SAVE AS code has to have a place to run and deleting the button (that enters that "place to run" and works in that context) is like the old Looney Tunes cartoons where you saw off the branch where you are sitting and the tree falls - but the branch doesn't. I don't think that work.

But also, I have not used command buttons on Word so have to admit I am guessing. Therefore, try it. But I believe it will fail with some kind of "Can't delete an active button." Or something to that effect.

The worst that can happen is that it will delete or corrupt the entire file - so make a good backup before actually trying it. Then, if it doesn't work, just delete the experimental version and restore the backup. I know it sounds like I'm being a bit cavalier about this, but seriously... it is not that hard to make a backup and try something to see if it works. Then restore the backup if it didn't. It is extremely unlikely to crash the system so just try the experiment. But don't expect me to bet in favor of it working that way.

 

[Weekleyba]

I got to work almost like I want it.
Clicking the CommandButton first deletes the command button and then goes into Save As.
You enter the name of the new Word file and it shows as new .docx.
The code shrunk to this. See below.
The template is left unchained, and the new Word file .docx is created by the user and saved where they chose.

Private Sub CommandButton1_Click()
 
    CommandButton1.Select
    Selection.Delete
 
    MsgBox ("CommandButton1 deleted.")

    With Application.FileDialog(msoFileDialogSaveAs)
        .FilterIndex = 1
        .Show
        
        If .SelectedItems.Count <> 0 Then
            ThisDocument.SaveAs2 .SelectedItems(1), wdFormatXMLDocument
        End If
    End With

    MsgBox ("Save As completed.")
      
End Sub

The only issue now is, when I open the new .docx file, it still pops up with a warning about running SQL command.
This would be left over from the Mail Merge Recipients.
I need to be able to find some code to delete that during the process of creating the new Word .docx file.
Any ideas on that?

 

[Weekleyba]

I believe I got it!
Here's the code and the attached Word Marco Enabled document that works like I wanted it to.

Private Sub CommandButton1_Click()
 
    CommandButton1.Select
    Selection.Delete
 
    MsgBox ("CommandButton1 deleted.")

    ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

    MsgBox ("Mail Merge Field deleted.")

    With Application.FileDialog(msoFileDialogSaveAs)
        .FilterIndex = 1
        .Show
        
        If .SelectedItems.Count <> 0 Then
            ThisDocument.SaveAs2 .SelectedItems(1), wdFormatXMLDocument
        End If
    End With

    MsgBox ("Save As completed.")
      
End Sub

 

[The_Doc_Man]

To be honest, I'm a little bit surprised that a button can delete itself. I would have made a cash bet (a small one) against it. But congratulations on solving the problem. AND we learned something, so thanks for posting the solution.

 

[Gasman]

I believe I got it!
Here's the code and the attached Word Marco Enabled document that works like I wanted it to.

Private Sub CommandButton1_Click()

    CommandButton1.Select
    Selection.Delete

    MsgBox ("CommandButton1 deleted.")

    ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

    MsgBox ("Mail Merge Field deleted.")

    With Application.FileDialog(msoFileDialogSaveAs)
        .FilterIndex = 1
        .Show
       
        If .SelectedItems.Count <> 0 Then
            ThisDocument.SaveAs2 .SelectedItems(1), wdFormatXMLDocument
        End If
    End With

    MsgBox ("Save As completed.")
     
End Sub

I get that merge message with what you have supplied and as there is no source, I just see the command button and not able to clcik it, just in design mode? I can finally get to the code above after cancelling a slew of error messages.

 

[Weekleyba]

I get that merge message with what you have supplied and as there is no source, I just see the command button and not able to clcik it, just in design mode? I can finally get to the code above after cancelling a slew of error messages.

I'm not sure why that is happening for you. Perhaps your macros are being blocked?
Here's how it works for me using Windows 10, Word 2016, stepping through each screen after pressing the CommandButton1 and finally saving the new .docx to Test PA27. No error messages except the first merge message.

 

[The_Doc_Man]

Now, I'm not going to swear to this either, since my bet was wrong before, but you could probably, for that sequence, make the status messages go away. Just turn off the warnings with a DoCmd.SetWarnings = FALSE (but at the end of the sequence, remember to set it TRUE again.) The file dialog won't go away because it's not a warning. But the "deleted" and "completed" messages might not show up.

 

[Weekleyba]

Now, I'm not going to swear to this either, since my bet was wrong before, but you could probably, for that sequence, make the status messages go away. Just turn off the warnings with a DoCmd.SetWarnings = FALSE (but at the end of the sequence, remember to set it TRUE again.) The file dialog won't go away because it's not a warning. But the "deleted" and "completed" messages might not show up.

I think you're referring to the three messages that pop up. Those three messages ("CommandButton1 deleted"," Mail Merge Field deleted", "Save As completed") are just message boxes I put in there to verify each step as the code runs. They are not needed and can be deleted in the code.
The first warning the pops up when the document is first opened, is the MS Word warning message, "Opening this document will run the following SQL command:". This seems to be difficult to turn off without causing more problems. So I've chosen to leave that alone.

 

[Gasman]

That is the message I see.
Can't get the rest to run though, perhaps because I am on 2007?
Not to worry, just wanted to see it working.