Fieldupdate before saving and save document where documentname is resut of field values

JacquesW · #1 02-27-2015, 02:46 AM

I am a novice in macro and VBA. I know a little of macro's and I am not afraid tot experiment with VBA.

My question is:
I created a document with a number of fields in it.
There are also crosslinks to fields in the header.
When the document is closed by a user I want the following to happen:
1. Fields are updated (also the crosslinks in the header and or footer)
2. A popup appears that gives the user a choice between:
- saving as .docx or
- saving as .pdf
of course thereafter the code to execute the users choice: .docx or .pdf.

When executing that code I want the documentname pre filled as a result of a prefix plus the date plus the values that the user has filled in in three fields.

sometjhing like this:
VBP [yyyymmdd] [value field 1] [value field 2] [value field 3].docx or .pdf
"VBP 20150226 Surname 00045678 salary.docx"

The user then only can choose the location where to save the document.

I know for a novice I am asking a lot at once.
At the same tine I do not know if this is best done in VBA or with a macro?

I hope someone can help me.

gmayor · #2 03-01-2015, 03:09 AM

What you ask is quite straightforward, but what kind of 'fields' are involved?
You can call the example macro at http://www.gmayor.com/installing_macro.htm to update your fields, but how to get the data from them will depend on what type of fields they are.

JacquesW · #3 03-01-2015, 05:52 AM

Hello Graham,

Thanks for your reply.
The fields I use are two tekstfields and one where the user has to choose out of a dropdown list.
I already read your page about installing macro's, but I could not figure out how to get things working.
As I said beforte, I do not know much about VBA or macro programming.

So I appreciate your help.
Jacques Wolters

gmayor · #4 03-01-2015, 07:01 AM

Are these Legacy dropdown form fields, or dropdown Content Controls. Both require a user to pick from a dropdown.

JacquesW · #5 03-01-2015, 10:41 AM

All fields are Content Control fields.
I attached the file so you can see what it looks like.
It is in dutch.

I realise now that I have a macro installed and the file is no .docx, but a .docm.

For now I added a button to update the header fields, ann that works.
But . . . .
Not all users know how to use it and are not aware of updating before saving.
That is why I want the saving proces to be automated the way I asked before.
So they have no alternative but .docm or .pdf.

P.S.
Is it possible to set it up as a macro template (dotm)?

gmayor · #6 03-02-2015, 12:20 AM

OK, now we are getting somewhere

You can remove the button as it is not required and adds an unnecessary step. However you must add unique titles to each of the content controls.

Delete the code from the ThisDocument module and replace the code in the NewMacros folder of the document with the following (I would rename the module to 'modMain' but it will work without). You will have to translate the message box texts to Dutch and ensure that you use the names shown for the titles of the two fields that make up the name.

The code includes two simple standard functions, to get the pathname and to update the fields (which you can use in your projects), a function to get the name from the fields and a pair of macros to intercept the Save and SaveAs commands.

Save the document as a macro enabled template and create new documents from it.

When the user clicks Save or SaveAs the code runs

Code:

Option Explicit
Sub FileSave()
    Call FileSaveAs
lbl_Exit:
    Exit Sub
End Sub

Sub FileSaveAs()
Dim iAsk As Long
Dim strPath As String
Dim oCC As ContentControl
    For Each oCC In ActiveDocument.ContentControls
        If oCC.Title = "Geboortenaam" Or oCC.Title = "SAP nummer" Then
            If oCC.Range.Text = oCC.PlaceholderText Then
                MsgBox "Complete the '" & oCC.Title & "' field"
                GoTo lbl_Exit
            End If
        End If
    Next oCC
    UpdateAllFields
    iAsk = MsgBox("Save as PDF Format?", vbYesNoCancel)
    strPath = BrowseForFolder("Select the folder to save the document")
    Select Case iAsk
        Case vbYes
            ActiveDocument.SaveAs2 FileName:=strPath & GetFilename(ActiveDocument) & ".pdf", Fileformat:=wdFormatPDF
        Case vbNo
            ActiveDocument.SaveAs2 FileName:=strPath & GetFilename(ActiveDocument) & ".docx", Fileformat:=wdFormatXMLDocument
        Case Else
            MsgBox "Document Not Saved!"
    End Select
lbl_Exit:
    Exit Sub
