Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:\Docs\Special 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 -< ChangeFileOpenDirectory "W:\Jewelry_Pix\" >- work?


    TIA,
    Attached Files Attached Files

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by jharrisiii View Post
    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.

    Quote Originally Posted by jharrisiii View Post
    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:
    [topic=446425]Display Insert Picture dialog (Word 2000) (Lounge forum: Visual Basic for Apps)[/topic]
    [topic=760019]Inserting Pictures - unable to change folder (Word2003) (Lounge forum: Word Processing)[/topic].

    Any luck?

  4. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    Quote Originally Posted by jharrisiii View Post
    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 -< ChangeFileOpenDirectory "W:\Jewelry_Pix\" >- 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

  5. #4
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jscher2000 View Post
    Check out these threads for some suggestions:
    [topic=446425]Display Insert Picture dialog (Word 2000) (Lounge forum: Visual Basic for Apps)[/topic]
    [topic=760019]Inserting Pictures - unable to change folder (Word2003) (Lounge forum: Word Processing)[/topic].

    Any luck?

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

  6. #5
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Gary Frieder View Post
    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:\Docs\Special 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?

  7. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    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.

    [attachment=88593:Template-with-ActiveX-button.zip]

    == Edit ==

    Quote Originally Posted by jharrisiii View Post
    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?
    Attached Files Attached Files

  8. #7
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jscher2000 View Post
    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.

    [attachment=88593:Template-with-ActiveX-button.zip]

    == 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. :-(

  9. #8
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    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:\Docs\Special 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:\Docs\Special 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

  10. #9
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Gary Frieder View Post
    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:\Docs\Special 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:\Docs\Special 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...

    [attachment=88608:SubAutoNew-2010-04-21_1.txt]

    J
    Attached Files Attached Files

  11. #10
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    Quote Originally Posted by jharrisiii View Post
    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

  12. #11
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Gary Frieder View Post
    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

  13. #12
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ========= 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 =============

  14. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by jharrisiii View Post
    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?

  15. #14
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    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

  16. #15
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jscher2000 View Post
    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •