Save .dotm template to .docx document with VBA

[michaelvawa]

Hi,

I have a small Word template that needs to be migrated to Office 2010. The VBA script collects a few inputs, places them in the document and saves it as filename.doc, leaving the template unchanged of course.

I upgraded the file from .dot to .dotm. This is the VBA code in the background (or at least the part where it has errors, the collecting of data works fine):

naam = naamLAST & ", " & naamFIRST
docname = location & "\" & naam & " - " & datum
Document = naam & " - " & datum
formaatoutput = ThisDocument.SaveFormat
MsgBox ("Saveformat is " & formaatoutput)
MsgBox ("Document Saved as """ & Document & """ on location """ & location & """")
ThisDocument.SaveAs2 FileName:=docname, FileFormat:=Word.WdSaveFormat.wdFormatXMLDocument

Now, the SaveFormat he returns is 15, meaning .dotm. However, I want the document to save it as .docx, losing the VBA script but keeping the data, as it used to do the trick in Word97-format. Some research suggested that it should be SaveFormat = 12, or wdFormatXMLDocument. What am I doing wrong? And even better, how would I be able to solve it?

Thank you very much for your assistance.

[Frosty]

If you're trying to migrate an earlier version of a Word template to Office 2010, the recommended best practice is really to build it from scratch.

Even though SaveAs .dot to .dotm (or .dotx, or .docx or .docm) will work... you're setting your template up for failure later, at least that seems to be what the industry best practice is, according to MicroSystems (a pretty knowledgeable company).

That said, you've got a .dotm file, and you want to use code to SaveAs to a .docx?

Why can't you just create a new document based on the .dotm, and then do a Save?

Sub TestSaveAs(sDocName as string)
Dim oNewDoc As Document

Set oNewDoc = Documents.Add(ThisDocument.FullName)

oNewDoc.SaveAs2 FileName:= sDocName, FileFormat:= wdFormatXMLDocument
End Sub

so you'd replace your SaveAs2 code snippet above with the following line...

'ThisDocument.SaveAs2 FileName:=docname, FileFormat:=Word.WdSaveFormat.wdFormatXMLDocument
TestSaveAs docname

[michaelvawa]

Thanks a lot, that indeed saves the file perfectly as .docx!
However, now the problem occurs that it stores the input data in the .dotm template aswell. And after saving he opens a document2 and runs the macro again, how can I prevent this from happening?

[michaelvawa]

Slight edit: the template works without saving itself now, so that's good.
Only thing now is that once he saved document1.docx under the right name, he opens a new document2.docx and runs the macro again, while the template should actually be closed right after the document is saved. How can I do this? :banghead:

[Frosty]

I've read and re-read your posts and I honestly don't understand what you want to do from the limited information you've given. It sounds like you need to change the order of actions. But I don't know what is happening when.

Can you either post the whole code and the problem, or describe the steps you want to have happen as if to a three year old?

[michaelvawa]

Here is the entire code behind the document:

Dim naam As String
Dim naamLAST As String
Dim naamFIRST As String
Dim model As String
Dim serienum As String
Dim docname As String
Dim vraag As Integer
Dim Document As String
Dim approver As String
Dim monitor As String
Dim monitorser As String
Dim allinone As String
Dim allinoneser As String
Dim dbay As String
Dim dbayser As String
Dim phone As String
Dim phoneser As String
Dim modem As String
Dim modemser As String
Dim datum As String
Dim counter As Integer
Const location As String = "C:\Users\MVANWAS\Desktop\AccFormTest"
Const apro As String = "Alain Chauvaux"
Const NotApplic As String = "Not applicable"

Sub AccFormSave(sDocName As String)
counter = 1

Dim oNewDoc As Document

Set oNewDoc = Documents.Add(ThisDocument.FullName)

oNewDoc.SaveAs2 FileName:=sDocName, FileFormat:=wdFormatXMLDocument

End Sub

Private Sub Document_New()
If counter = 0 Then
MsgBox (counter)
If MsgBox("Do you want to give input on this document?", vbYesNo) = vbYes Then
vraag = MsgBox("HBO/WAFO user?", vbYesNo)
naamFIRST = InputBox("Give all the FIRSTnames of the user(As mentioned in pasport) :", "Laptop")
naamLAST = InputBox("Give LASTname of the user(As mentioned in pasport) :", "Laptop")
model = InputBox("Give the type of the laptop :", "Laptop")
serienum = InputBox("Give the serial of the laptop :", "Laptop")
datum = (Format(Date, "dd-mm-yy"))
naam = naamFIRST & " " & naamLAST
ThisDocument.FormFields("TxtName1").Result = naam
ThisDocument.FormFields("TxtName2").Result = naam
ThisDocument.FormFields("TxtName3").Result = naam
ThisDocument.FormFields("TxtName4").Result = naam
ThisDocument.FormFields("TxtName5").Result = naam
ThisDocument.FormFields("TxtName6").Result = naam
ThisDocument.FormFields("TxtName7").Result = naam
ThisDocument.FormFields("TxtName8").Result = naam
ThisDocument.FormFields("Txtmodel1").Result = model
ThisDocument.FormFields("Txtmodel2").Result = model
ThisDocument.FormFields("Txtmodel3").Result = model
ThisDocument.FormFields("Txtserial1").Result = serienum
ThisDocument.FormFields("Txtserial2").Result = serienum
ThisDocument.FormFields("Txtserial3").Result = serienum

If vraag = 6 Then
monitor = InputBox("Geef het model van het scherm :", "Laptop", NotApplic)
monitorser = InputBox("Geef het serienummer van het scherm :", "Laptop", NotApplic)
allinone = InputBox("Geef het model van de All-In-One / Printer :", "Laptop", NotApplic)
allinoneser = InputBox("Geef het serienummer van de All-In-One / Printer :", "Laptop", NotApplic)
dbay = InputBox("Geef het model van de D-bay :", "Laptop", NotApplic)
dbayser = InputBox("Geef het serienummer van de D-bay :", "Laptop", NotApplic)
phone = InputBox("Geef het model van de telefoon :", "Laptop", NotApplic)
phoneser = InputBox("Geef het serienummer van de telefoon :", "Laptop", NotApplic)
modem = InputBox("Geef het model van de modem :", "Laptop", NotApplic)
modemser = InputBox("Geef het serienummer van de modem :", "Laptop", NotApplic)
'input wegschrijven
ThisDocument.FormFields("TxtMonitorModel1").Result = monitor
ThisDocument.FormFields("TxtMonitorModel2").Result = monitor
ThisDocument.FormFields("TxtMonitorSerial1").Result = monitorser
ThisDocument.FormFields("TxtMonitorSerial2").Result = monitorser
ThisDocument.FormFields("TxtAllInOneModel1").Result = allinone
ThisDocument.FormFields("TxtAllInOneModel2").Result = allinone
ThisDocument.FormFields("TxtAllInOneSerial1").Result = allinoneser
ThisDocument.FormFields("TxtAllInOneSerial2").Result = allinoneser
ThisDocument.FormFields("TxtDBayModel1").Result = dbay
ThisDocument.FormFields("TxtDBayModel2").Result = dbay
ThisDocument.FormFields("TxtDBayserial1").Result = dbayser
ThisDocument.FormFields("TxtDBayserial2").Result = dbayser
ThisDocument.FormFields("TxtPhoneModel1").Result = phone
ThisDocument.FormFields("TxtPhoneModel2").Result = phone
ThisDocument.FormFields("TxtPhoneSerial1").Result = phoneser
ThisDocument.FormFields("TxtPhoneSerial2").Result = phoneser
ThisDocument.FormFields("TxtModemModel1").Result = modem
ThisDocument.FormFields("TxtModemModel2").Result = modem
ThisDocument.FormFields("TxtModemSerial1").Result = modemser
ThisDocument.FormFields("TxtModemSerial2").Result = modemser
End If
approver = InputBox("Who will sign the Authorization form", Laptop, apro)
ThisDocument.FormFields("TxtApprover").Result = approver
naam = naamLAST & ", " & naamFIRST
docname = location & "\" & naam & " - " & datum
docname2 = location & "\" & naam & " - " & datum & ".docx"
Document = naam & " - " & datum & ".docx"
MsgBox ("Document Saved as """ & Document & """ on location """ & location & """")
'ThisDocument.SaveAs2 FileName:=docname, FileFormat:=Word.WdSaveFormat.wdFormatXMLDocument
AccFormSave docname
ThisDocument.Saved = True
Word.Application.Documents.Close SaveChanges:=wdDoNotSaveChanges
Word.Application.Documents.Open (docname2)

End If
End If
End Sub

[Frosty]

Why are you using ThisDocument instead of ActiveDocument?

Seems like you have code in a .dotm (could probably use a .docm -- depends on a few things-- but mostly what you want to happen when you double click on the document).

How do people create a new document from this template?

[michaelvawa]

The document is stored on a server and will move to sharepoint in a while. Users open the document, get the questions from the macro, the script puts the entries in the document and automaticly stores it on a server location. I figured that a docm-file would do the trick, but the company wants it in a template as it used to be in the 97-format...

[Frosty]

I haven't wrapped my mind around as much of the 2010/2007 environment as I would like, but I'm pretty sure the major difference between a .docm and a .dotm is what happens when you double-click the file when it's on your desktop (or any location you can explore to).

With a document-- double-click opens the document
With a template--double-click creates a NEW document based on the template, but still leaves you access to any code in that template (2007/2010 have provided additional save formats, such that there are templates which do not allow you to save code in them, but that's a different issue).

It sounds like you want a template.

Couple other comments:

1. Don't use variable names of existing types.

You wouldn't...
Dim Integer As Integer
so you shouldn't...
Dim Document As Document
and you should especially not...
Dim Document As String

2. Always always always use Option Explicit at the top of your code modules. You have a bug in your code. This will identify it.

3. Try to separate out your functions. I'll show you what I mean (EDITTED: didn't upload a project, as it's just this code).

4. ThisDocument refers to the project the code is running from. ActiveDocument refers to the document currently "primary" in the application window (and generally the document you want to be working on within a Document_New event). Your code (and your process) suggests that you might have confused the two.

There is a perfectly reasonable explanation for this confusion: some Document Management Systems do not open "copies" of templates. Why don't you try the following code in the module.


Option Explicit
Dim naam As String
Dim naamLAST As String
Dim naamFIRST As String
Dim model As String
Dim serienum As String
Dim docname As String
Dim docname2 As String

Dim vraag As Integer
Dim sDocument As String
Dim approver As String
Dim monitor As String
Dim monitorser As String
Dim allinone As String
Dim allinoneser As String
Dim dbay As String
Dim dbayser As String
Dim phone As String
Dim phoneser As String
Dim modem As String
Dim modemser As String
Dim datum As String
Dim counter As Integer
Const location As String = "C:\Users\MVANWAS\Desktop\AccFormTest"
Const apro As String = "Alain Chauvaux"
Const NotApplic As String = "Not applicable"

'-----------------------------------------------------------------------------------------------------------
Sub UpdateFormFields(oInDoc As Document, sWithFieldName As String, sToThisValue As String)
Dim i As Integer
On Error Resume Next
'update each of the same named form fields
Do
i = i + 1
oInDoc.FormFields(sWithFieldName & CStr(i)).Result = sToThisValue
Loop Until Err.Number <> 0
'and try and update the one without a number
oInDoc.FormFields(sWithFieldName).Result = sToThisValue
End Sub

'-----------------------------------------------------------------------------------------------------------
Private Sub Document_New()
Dim oDocumentToWorkOn As Document

'decide which one you want to try out
'Set oDocumentToWorkOn = ThisDocument
Set oDocumentToWorkOn = ActiveDocument

If MsgBox("Do you want to give input on this document?", vbYesNo) = vbYes Then
vraag = MsgBox("HBO/WAFO user?", vbYesNo)
naamFIRST = InputBox("Give all the FIRSTnames of the user(As mentioned in pasport) :", "Laptop")
naamLAST = InputBox("Give LASTname of the user(As mentioned in pasport) :", "Laptop")
model = InputBox("Give the type of the laptop :", "Laptop")
serienum = InputBox("Give the serial of the laptop :", "Laptop")
datum = (Format(Date, "dd-mm-yy"))
naam = naamFIRST & " " & naamLAST

UpdateFormFields oDocumentToWorkOn, "TxtName", naam
UpdateFormFields oDocumentToWorkOn, "Txtmodel", model
UpdateFormFields oDocumentToWorkOn, "Txtserial", serienum

If vraag = vbYes Then
monitor = InputBox("Geef het model van het scherm :", "Laptop", NotApplic)
monitorser = InputBox("Geef het serienummer van het scherm :", "Laptop", NotApplic)
allinone = InputBox("Geef het model van de All-In-One / Printer :", "Laptop", NotApplic)
allinoneser = InputBox("Geef het serienummer van de All-In-One / Printer :", "Laptop", NotApplic)
dbay = InputBox("Geef het model van de D-bay :", "Laptop", NotApplic)
dbayser = InputBox("Geef het serienummer van de D-bay :", "Laptop", NotApplic)
phone = InputBox("Geef het model van de telefoon :", "Laptop", NotApplic)
phoneser = InputBox("Geef het serienummer van de telefoon :", "Laptop", NotApplic)
modem = InputBox("Geef het model van de modem :", "Laptop", NotApplic)
modemser = InputBox("Geef het serienummer van de modem :", "Laptop", NotApplic)
'input wegschrijven
UpdateFormFields oDocumentToWorkOn, "TxtMonitorModel", monitor
UpdateFormFields oDocumentToWorkOn, "TxtMonitorSerial", monitorser
UpdateFormFields oDocumentToWorkOn, "TxtAllInOneModel", allinone
UpdateFormFields oDocumentToWorkOn, "TxtAllInOneSerial", allinoneser
UpdateFormFields oDocumentToWorkOn, "TxtDBayModel", dbay
UpdateFormFields oDocumentToWorkOn, "TxtDBayserial", dbayser
UpdateFormFields oDocumentToWorkOn, "TxtPhoneModel", phone
UpdateFormFields oDocumentToWorkOn, "TxtPhoneSerial", phoneser
UpdateFormFields oDocumentToWorkOn, "TxtModemModel", modem
UpdateFormFields oDocumentToWorkOn, "TxtModemSerial", modemser
End If
approver = InputBox("Who will sign the Authorization form", "Laptop", apro)

UpdateFormFields oDocumentToWorkOn, "TxtApprover", approver

naam = naamLAST & ", " & naamFIRST
docname = location & "\" & naam & " - " & datum
docname2 = location & "\" & naam & " - " & datum & ".docx"
sDocument = naam & " - " & datum & ".docx"
MsgBox ("Document Saved as """ & sDocument & """ on location """ & location & """")

oDocumentToWorkOn.SaveAs2 FileName:=docname, FileFormat:=Word.WdSaveFormat.wdFormatXMLDocument

'do you want to close something here?
'oDocumentToWorkOn.Saved = True
'Word.Application.Documents.Close SaveChanges:=wdDoNotSaveChanges
'Word.Application.Documents.Open (docname2)

End If
End Sub

[michaelvawa]

Works perfectly, you're a genious!
Thanks a lot for the assistance, it makes sense when I read through the code.