Form automation from DOT to DOC

Topic

New Reply

I am automating a Special Order Form; adding a serial number, drop-downs, and text fields.
The working form and VB code is in “SOForm.DOT” which exists on our local file server.
We use Goldmine CRM to launch and populate automated documents with Company, Address, etc.

The goal:
1. Use Goldmine to launch the SOForm.DOT,
2. Populate the form with Customer data,
3. Save the form as “SOForm_#####.DOC,
4. Have the user populate the variable text fields, and a insert a picture, and
5. Save on exit.
Everything works in edit mode…

All of the code works when executed from the VBA editor.
The automated process fails because one of the first code operations is to save the file as a DOC which creates a new clean file with No VBA code.
The failure is isolated to the “Add Picture” code tied to a Button.

This is the code to save file:
ActiveDocument.SaveAs FileName:=”V:DocsSpecial Orders” & “SO_Quote_” & Format(SOquote, “00#”)

Side issue – This is the code to point the dialog box to the picture directory; doesn’t work.

ChangeFileOpenDirectory “W:Jewelry_Pix”

This is the code snippet tied to the “Add A Picture” button:
‘ \ Get a handle to the Insert picture dialog
Set oDialog = Dialogs(wdDialogInsertPicture)
‘ \ Work with dialog
With oDialog
‘ \ Display the dialog
.Display

Amplifications: The only piece broken is the “Add Picture” button/code.
A. In step 3, I immediately save the file as a doc to insure process integrity before the user gets a chance to make any changes.
B. In step 4, the user has an “Add Picture” button tied to the code above. This is the code that is missing from the DOC.
C. There are multiple users involved so adding code to Normal.Dot isn’t feasible.
D. There is an existing Goldmine directory for common documents; i.e., templates.

Questions:
How to I bind the button code that exists in “SOForm. DOT to the new DOCs?
Why doesn’t — work?

TIA,

Reply | Quote

Replies

All of the code works when executed from the VBA editor.
The automated process fails because one of the first code operations is to save the file as a DOC which creates a new clean file with No VBA code.
The failure is isolated to the “Add Picture” code tied to a Button.

I assume this is an ActiveX button from the Control Toolbox, rather than a Word MACROBUTTON field. I’m not familiar with how the ActiveX button works when the code is in the attached template, but I assume there is a way to do it.

Side issue – This is the code to point the dialog box to the picture directory; doesn’t work.

ChangeFileOpenDirectory “W:Jewelry_Pix”

This is the code snippet tied to the “Add A Picture” button:
‘ \ Get a handle to the Insert picture dialog
Set oDialog = Dialogs(wdDialogInsertPicture)
‘ \ Work with dialog
With oDialog
‘ \ Display the dialog
.Display

Check out these threads for some suggestions:
Display Insert Picture dialog (Word 2000) (Lounge forum: Visual Basic for Apps)
Inserting Pictures – unable to change folder (Word2003) (Lounge forum: Word Processing).

Any luck?

Reply | Quote

Check out these threads for some suggestions:
Display Insert Picture dialog (Word 2000) (Lounge forum: Visual Basic for Apps)
Inserting Pictures – unable to change folder (Word2003) (Lounge forum: Word Processing).

Any luck?

++ Thanks for the links – That code works perfectly. ++

Reply | Quote

Amplifications: The only piece broken is the “Add Picture” button/code.
A. In step 3, I immediately save the file as a doc to insure process integrity before the user gets a chance to make any changes.
B. In step 4, the user has an “Add Picture” button tied to the code above. This is the code that is missing from the DOC.
C. There are multiple users involved so adding code to Normal.Dot isn’t feasible.
D. There is an existing Goldmine directory for common documents; i.e., templates.
Questions:
How to I bind the button code that exists in “SOForm. DOT to the new DOCs?
Why doesn’t — work?

Having the code reside in the .dot, so that new .doc’s created from the .dot do not contain code, is the standard model, so there’s nothing wrong with that.

If you add a button from the Controls Toolbox to the .dot, and associate code with the click event of that button, that code will still run fine if you click the button in a new .doc that has been created based on the .dot.
You can do a quick proof of that by adding a button to the .dot and putting a simple line of code like “MsgBox “Button was clicked” in the click event procedure for the button (the click event procedure resides in the ThisDocument module of the .dot – so your Add Picture code needs be in that procedure, or else called from that procedure).

So having the button per se, is probably not the cause of the problem.

Gary

Reply | Quote

Having the code reside in the .dot, so that new .doc’s created from the .dot do not contain code, is the standard model, so there’s nothing wrong with that.

If you add a button from the Controls Toolbox to the .dot, and associate code with the click event of that button, that code will still run fine if you click the button in a new .doc that has been created based on the .dot.
You can do a quick proof of that by adding a button to the .dot and putting a simple line of code like “MsgBox “Button was clicked” in the click event procedure for the button (the click event procedure resides in the ThisDocument module of the .dot – so your Add Picture code needs be in that procedure, or else called from that procedure).

So having the button per se, is probably not the cause of the problem.

Gary

— Button was added from Controls Toolbox to the .DOT and code added.
From VB editor, Button Code works perfectly.
From the open DOT, Button Code works perfectly.
But
When DOT file is Opened, it immediately creates DOC file as part of AutoNew() process.
ActiveDocument.SaveAs FileName:=”V:DocsSpecial Orders” & “SO_Quote_” & Format(SOquote, “00#”)
‘Resulting file = “SO_Quote_10460.doc”

From the resulting DOC, Button code is not there and of course the button doesn’t work.
If I look at the document properties in the VB editor, there is no code at all.

Tried various approaches with no joy:
Killed SaveAs code (resulted in Document5.DOT)
Killed document protection
Moved postion of code in Module
Changed Macro security level
Changed button name….
Added Option button to execute same code (Again, works in DOT, but not DOC)

Code works in DOT
Button works in DOT
No code, no works in DOC
All other items work – DropDowns, Text boxes, etc…

What am I missing?

Reply | Quote

I created a silly example to test this, and it seems to work off the local drive. I haven’t tried to save the document to a network drive.

== Edit ==

From the resulting DOC, Button code is not there and of course the button doesn’t work.
If I look at the document properties in the VB editor, there is no code at all.

But you do see the attached template, and this is the only part that isn’t working?

Reply | Quote

I created a silly example to test this, and it seems to work off the local drive. I haven’t tried to save the document to a network drive.

== Edit ==

But you do see the attached template, and this is the only part that isn’t working?

=== Problem Isolated but not resolved ====

++ Thanks for the ActiveX button template ++

Used it to put the problem in a box.
Your template worked when manually called (double click on file)
My code in your template worked when manually called.

No code worked when called from Goldmine as a DDE process.
Goldmine is run as an enterprise application from the server.
Each workstation runs as a separate instance sharing common resources; i.e., SQL database, template files, etc…
When a user starts a Goldmine Document Automation Procees (create a letter, fill in a form, etc)
it appears the DDE handler breaks something when it launches the local MS Word on the calling workstation.
To date, never had a need for more than “fill in the blank with Goldmine field data” so didn’t know this was in the closet.

Tried moving template file from server to local, killing GM fields, still no joy.

For the moment, it looks like the users will have to manually – Insert | Picture | From File –

Oh well.

Reply | Quote

One perhaps key thing (which didn’t jump out at me the first time you posted it) is the way you’re creating a new document based on the template.

It looks like your code is creating a new document based on the template, by opening the template, and then doing a SaveAs under a new name, with a .doc (rather than .dot) file extension.
That’s never the right way to create a new document from a template (whether programatically or via the user interface – from the user interface, you would want to use File > New from within Word, or double-click the template file from Windows Explorer).
Opening the template and saving it as a .doc, is going to break the ‘based on’ relationship between the document and its parent template, and that’s why your code will not run.

Rather than using code like:

Code:

Activedocument.SaveAs FileName:="V:DocsSpecial Orders" & "SO_Quote_" & Format(SOquote, "00#")

to create a new file, instead you need to use code like:

Code:

Dim oNewDoc As Document
Set oNewdoc = Documents.Add(fullpathandfilenameoftemplate)
oNewDoc.SaveAs "V:DocsSpecial Orders" & "SO_Quote_" & Format(SOquote, "00#")

– this is programatically the equivalent of File > New or double-clicking the template in the user interface.

This will create a new document that retains a link to the parent template, and the button code will run.

Gary

Reply | Quote

One perhaps key thing (which didn’t jump out at me the first time you posted it) is the way you’re creating a new document based on the template.

It looks like your code is creating a new document based on the template, by opening the template, and then doing a SaveAs under a new name, with a .doc (rather than .dot) file extension.
That’s never the right way to create a new document from a template (whether programatically or via the user interface – from the user interface, you would want to use File > New from within Word, or double-click the template file from Windows Explorer).
Opening the template and saving it as a .doc, is going to break the ‘based on’ relationship between the document and its parent template, and that’s why your code will not run.

Rather than using code like:

Code:

Activedocument.SaveAs FileName:="V:DocsSpecial Orders" & "SO_Quote_" & Format(SOquote, "00#")

to create a new file, instead you need to use code like:

Code:

Dim oNewDoc As Document
Set oNewdoc = Documents.Add(fullpathandfilenameoftemplate)
oNewDoc.SaveAs "V:DocsSpecial Orders" & "SO_Quote_" & Format(SOquote, "00#")

– this is programatically the equivalent of File > New or double-clicking the template in the user interface.

This will create a new document that retains a link to the parent template, and the button code will run.

Gary

— Guilty of Cut & Paste mayhem —-
The Save As code was initially called at the end of a process, after all input was done.
I moved it to the front to maintain process numbering integrity; no matter what the user did, the file was saved and tracked.

—————————————————–

Your code is just what the doctor ordered.

New logic problem has presented itself.

I need the AutoNew() process to automatically update the SOquoteNumber.txt file and load the SOquote varible from that file.
Putting your oNewdoc code in AutoNew() creates a loop that continues to automatically and continuously create new docs (Very Exciting but not good).

What is the magic juice that takes me from AutoNew() (updating the quote number file)
to a separate process of starting a new doc that doesn’t create a new doc that creates a new doc, etc…

J

Reply | Quote

Your code is just what the doctor ordered.

New logic problem has presented itself.

I need the AutoNew() process to automatically update the SOquoteNumber.txt file and load the SOquote varible from that file.
Putting your oNewdoc code in AutoNew() creates a loop that continues to automatically and continuously create new docs (Very Exciting but not good).

What is the magic juice that takes me from AutoNew() (updating the quote number file)
to a separate process of starting a new doc that doesn’t create a new doc that creates a new doc, etc…
J

Glad that’s getting you closer!

What I should have mentioned is that the code I posted, cannot be run from within the .dot file on which new documents will be based – for sure, if you do that, you would get the infinite loop you describe.

The magic juice is to run that code from a global add-in template – that should just create one new document at a time, and the AutoNew code in the document template will just run once as well.
Post back if you need more clarification.

Gary

Reply | Quote

Glad that’s getting you closer!

What I should have mentioned is that the code I posted, cannot be run from within the .dot file on which new documents will be based – for sure, if you do that, you would get the infinite loop you describe.

The magic juice is to run that code from a global add-in template – that should just create one new document at a time, and the AutoNew code in the document template will just run once as well.
Post back if you need more clarification.

Gary

With everyone’s help, I’ve got a partial solution to my original goal.
I’ve got a functional form with no automation provided through VB code or Control objects, only Form objects.

The VB/Button Code problem is isolated to Goldmine DDE.

If I run the GM Automated Process, the resulting document is a product of the DOT minus any code; drop boxes, text fields, all work.
If I run the DOT manually (right-click, Run), everything works as expected including the button code.

======================

Separate but associated issue:

As the last step of the quote, the user browses for a jpg file then inserts it.

I have the form Protected for filling in fields and a section break and no protection for the picture area.
As the user fills in the form, they progress field by field with the Tab key.
I have the last Text Field in the unprotected section so the user tabs into it which allows Insert/Picture/From file action.

It all works as expected except – after insertion the picture can not be selected for moving or scaling.
Tools/Option/Edit/Insert-Paste Picutures is set to In-Front-of-Text, but the picture moves down with text inserted above in the protected section.

A problem arises when the user wants to insert more than one picture (they stack) or wants to rescale the picture.

Is there a workaround in Forms tools to get access to the picture?
Remember, in this scenario, I don’t have access to any VB code.

Thanks for all your help so far.

J

Reply | Quote

========= Goldmine Update ==========

Tech Support just weighed in – GM uses a Normal.dot add-in to handle Word automation: merges, linking, faxing, etc.

When GM launches a Word process, it has to associate the resulting document with Normal.dot which precludes any user automation code unless it resides in Normal.dot.
This is graphically illustrated in the VB editor Project Explorer Tree by [Project/References/Normal].
You normally see [Project/References/SOquote] (In my case)

========= Case Closed =============

Reply | Quote

When GM launches a Word process, it has to associate the resulting document with Normal.dot which precludes any user automation code unless it resides in Normal.dot.

This is an odd situation. Normal.dot is always present in the Word environment regardless of a document’s “attached template.” Why attach it explicitly?

Reply | Quote

This is an odd situation. Normal.dot is always present in the Word environment regardless of a document’s “attached template.” Why attach it explicitly?

I don’t want to completely bust GM since I’m running ver 6.7 until I schedule my install of ver 8.5 (the latest).

Tech Support said 8.5 uses DLL’s instead of DDE commands so there is a chance of Joy with the new version.

“Why attach explicitly?” – Silo thinking.

‘Everything you need for CRM is here, right here in Goldmine.’

Of course as soon as you leave the monkey farm, that paradigm falls apart.

Again, thanks for the help.

J

Reply | Quote

With regard to the picture formatting: A big limitation of having unprotected sections in an otherwise protected document, is that many of Word’s usual functions are disabled, even in the unprotected sections. It looks like most of the picture formatting functions fall into this category.
You can work around these limitations using VBA code, but not if you can’t associate VBA code with the document’s attached template, which is the case if I understand your situation right.

Working within the user interface, I can’t see any way to change the text wrapping options. A clunky but still available way to change the picture size is to click once to select the picture, and then manually grab and drag one of the selection handles to resize the picture.

With regard to the limitation of having code in Normal.dot, and having to use Normal.dot as the basis for new documents, like Jefferson said, that is an odd situation, and really a terrible limitation to have imposed on you by a third party. It’s not unusual for third-party software to put code into Normal.dot (since Normal.dot functions as a built-in global add-in template, any code in Normal.dot is always available to any/all documents when using Word), but to force all documents to be based on Normal is pretty awful – that would mean that you can’t use any other templates, not just custom ones, but also any other templates that come out of the box with Word. Hard to understand why Goldmine sees that as a technical necessity.

Gary

Reply | Quote

With regard to the picture formatting: A big limitation of having unprotected sections in an otherwise protected document, is that many of Word’s usual functions are disabled, even in the unprotected sections. It looks like most of the picture formatting functions fall into this category.
You can work around these limitations using VBA code, but not if you can’t associate VBA code with the document’s attached template, which is the case if I understand your situation right.

Yes, no code unless it’s Normal code…

Working within the user interface, I can’t see any way to change the text wrapping options. A clunky but still available way to change the picture size is to click once to select the picture, and then manually grab and drag one of the selection handles to resize the picture.

Not even Click On Picture is available…

With regard to the limitation of having code in Normal.dot, and having to use Normal.dot as the basis for new documents, like Jefferson said, that is an odd situation, and really a terrible limitation to have imposed on you by a third party. It’s not unusual for third-party software to put code into Normal.dot (since Normal.dot functions as a built-in global add-in template, any code in Normal.dot is always available to any/all documents when using Word), but to force all documents to be based on Normal is pretty awful – that would mean that you can’t use any other templates, not just custom ones, but also any other templates that come out of the box with Word. Hard to understand why Goldmine sees that as a technical necessity.

Gary

See previous comment re: Silo thinking, current version DLL’s… etc.

Reply | Quote

If they’ve moved from DDE to DLLs you may have a hope with the new version.

Gary

Reply | Quote

If they’ve moved from DDE to DLLs you may have a hope with the new version.

Gary

I will post update once I get version 8.5 installed.

——————————————————————————–

After thinking about my results, I believe the process used by GM is:

Launch template file
Create Doc file from template (usual Word process)
Process all ‘instant’ code (this is why my AutoNew() code ran)
Explicit Attach Normal.dot to the Doc file.
Process all GM specific code (field merge to insert GM data; i.e., Company, Address, Contact, etc.)
Release Doc control to user.

This scenario expains why some of my code worked (Add & Save Quote – executed as part of AutoNew)
and other code didn’t (Change directory, OnClick, OnExit, etc – dependent on parent .Dot file)

Later this week I will go through my intended VB operations, clean up and annotate actions and post for the benefit of Loungers.

Lots of useful form code in one exemplar:
Use of bookmarks in form automation
Generate and insert incrementing serial number
Set and reset default directory path for this one document
Use ButtonControl to initiate action.
Use protection on a section by section basis to allow user input
Turn off screen “flickering” during form update

J

Reply | Quote