End Sub

Sub UpdateAllFields()
Dim oStory As Range
    For Each oStory In ActiveDocument.StoryRanges
        oStory.Fields.Update
        If oStory.StoryType <> wdMainTextStory Then
            While Not (oStory.NextStoryRange Is Nothing)
                Set oStory = oStory.NextStoryRange
                oStory.Fields.Update
            Wend
        End If
    Next oStory
    Set oStory = Nothing
lbl_Exit:
    Exit Sub
End Sub

Function GetFilename(oDoc As Document) As String
Dim oCC As ContentControl
Dim strName As String
Dim strNumber As String
Dim strDate As String
    With oDoc
        For Each oCC In .ContentControls
            If oCC.Title = "Geboortenaam" Then strName = oCC.Range.Text
            If oCC.Title = "SAP nummer" Then strNumber = oCC.Range.Text
        Next oCC
        strDate = Format(Date, "yyyymmdd")
        GetFilename = "VBP " & strDate & Chr(32) & strName & Chr(32) & strNumber
    End With
lbl_Exit:
    Exit Function
End Function

Function BrowseForFolder(Optional strTitle As String) As String
Dim fDialog As FileDialog
    On Error GoTo err_Handler
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    With fDialog
        .Title = strTitle
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewList
        If .Show <> -1 Then GoTo err_Handler:
        BrowseForFolder = fDialog.SelectedItems.Item(1) & Chr(92)
    End With
lbl_Exit:
    Exit Function
err_Handler:
    BrowseForFolder = vbNullString
    Resume lbl_Exit
End Function

JacquesW · #7 03-04-2015, 07:28 AM

I have been testing, and it works so far.

I have two questions:

How can I ad the value of the dropdownlist field "Besluitsoort" to add it to the filename for saving?
I tried the code you used for for the other two fields, but that does not work.
Is it possible to show the normal save/saveas window with the prepared name placed in it?

If my question is not clear enough, please ask me to explain it further.

gmayor · #8 03-04-2015, 10:40 PM

There is no dropdown field in the example document with that title. There is a heading - Soort besluit - but the associated dropdown is untitled. Is that the field you meant?

If it is then it contains values like - Arbeidstijd: Uitbreiding - which includes an illegal filename character. What exactly is it that you want to include in your filenames.

JacquesW · #9 03-05-2015, 12:15 AM

Sorry for the mix up of the field name.
It is indeed that field.
You do not have to worry about the character ":".
I am going to set Title and Label of this field to "Besluitsoort".
I am also going to change the values so there will be no more illegal file name characters in it.
I want the choosen value to be added as the last part of the filename.

What I did not explain in my previous question is the following.
The macro works great, but the presentation/screen to choose a directory where to save the document is confusing for most of my formusers.
That is why I would like it to be the normal screen that appears when you want tot save a document, but then with the documentname already preset in the "saveas" box, and with the fileformat set to the choice the user has made (pdf or docx).

I hope this makes sense to you.
Thanks for the help so far. I already learned a lot about macro etc.

gmayor · #10 03-05-2015, 01:03 AM

The following should work if you change the form as indicated. I have left in the line for testing with the colon in place:

Code:

Option Explicit
Sub FileSave()
    If Not ActiveDocument = ThisDocument Then
        Call FileSaveAs
    Else
        ActiveDocument.Save
    End If
lbl_Exit:
    Exit Sub
End Sub

Sub FileSaveAs()
Dim iAsk As Long
Dim strPath As String
Dim oCC As ContentControl
    If Not ActiveDocument = ThisDocument Then
        For Each oCC In ActiveDocument.ContentControls
            If oCC.Title = "Geboortenaam" _
               Or oCC.Title = "SAP nummer" _
               Or oCC.Title = "Besluitsoort" Then
                If oCC.Range.Text = oCC.PlaceholderText Then
                    MsgBox "Complete the '" & oCC.Title & "' field"
                    GoTo lbl_Exit
                End If
            End If
        Next oCC
        UpdateAllFields
        iAsk = MsgBox("Save as PDF Format?", vbYesNoCancel)
        Select Case iAsk
            Case vbYes
                With Dialogs(wdDialogFileSaveAs)
                    .Name = GetFilename(ActiveDocument) & ".pdf"
                    .Format = wdFormatPDF
                    .Show
                End With
            Case vbNo
                With Dialogs(wdDialogFileSaveAs)
                    .Name = GetFilename(ActiveDocument) & ".docx"
                    .Format = wdFormatXMLDocument
                    .Show
                End With
            Case Else
                MsgBox "Document Not Saved!"
        End Select
    Else
        Dialogs(wdDialogFileSaveAs).Show
    End If
