Create sections in Word document that VBA can see and use?

[Stildawn]

Hi All

As some of you know I have been working on a project that generates a document in word and saves it as a PDF. This project is almost finished and is in a working state as of right now. However I was going through the code and adding in error handling and checks for the users so that it can automate and fix any user mistakes.

At present I open up a word document (called template.docm) which is blank but with saved bookmarks (and their correct locations).

At the moment this template.docm is quite simple, the booksmarks are just one line below each other as follows:

Job bookmark
Screen bookmark
Notes bookmark
Body bookmark
Time bookmakr

The Job bookmark is a job number which is filled in via an Outlook vba code, this is the Outlook VBA code which inserts a user entered job number into the "Job" bookmark in Template.docm:

OUTLOOK CODE:

job = InputBox("Enter in Job Number")
Dim objWord As Object
Dim FileName As StringSet objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Open "G:\Airfreight_Export\Export Airfreight\Template.docm"
objWord.Activate

With objWord.ActiveDocument
.Bookmarks("Job").Range.Text = job
.Application.Run MacroName:="Run"
End With
Set objWord = Nothing

So this opens Template.docm and inserts the job number into the top of the page (since the bookmark "Job" is located at the top of the page)

So on opening the template.docm would look something like this:

Testjob number




With the other bookmarks below currently blank. The Outlook VBA as you can see runs a macro called "Run" this starts all the coding saved in the Template.docm. Here is the first code in the form of a userform with a giant textbox and an "ok" button:

WORD CODE FIRST USERFORM:

Private Sub ok_Click()
On Error GoTo Handler
ActiveDocument.Bookmarks("Notes").Range.Text = "Shipment Notes"
Selection.GoTo What:=wdGoToBookmark, Name:="Notes"
Selection.Expand wdLine
Selection.Font.Bold = True

ActiveDocument.Bookmarks("Body").Range.Text = TextBox1.Value
ActiveDocument.Bookmarks("Time").Range.Text = Now & " - " & Environ$("Username")

Unload comments

With highlow
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    .Show
End With

Exit Sub

As you can see, this code populates a few of the other bookmarks in turn. Firstly just a title for the body ("Shipment Notes:" in bold), then whatever the user entered into the giant textbox, then a timestamp with their user id.

It then runs the next userform. The next two user forms are service selectors, the first form allows the user to select the priority (High, Normal, Low). Then the next one from their selects the job to be performed (HB, Millman, Both)

This will then get the user to this point where the following code will run (there are 9 versions of this code based on what selections the user made for priority and job to be performed userforms):

WORD CODE LAST CODE:

Private Sub hbhigh_Click()
On Error GoTo Handler

'1
Unload highselect

'2
MsgBox ("Set Windows focus on Logis, then click ALT+PRINT SCREEN, then click OK below")

'3
Dim job As String
Selection.GoTo What:=wdGoToBookmark, Name:="Job"
Selection.Expand wdLine
job = Selection.Text
job = Replace(job, Chr(13), "")

'4
Selection.GoTo What:=wdGoToBookmark, Name:="Screen"
Selection.Paste

'5
Selection.GoTo What:=wdGoToBookmark, Name:="Job"

'6
cor = MsgBox("Job Number: " & job & vbNewLine & "Priority: High" & vbNewLine & "Service: HB", vbYesNo, "Is the document and the details below are correct?")
If cor = vbYes Then
    Else
        With highlow
            .StartUpPosition = 0
            .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
            .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
            .Show
        End With
        Exit Sub
End If

'7
Dim DocName As String
DocName = "N:\Customs\Customs_Docs\" & job & "\" & job & ".pdf"
ActiveDocument.ExportAsFixedFormat DocName, 17
ActiveDocument.Saved = True

'8
pri = "H"
subj = "NZAKLAEH"

'9
Dim objOutl
Set objOutl = CreateObject("Outlook.Application")
Set objMailItem = objOutl.CreateItem(olMailItem)
Application.ScreenUpdating = False
ans = MsgBox("Do you need to add any more attachments?", vbYesNo)
If ans = vbYes Then
    Dim dlgOpen As FileDialog
    Dim iTemp As Integer
    Set dlgOpen = Application.FileDialog(FileDialogType:=msoFileDialogOpen)
    With dlgOpen
        .AllowMultiSelect = True
        .Title = "Select additional attachments"
        .Show
        If .SelectedItems.Count <> 0 Then
            For iTemp = 1 To .SelectedItems.Count
                FName = Dir(.SelectedItems(iTemp))
                FileCopy .SelectedItems(iTemp), "N:\Customs\Customs_Docs\" & job & "\" & FName
            Next
        End If
    End With
