johns_ar
asked on
Microsoft Word Macro Automation
Hi,
Ive created a word template which has various autotext entries in it. Nothing fancy just date, username, filename etc. My main problem is that by default Word will not automatically update these when the document is saved like it does when you go to print. I want to automate this process so that when a user saves the file the fields are populated correctly. I have almost sorted it using a macro and a few word commands but ive fallen at the final hurdle.
Ive got an updateall macro that updates the fields and filesave, filesaveas, filesaveall and autoopen macro/word commands to finish off the job. The macro/word commands run perfectly and update the fields as required but theres one strange problem thats annoying me. If a document has been saved as "filename x" and then the user continues to work and makes changes then presses save again it doesnt just save over the top of the document, the save box comes up and they have to choose where to save the file, they can just replace the current file but this is not what I want.
Can anyone point me in the right direction here? My macros/word commands are below. Hopefully theres a schoolboy error in there that someone can pick out straight away! Thanks in advance.
Attribute VB_Name = "AutoOpen"
Sub AutoOpen()
Dim aStory As Range
Dim aField As Field
Dim aSection As Section
Dim aHeaderFooter As HeaderFooter
For Each aSection In ActiveDocument.Sections
For Each aHeaderFooter In aSection.Headers
Set aStory = aHeaderFooter.Range
For Each aField In aStory.Fields
aField.Update
Next aField
Next aHeaderFooter
For Each aHeaderFooter In aSection.Footers
Set aStory = aHeaderFooter.Range
For Each aField In aStory.Fields
aField.Update
Next aField
Next aHeaderFooter
Next aSection
End Sub
-------------------------- ---------- ---------- ---------- ---
Attribute VB_Name = "FileSave"
Sub FileSave()
Dim pRange As Word.Range
Dim oFld As Field
On Error GoTo Handler
Retry:
With Dialogs(wdDialogFileSaveAs )
If .Show = 0 Then Exit Sub
End With
System.Cursor = wdCursorNormal
ActiveWindow.Caption = ActiveDocument.FullName
For Each pRange In ActiveDocument.StoryRanges
Do
For Each oFld In pRange.Fields
If oFld.Type = wdFieldFileName Then
oFld.Update
End If
Next oFld
Set pRange = pRange.NextStoryRange
Loop Until pRange Is Nothing
Next
ActiveDocument.Save
Exit Sub
Handler:
If Err.Number = 5155 Or Err.Number = 5153 Then
MsgBox Err.Decription, vbOKOnly
Resume Retry
End If
End Sub
-------------------------- ---------- ---------- ----
Attribute VB_Name = "FileSaveAll"
Sub FileSaveAll()
Dim pRange As Word.Range
Dim oFld As Field
On Error GoTo Handler
Retry:
With Dialogs(wdDialogFileSaveAs )
If .Show = 0 Then Exit Sub
End With
System.Cursor = wdCursorNormal
ActiveWindow.Caption = ActiveDocument.FullName
For Each pRange In ActiveDocument.StoryRanges
Do
For Each oFld In pRange.Fields
If oFld.Type = wdFieldFileName Then
oFld.Update
End If
Next oFld
Set pRange = pRange.NextStoryRange
Loop Until pRange Is Nothing
Next
ActiveDocument.Save
Exit Sub
Handler:
If Err.Number = 5155 Or Err.Number = 5153 Then
MsgBox Err.Decription, vbOKOnly
Resume Retry
End If
End Sub
-------------------------- ---------- ---------- -----
Attribute VB_Name = "FileSaveAs"
Sub FileSaveAs()
Dim pRange As Word.Range
Dim oFld As Field
On Error GoTo Handler
Retry:
With Dialogs(wdDialogFileSaveAs )
If .Show = 0 Then Exit Sub
End With
System.Cursor = wdCursorNormal
ActiveWindow.Caption = ActiveDocument.FullName
For Each pRange In ActiveDocument.StoryRanges
Do
For Each oFld In pRange.Fields
If oFld.Type = wdFieldFileName Then
oFld.Update
End If
Next oFld
Set pRange = pRange.NextStoryRange
Loop Until pRange Is Nothing
Next
ActiveDocument.Save
Exit Sub
Handler:
If Err.Number = 5155 Or Err.Number = 5153 Then
MsgBox Err.Decription, vbOKOnly
Resume Retry
End If
End Sub
-------------------------- ---------- ---------- ---------- -
Sorry its a big long winded!
Ive created a word template which has various autotext entries in it. Nothing fancy just date, username, filename etc. My main problem is that by default Word will not automatically update these when the document is saved like it does when you go to print. I want to automate this process so that when a user saves the file the fields are populated correctly. I have almost sorted it using a macro and a few word commands but ive fallen at the final hurdle.
Ive got an updateall macro that updates the fields and filesave, filesaveas, filesaveall and autoopen macro/word commands to finish off the job. The macro/word commands run perfectly and update the fields as required but theres one strange problem thats annoying me. If a document has been saved as "filename x" and then the user continues to work and makes changes then presses save again it doesnt just save over the top of the document, the save box comes up and they have to choose where to save the file, they can just replace the current file but this is not what I want.
Can anyone point me in the right direction here? My macros/word commands are below. Hopefully theres a schoolboy error in there that someone can pick out straight away! Thanks in advance.
Attribute VB_Name = "AutoOpen"
Sub AutoOpen()
Dim aStory As Range
Dim aField As Field
Dim aSection As Section
Dim aHeaderFooter As HeaderFooter
For Each aSection In ActiveDocument.Sections
For Each aHeaderFooter In aSection.Headers
Set aStory = aHeaderFooter.Range
For Each aField In aStory.Fields
aField.Update
Next aField
Next aHeaderFooter
For Each aHeaderFooter In aSection.Footers
Set aStory = aHeaderFooter.Range
For Each aField In aStory.Fields
aField.Update
Next aField
Next aHeaderFooter
Next aSection
End Sub
--------------------------
Attribute VB_Name = "FileSave"
Sub FileSave()
Dim pRange As Word.Range
Dim oFld As Field
On Error GoTo Handler
Retry:
With Dialogs(wdDialogFileSaveAs
If .Show = 0 Then Exit Sub
End With
System.Cursor = wdCursorNormal
ActiveWindow.Caption = ActiveDocument.FullName
For Each pRange In ActiveDocument.StoryRanges
Do
For Each oFld In pRange.Fields
If oFld.Type = wdFieldFileName Then
oFld.Update
End If
Next oFld
Set pRange = pRange.NextStoryRange
Loop Until pRange Is Nothing
Next
ActiveDocument.Save
Exit Sub
Handler:
If Err.Number = 5155 Or Err.Number = 5153 Then
MsgBox Err.Decription, vbOKOnly
Resume Retry
End If
End Sub
--------------------------
Attribute VB_Name = "FileSaveAll"
Sub FileSaveAll()
Dim pRange As Word.Range
Dim oFld As Field
On Error GoTo Handler
Retry:
With Dialogs(wdDialogFileSaveAs
If .Show = 0 Then Exit Sub
End With
System.Cursor = wdCursorNormal
ActiveWindow.Caption = ActiveDocument.FullName
For Each pRange In ActiveDocument.StoryRanges
Do
For Each oFld In pRange.Fields
If oFld.Type = wdFieldFileName Then
oFld.Update
End If
Next oFld
Set pRange = pRange.NextStoryRange
Loop Until pRange Is Nothing
Next
ActiveDocument.Save
Exit Sub
Handler:
If Err.Number = 5155 Or Err.Number = 5153 Then
MsgBox Err.Decription, vbOKOnly
Resume Retry
End If
End Sub
--------------------------
Attribute VB_Name = "FileSaveAs"
Sub FileSaveAs()
Dim pRange As Word.Range
Dim oFld As Field
On Error GoTo Handler
Retry:
With Dialogs(wdDialogFileSaveAs
If .Show = 0 Then Exit Sub
End With
System.Cursor = wdCursorNormal
ActiveWindow.Caption = ActiveDocument.FullName
For Each pRange In ActiveDocument.StoryRanges
Do
For Each oFld In pRange.Fields
If oFld.Type = wdFieldFileName Then
oFld.Update
End If
Next oFld
Set pRange = pRange.NextStoryRange
Loop Until pRange Is Nothing
Next
ActiveDocument.Save
Exit Sub
Handler:
If Err.Number = 5155 Or Err.Number = 5153 Then
MsgBox Err.Decription, vbOKOnly
Resume Retry
End If
End Sub
--------------------------
Sorry its a big long winded!
ASKER
Thanks SQ!
Sub FileSave()
Dim pRange As Word.Range
Dim oFld As Field
On Error GoTo Handler
Retry:
If ActiveDocument.Name = Null Then
With Dialogs(wdDialogFileSaveAs )
If .Show = 0 Then Exit Sub
End With
End If
System.Cursor = wdCursorNormal
ActiveWindow.Caption = ActiveDocument.FullName
For Each pRange In ActiveDocument.StoryRanges
Do
For Each oFld In pRange.Fields
If oFld.Type = wdFieldFileName Then
oFld.Update
End If
Next oFld
Set pRange = pRange.NextStoryRange
Loop Until pRange Is Nothing
Next
ActiveDocument.Save
Exit Sub
Handler:
If Err.Number = 5155 Or Err.Number = 5153 Then
MsgBox Err.Decription, vbOKOnly
Resume Retry
End If
End Sub
Note the check to see if the file has been saved before - if not it shows the SaveAsDialog, if the file has been saved (i.e. it has a filename) then routine skips the dialog box and saves the doc.
I would also recommend adding a close routine so that is the user closes Word without saving first you still have control over the saving of the file.
Hope this helps,
Thanks,
SQ
Dim pRange As Word.Range
Dim oFld As Field
On Error GoTo Handler
Retry:
If ActiveDocument.Name = Null Then
With Dialogs(wdDialogFileSaveAs
If .Show = 0 Then Exit Sub
End With
End If
System.Cursor = wdCursorNormal
ActiveWindow.Caption = ActiveDocument.FullName
For Each pRange In ActiveDocument.StoryRanges
Do
For Each oFld In pRange.Fields
If oFld.Type = wdFieldFileName Then
oFld.Update
End If
Next oFld
Set pRange = pRange.NextStoryRange
Loop Until pRange Is Nothing
Next
ActiveDocument.Save
Exit Sub
Handler:
If Err.Number = 5155 Or Err.Number = 5153 Then
MsgBox Err.Decription, vbOKOnly
Resume Retry
End If
End Sub
Note the check to see if the file has been saved before - if not it shows the SaveAsDialog, if the file has been saved (i.e. it has a filename) then routine skips the dialog box and saves the doc.
I would also recommend adding a close routine so that is the user closes Word without saving first you still have control over the saving of the file.
Hope this helps,
Thanks,
SQ
ASKER
Cheers for that.
Getting a compile error:
Expected: end of statement
on the
If. Show = 0 Then Exit Sub
and also
Do
For Each oFld In pRange.Fields
If oFld.Type = wdFieldFileName Then
oFld.Update
End If
Next oFld
Set pRange = pRange.NextStoryRange
Loop Until pRange Is Nothing
is all red :(
Getting a compile error:
Expected: end of statement
on the
If. Show = 0 Then Exit Sub
and also
Do
For Each oFld In pRange.Fields
If oFld.Type = wdFieldFileName Then
oFld.Update
End If
Next oFld
Set pRange = pRange.NextStoryRange
Loop Until pRange Is Nothing
is all red :(
oops!
Easier answer - just remove the whole section for the dialog box. Word will automatically show the Save As dialog if the file has never been saved before!
Sub FileSave()
Dim pRange As Word.Range
Dim oFld As Field
On Error GoTo Handler
Retry:
System.Cursor = wdCursorNormal
ActiveWindow.Caption = ActiveDocument.FullName
For Each pRange In ActiveDocument.StoryRanges
Do
For Each oFld In pRange.Fields
If oFld.Type = wdFieldFileName Then
oFld.Update
End If
Next oFld
Set pRange = pRange.NextStoryRange
Loop Until pRange Is Nothing
Next
ActiveDocument.Save
Exit Sub
Handler:
If Err.Number = 5155 Or Err.Number = 5153 Then
MsgBox Err.Decription, vbOKOnly
Resume Retry
End If
End Sub
That one worked for me for both a new document (never saved beofre) and for saving changes to an existing document. Let me know how you get on.
Easier answer - just remove the whole section for the dialog box. Word will automatically show the Save As dialog if the file has never been saved before!
Sub FileSave()
Dim pRange As Word.Range
Dim oFld As Field
On Error GoTo Handler
Retry:
System.Cursor = wdCursorNormal
ActiveWindow.Caption = ActiveDocument.FullName
For Each pRange In ActiveDocument.StoryRanges
Do
For Each oFld In pRange.Fields
If oFld.Type = wdFieldFileName Then
oFld.Update
End If
Next oFld
Set pRange = pRange.NextStoryRange
Loop Until pRange Is Nothing
Next
ActiveDocument.Save
Exit Sub
Handler:
If Err.Number = 5155 Or Err.Number = 5153 Then
MsgBox Err.Decription, vbOKOnly
Resume Retry
End If
End Sub
That one worked for me for both a new document (never saved beofre) and for saving changes to an existing document. Let me know how you get on.
ASKER
Yep that works a treat SQ, nice one.
Only problem now once ive saved the template, open it as a regular document and saved my changes and re opened it the old document name is still showing. Looks like my autoopen doesnt work!
Also footer isnt showing on the document but is on the template. Strange, looking into that now.
Only problem now once ive saved the template, open it as a regular document and saved my changes and re opened it the old document name is still showing. Looks like my autoopen doesnt work!
Also footer isnt showing on the document but is on the template. Strange, looking into that now.
Do you need an AutoNew() as well - so that when the user creates a new document based on the template the macro will run then?
ASKER
Yeah think so!
Final problem is, which is going to be a difficult one, if a user creates a document based on my template including the macros which is then sent outside the organisation the recipient is going to have all sorts of problems opening it due to macro security and virus scanners?
Or can you embed them like fonts ?
Final problem is, which is going to be a difficult one, if a user creates a document based on my template including the macros which is then sent outside the organisation the recipient is going to have all sorts of problems opening it due to macro security and virus scanners?
Or can you embed them like fonts ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The docs will go out as Word docs, only a handful of machines have acrobat pro on them, couple that with the fact id hate to go round showing everyone how to convert a document to pdf with some of the staff here!
Good point about the macro's not running if the recipient has them disabled though. Worth a think over.
Good point about the macro's not running if the recipient has them disabled though. Worth a think over.
fair point about end users!
good luck - hope this has helped you.
Cheers
SQ
good luck - hope this has helped you.
Cheers
SQ
ASKER
certainly has !
thank you m8
thank you m8
Hi johns_ar,
Do you have any other questions you would like answering from this post? If not could you close the question and provide feedback?
Many thanks!
SQ
Do you have any other questions you would like answering from this post? If not could you close the question and provide feedback?
Many thanks!
SQ
ASKER
Nope nothing else. I had accepted one of your answers, do I need to do more?
First look thorough you are using the wdDialogFileSaveAs for the FileSave sub so the user will always be prompted for a filename etc.
I'll try and remember how to do this unless anyone else posts first!