Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Excel template from Word (Word 2000/Excel 2000)

    We have a Microsoft Word global template program that is loaded in Word's startup folder where 35 templates are catagorized as "options" under various tabs on a main, multipage dialog box. The following is example code used to run the various templates. However, we now have a Microsoft Excel expense report template that we would like to run from Word's global template program. Besides referencing Microsoft Excel now for the global template program's file, can someone please share with me a snippet of code to run this one Excel template? The Excel template's name is "Monthly Expense Report" and it will reside in the same "Templates" folder as shown below; of course, it will need to open up in Excel. Many thanks...Mary

    If optInvestigation.Value = True Then
    Unload Me
    Documents.Add Template:= _
    "CentaurusSharedDataTemplatesProduct Quality Investigation Report.dot", NewTemplate:= _
    False
    Exit Sub
    End
    End If

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Excel template from Word (Word 2000/Excel 2000)

    You can use the Windows API function ShellExecute to create a new workbook based on a specified template.

    Put the following declarations at the top of the module:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

    Private Const SW_SHOWMAXIMIZED As Long = 3

    <img src=/w3timages/blueline.gif width=33% height=2>

    The following procedure uses ShellExecute to create a new workbook based on the template whose name is passed as argument.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Function CreateNewWorkbook(strTemplate As String) As Boolean
    Dim lngResult As Long
    lngResult = ShellExecute(0, "New", strTemplate, 0&, 0&, SW_SHOWMAXIMIZED)
    If lngResult <= 32 Then
    MsgBox "Couldn't create new worksheet.", vbExclamation
    Else
    CreateNewWorkbook = True
    End If
    End Function

    <img src=/w3timages/blueline.gif width=33% height=2>

    Call it in your code as follows:

    Dim blnResult As Boolean
    blnResult = CreateNewWorkbook("C:ExcelTest.xlt")

    If a new workbook was created, blnResult will be True. (Of course, you must substitute the path and filename of an existing template or workbook.)

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Excel template from Word (Word 2000/Excel 2000)

    Hello, Hans...Thank you very much for the code! I'm at my home computer and just tested it. It works great! At home, I have Windows Me, Word XP, and Excel 2000. At my workplace, most users have Windows 2000, and Office 2000; however, some users have Windows XP, and Office XP, so I trust the code is going to work in the company's global template program that I mentioned in my post. Many, many thanks for your quick response and for sharing your expert knowledge. Sincerely.....Mary

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Excel template from Word (Word 2000/Excel 2000)

    Hello Mary,

    This code works in all flavors of Windows later than Windows 3.x (and in all versions of Office); it is not version-dependent.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Excel template from Word (Word 2000/Excel 2000)

    Hello, Hans...Help, please....At home, the test code worked fine. Now that I'm at the office and have incorporated it into a real program, when clicking on the Excel option, nothing happens. The program just ends without opening Excel and the named Excel template. The only thing different is that the Excel template is located on a network drive, as are the Word templates, not my local hard drive, which I used at home. I copied the code below eliminating the other 33 Word template options so you can look at it. Can you determine what the problem might be? Many thanks...Mary

    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

    Private Const SW_SHOWMAXIMIZED As Long = 3

    Private Sub CommandButton1_Click()
    If optExpense.Value = True Then
    Unload Me
    Dim blnResult As Boolean
    blnResult = CreateNewWorkbook("CentaurusProcess R&DTemplatesmja Expense Report Template.xlt")
    Exit Sub
    End
    End If

    If optInvestigation.Value = True Then
    Unload Me
    Documents.Add Template:= _
    "CentaurusProcess R&DTemplatesProduct Quality Investigation Report.dot", NewTemplate:= _
    False
    Exit Sub
    End
    End If
    End Sub
    Private Sub CommandButton2_Click()
    End
    End Sub
    Private Sub UserForm_Initialize()
    m_iTotalSteps = MultiPage1.Pages.Count
    MultiPage1.Value = 0
    End Sub
    Function CreateNewWorkbook(strTemplate As String) As Boolean
    Dim lngResult As Long
    lngResult = ShellExecute(0, "New", strTemplate, 0&, 0&, SW_SHOWMAXIMIZED)
    If lngResult <= 32 Then
    MsgBox "Couldn't create new worksheet.", vbExclamation
    Else
    CreateNewWorkbook = True
    End If
    End Function

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Excel template from Word (Word 2000/Excel 2000)

    Hi Mary,

    You put the declaration of ShellExecute and the definition of CreateNewWorkbook in the module behind the UserForm.

    When the user clicks CommandButton1, you start by unloading the form. From that moment, CreateNewWorkbook is not in memory any more, so it can't be executed. There are two ways to solve this:

    1. Move the line Unload Me below the instruction that calls CreateNewWorkbook

    or

    2. Move the declaration of ShellExecute and SW_ShowMaximized and the definition of CreateNewWorkbook to a standard module. You must replace Private by Public, otherwise the declarations won't be recognized.

    Note: you should avoid the End instruction if possible. Look it up in the online help, it is a very abrupt way to end execution. Exit Sub is usually enough.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Excel template from Word (Word 2000/Excel 2000)

    Hans, many, many thanks! Your first option to move the "Unload Me" line below the instruction that calls CreateNewWorkbook does the trick. The Excel template now opens fine. As always, I really appreciate your help....Mary
    P.S. I'll take your advise about using "End"--I've just been using it out of habit without thinking about the consequences. Thanks.

Posting Permissions

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