End If
strEmailAddr = "email.address@domain.com"
objMailItem.Recipients.Add strEmailAddr
objMailItem.Body = ""
objMailItem.Subject = subj & "-" & pri & "-" & job
Dim MyObj As Object, MySource As Object, file As Variant
file = Dir("N:\Customs\Customs_Docs\" & job & "\")
While (file <> "")
    objMailItem.Attachments.Add "N:\Customs\Customs_Docs\" & job & "\" & file
    file = Dir
Wend
objMailItem.Send
Set objMailItem = Nothing
Set objOutl = Nothing
Application.ScreenUpdating = True

'10
MsgBox ("Docs sent to GSC successfully")

'11
Application.Quit

Exit Sub

This is a big code, what it does it:
1. Unloads the previous userform
2. Instructs the user to take a specific screenshot (of another application they have running)
3. Creates a "job" variable from the text in Bookmark "Job" and cleans it (as for some reason it was getting an invisable character which messed things up.
4. Goes to the "Screen" bookmark and then pastes in the screenshot from step 2.
5. Goes to the "Job" bookmark (hopefully this steps updates the word display so the screenshot is visable to the user, though I havent tested this yet)
6. This is the part this post is all about but I'll explain more below, this is the check part, it ask the user to check the document and the details (which are the choices they made)
7. Saves the document as a PDF into the jobs folder
8. Sets some variables these are dependent on what the user selected before (and why there are 9 versions of this code)
9. Basically emails the new docs (and any others in the job folder) using the variables from step 8 to create the subject line
10. Informs the user everything is ok
11. Quits

This is all working great. And at the end the document looks something like this:

test job number
IMG: Screenshot of Application IMG
Shipment Notes:
Here are random shipment notes, more notes
More notes

more notes
some more notes etc.
08/08/13 10.26am - tbaker



Heres the question I have, in step 6 there is this code here:

'6
cor = MsgBox("Job Number: " & job & vbNewLine & "Priority: High" & vbNewLine & "Service: HB", vbYesNo, "Is the document and the details below are correct?")
If cor = vbYes Then
    Else
        With highlow
            .StartUpPosition = 0
            .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
            .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
            .Show
        End With
        Exit Sub
End If

As you can see this is a check so the user can look over the document and continue, or currently go back and reselect the priority and service. What I need here is instead of going back to just reselect the priority and service, I need basically a full restart.

So I would basically like to clear all these bookmarks: Screen (delete the screenshot pasted here), Notes (delete the text here), Body (delete the text here) and Time (delete the text here).... But I need to leave alone the Job bookmark as this contains the job number carried over from Outlook.

Then I can redirect the user to the initial userform code (WORD CODE FIRST USERFORM above) and then essentially begin the process again, allowing the user to re-enter the textbox and thus the timestamp and then on from that put in a new screenshot when they get to step 2 & 4 in WORD CODE LAST CODE above.

But all the while making sure to keep the text in Job bookmark perfectly intact, as well as retaining the other bookmark positions.

I have tried using code like this:

WORD TEST CODE - DOESNT WORK

ActiveDocument.Bookmarks("Screen").Range.Text = ""
ActiveDocument.Bookmarks("Notes").Range.Text = ""
ActiveDocument.Bookmarks("Body").Range.Text = ""
ActiveDocument.Bookmarks("Time").Range.Text = ""

This doesn't seem to do anything.

Also deleteing the bookmarks doesn't clear them (the text inserted etc still remains).

We cant close and reopen Template.docm cause we will lose the Job number carried over from Outlook.

So I was thinking perhaps using bookmarks is limiting, is there another way where I can design the Template.docm before running any of these codes, and define four sections on the page (one for the screenshot, one for the Notes title, one for the Body and one for the Time). And have these sections usable in VBA coding, so I could populate them like I do already, but also clear them and repopulate them at any time?

Any and all solutions are welcome.

Thanks in advance

 

[ZVI]

Text can be added into bookmark rather than after it.
Use BMText for this:

Sub BMText(BMName As String, Text As String)
' ZVI:2013-08-08 http://www.mrexcel.com/forum/general-excel-discussion-other-questions/718912-create-sections-word-document-visual-basic-applications-can-see-use.html
' Add Text into (not after!) bookmark BMName
  On Error GoTo exit_
  Dim Rng As Range
  Set Rng = ActiveDocument.Bookmarks(BMName).Range
  Rng.Text = Text
  ActiveDocument.Bookmarks.Add BMName, Rng
exit_:
  If Err Then MsgBox Err.Description, vbCritical, "Error #" & Err.Number
End Sub
 
' Add text into bookmark
Sub Test_BMText1()
  BMText "Screen", "Text for the Screen bookmark"
End Sub
 
' Clear text of bookmark
Sub Test_BMText2()
  BMText "Screen", ""
End Sub

 

[Stildawn]

Thanks for the reply. I have solved this issue cheers

 

[ZVI]

You're welcome, thanks for the feedback