Results 1 to 9 of 9
  1. #1
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17

    RunApplication


    Ugggg,

    So I created a Macro button on a Form to run a query, export the data to an excel sheet, then Open Word and the template file under the /f switch command. Everything was working splendid as I added additional templates to the database folder. I created several other buttons, all exactly the same except the "filename.docx" for the other templates. Then after fooling around them in Word mailmerge I added one more template macro button. Now none of the buttons can find the respective "Filename.docx" It thinks it's deleted, renamed, or moved. I realized that I had not put a StopMacro function in the code so I went back and did that, but it did not solve the issue.

    Thinking I may have put something in exclusive mode, I restarted the Computer, but no change.

    The current command line for the RunApplication is winword.exe /f "InitialClaimTemplate.docx"

    The only way I can get it working again is to add the file's entire path, which is not multi user friendly. The database has not moved. I can go to database properties and cut and paste the file path for it into the command line and it pulls right up. So what happened to the use current path method that was working??? Would running the macros over and over without a StopMacro caused an error like this?

    I don't know how to stop all macros without an error.

    Thanks

  2. #2
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17
    I converted it to VBA:

    Code:
    DoCmd.OpenQuery "MailMerge", acViewNormal, acReadOnly    
    DoCmd.OutputTo acOutputQuery, "MailMerge", "ExcelWorkbook(*.xlsx)", "MailMerge.xlsx", False, "", , acExportQualityPrint
        DisplayAlerts = False
        Call Shell("winword.exe /f ""InitialClaimTemplate.docx""", 1)
        Exit Sub
    How do I set the Current path to put it in the Call Shell?

  3. #3
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17
    Fixed it with another Save As and overwriting the database file

  4. #4
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17
    It again lost the ability to open the word templates until I Save As and overwrite the database filename. Any ideas whats going on with this?

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    why do you have an exit sub right after the shell? If there's nothing else, then the end sub would suffice.
    I think there's not enough code showing to figure out the issue, unless it's because you exit the sub before the shell has a chance to process. But you mention SaveAs yet that's not evident in your post. When diverting outside of Access, you often need a pause to allow that portion to initiate. Try putting a stop command or break point right after the shell line to see if the path is resolved. Or show the rest of the pertinent code.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17
    This is the entire code for a button. It's was auto-converted from macro to script via Access.

    Code:
    Private Sub Command19_Click()On Error GoTo Command19_Click_Err
    
    
        ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
        ' <UserInterfaceMacro For="OpenRulesForm" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><Statements><Action Name="OpenForm"><Argument Name="FormName">Rules List
        ' _AXL:ing Form</Argument></Action></Statements></UserInterfaceMacro>
        DoCmd.OpenQuery "MailMerge", acViewNormal, acReadOnly
        DoCmd.OutputTo acOutputQuery, "MailMerge", "ExcelWorkbook(*.xlsx)", "MailMerge.xlsx", False, "", , acExportQualityPrint
        DisplayAlerts = False
        Call Shell("winword.exe /f ""InitialClaimTemplate.docx""", 1)
        Exit Sub
    
    
    
    
    Command19_Click_Exit:
        Exit Sub
    
    
    Command19_Click_Err:
        MsgBox Error$
        Resume Command19_Click_Exit
        
        DisplayAlerts = True
    
    
    End Sub
    Once I get the error that word can no longer find the template file, I re-save the database using the old fashion method of File, Save As and overWriting the database file I'm working in. Then when I click on the Buttons everything works again. I'm leaving the database open all the time, so I'm wondering if the auto-save or something like that is messing up the path name?? I can close the Database and re-open it but the buttons still won't work again until I Re-save using the Save As in the File Menu.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Never heard of this problem. For starters, you ought to be passing the full path of Word and the location of files involved, be they input, output, templates or whatever. Second, unless you need to see this query for some reason, there' no need to open it first. Keep your original form and try this on a copy of it. Add the full paths where shown. Code should wait at STOP line. Then step through one line at a time (F8?) and see if the output is successful. If so, then remove the stop and try again. If still good, then it is likely due to the lack of full paths in your code. I've used block caps to make my suggestions/comments easier to see. Obviously I can't test this, so all it might take to fail is one misplaced quote or something.
    Code:
    Private Sub Command19_Click()
    On Error GoTo Command19_Click_Err
    
    Dim strWordDoc As String, strXlWb As String
    
        ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
        ' <UserInterfaceMacro For="OpenRulesForm" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><Statements><Action Name="OpenForm"><Argument Name="FormName">Rules List
        ' _AXL:ing Form</Argument></Action></Statements></UserInterfaceMacro>
    
    strWordDoc = "full path to Word document here"
    strXlWb = "full path to XL workbook here"
    
    DoCmd.OutputTo acOutputQuery, "MailMerge", "ExcelWorkbook(*.xlsx)", strXlWb, False, "", , acExportQualityPrint
        DisplayAlerts = False '<<MAYBE BETTER TO USE DOCMD.SETWARNINGS FALSE, BUT DON'T SEE A NEED. WHAT MESSAGES OTHERWISE?
    
        Call Shell("winword.exe /f 'FULLL PATH TO DOCX FILE\InitialClaimTemplate.docx', 1)
    STOP
        'Exit Sub '<< NOT NEEDED. REMOVE OR WARNINGS WILL BE LEFT TURNED OFF. SEE BELOW.
    
    Command19_Click_Exit:
    DOCMD.SET WARNINGS TRUE 'DON'T SEE THE NEED FOR THIS.
        Exit Sub
    
    Command19_Click_Err:
        MsgBox "ERROR " & ERR.NUMBER & ": " & ERR.DESCRIPTION
        Resume Command19_Click_Exit
        
        'DisplayAlerts = True '<< NO GOOD HERE - IF NO ERROR THEY ARE LEFT OFF!!
    
    End Sub

  8. #8
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17
    Thank you for this, I will give it a try. My main reasoning for leaving out the entire path is I want the front end to be compatible with multiple users. Once they unzip it, all the template folders will be in the same path as the database, and the script will be able to find the template's path by default. This method works until some point when it fails to find the target template's filename, at that point I use File, Save As and overwrite the database filename and the buttons work again for some amount of time again.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Re-saving the db seems like an odd step to have to take, which might indicate the partial path is the problem. Having to re-save will become a pain, and can anyone do that or are they out of luck if you're on vacation?
    I want the front end to be compatible with multiple users
    This sounds like all users are sharing the same front end, which is ill advised. It's generally accepted that this promotes corruption of the db.
    the script will be able to find the template's path by default
    This I kind of doubt. Code is not smart; it only does what you tell it to with what you tell it.

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums