Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Set Calculation property from another application (Office97, SR2)

    Is it possible to set Application.Calculate from another application? I have a Word template that runs some code in Excel, and need to set calulation to xlManual while this is running. I keep getting Error 13 (type mismatch) when I try to use a variable set to Excel Application.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Set Calculation property from another application (Office97, SR2)

    Can you post the relevant part of code? Application.Calculate is an Excel Method, instructing Excel to recalculate everything in the active Workbook. Application.Calculation is a property which can be set as xlCalculationAutomatic, xlCalculationManual, or xlCalculationSemiautomatic.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    New Lounger
    Join Date
    May 2002
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Calculation property from another application (Office97, SR2)

    I've tried the following:

    Dim objExcel as Excel.Application
    Set objExcel = New Excel.Application
    objExcel.Application.Calculation = xlManual

    I've tried using GetObject and CreateObject, but with the same error (13 - Type Mismatch).
    Also tried manually setting the Calculation property (Tools | Options | Calculation) but found that this isn't saved when you close Excel. (Always reverts back to Automatic).

    All help greatly appreciated.

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

    Re: Set Calculation property from another application (Office97, SR2)

    When you create an Excel Application object, there is no open workbook (as there would be when you start Excel manually). You can't set options if no workbook is open.

    BTW, objExcel.Application isn't necessary - objExcel already represents the application.

    So you can write

    Dim objExcel As Excel.Application
    Dim objWorkbook As Excel.Workbook
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Add
    ' or Set objWorkbook = objExcel.Workbooks.Open("filename.xls")
    objExcel.Calculation = xlCalculationManual

Posting Permissions

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