lbl_Exit:
    Exit Sub
End Sub

Private Sub UpdateAllFields()
Dim oStory As Range
    For Each oStory In ActiveDocument.StoryRanges
        oStory.Fields.Update
        If oStory.StoryType <> wdMainTextStory Then
            While Not (oStory.NextStoryRange Is Nothing)
                Set oStory = oStory.NextStoryRange
                oStory.Fields.Update
            Wend
        End If
    Next oStory
    Set oStory = Nothing
lbl_Exit:
    Exit Sub
End Sub

Private Function GetFilename(oDoc As Document) As String
Dim oCC As ContentControl
Dim strName As String
Dim strNumber As String
Dim strDate As String
Dim strBesluitsoort As String
    With oDoc
        For Each oCC In .ContentControls
            If oCC.Title = "Geboortenaam" Then strName = oCC.Range.Text
            If oCC.Title = "SAP nummer" Then strNumber = oCC.Range.Text
            If oCC.Title = "Besluitsoort" Then strBesluitsoort = oCC.Range.Text
            'If oCC.Title = "Besluitsoort" Then strBesluitsoort = Replace(oCC.Range.Text, Chr(58), "")
        Next oCC
        strDate = Format(Date, "yyyymmdd")
        GetFilename = "VBP " & strDate & Chr(32) & strName & Chr(32) & strNumber & Chr(32) & strBesluitsoort
    End With
lbl_Exit:
    Exit Function
End Function

JacquesW · #11 03-05-2015, 08:17 AM

I have edited your code with the names I use.
First it did not run the way i think it should.
After I inactivated (') the lines that tested for "activedocument", all worked as I aspected it would do.

So I am pleased with the result, but have just a question about these lines I had to inactivate.

What is the purpose of these lines?

gmayor · #12 03-05-2015, 10:46 PM

The purpose of the test is to stop the code from working when editing the template, to allow you to save the template normally. Templates work on the premise that you create new documents from them. i.e the activedocument, whereas the template holding the code is thisdocument.

JacquesW · #13 03-06-2015, 12:16 AM

Thank you for the explanation.
I will test it and see if it works.

JacquesW · #14 03-06-2015, 12:39 AM

Just did the test and it works perfectly.

All the above answered my Original question.
So case closed, problem solved.
But. . . .

I want to add an other control to the template.
The field you named "SAP nummer", I call it Personeelsnummer (employeenumber) has to have exeactly 8 digits.
If the number has less digits the user has to add zeros before the number.
So 1234 becomes 00001234 or 123456 becomes 00123456.

Is there a way I can get the user to do so and also that he can not save the template if the nmber is not 8 digits long?

gmayor · #15 03-06-2015, 01:22 AM

The 8 digits check requires two changes

1. In the FileSaveAs macro immediately before the line 'Next oCC'
add:

Code:

           If oCC.Title = "Personeelsnummer" Then
                If Len(oCC.Range) > 8 Then
                    MsgBox "'Personeelsnummer' mag alleen 8 cijfers hebben."
                    GoTo lbl_Exit
                End If
            End If

2. In the 'GetFilename' function add the line

Code:

strNumber = Format(strNumber, "00000000")

immediately after 'Next oCC'

Translation to Dutch courtesy of Google, so feel free to fix the grammar

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How can I save a Word Document as a PDF file with a merged field filename?	kp2009	Word VBA	5	08-27-2015 11:45 PM
Summing up values in a Single Merge Field	Beowolf	Mail Merge	1	03-07-2014 03:26 PM
Save merged document as concatenated merge field values	texas791	Word VBA	4	02-25-2014 07:35 PM
Word ask to save template whenever i save a derived document	jorbjo	Word	3	10-04-2012 10:52 AM
Need to save QlikOCX Plugin as image while saving the PPT	udaya.kumar	PowerPoint	0	06-09-2012 11:54 PM