Setting Calculation Mode to Manual in Excel
Setting Calculation Mode to Manual in Excel
Setting Calculation Mode to Manual in Excel
Setting Calculation Mode to Manual in Excel
Setting Calculation Mode to Manual in Excel
Setting Calculation Mode to Manual in Excel Setting Calculation Mode to Manual in Excel Setting Calculation Mode to Manual in Excel Setting Calculation Mode to Manual in Excel Setting Calculation Mode to Manual in Excel Setting Calculation Mode to Manual in Excel Setting Calculation Mode to Manual in Excel Setting Calculation Mode to Manual in Excel
Setting Calculation Mode to Manual in Excel Setting Calculation Mode to Manual in Excel
Setting Calculation Mode to Manual in Excel
Go Back  Xtreme Visual Basic Talk > > > Setting Calculation Mode to Manual in Excel


Reply
 
Thread Tools Display Modes
  #1  
Old 08-17-2011, 02:05 PM
stevenw1956 stevenw1956 is offline
Newcomer
 
Join Date: Jul 2011
Posts: 21
Default Setting Calculation Mode to Manual in Excel


I learned somewhere that it is a good idea to set Calculation mode to manual in the beginning of an Excel Automation in order to speed up execution. But when I put this line in my program:

Code:
Dim oXL As Excel.Application
oXL = New Excel.Application()
oXL.Calculation = XlCalculation.xlCalculationManual
I get:

Exception from HRESULT: 0x800A03EC

I cannot tell, from the documentation I have seen, how to get any clue what this exception is about. Does anyone know what the issue is?
Reply With Quote
  #2  
Old 08-17-2011, 02:13 PM
stevenw1956 stevenw1956 is offline
Newcomer
 
Join Date: Jul 2011
Posts: 21
Default

I should probably add that when I view the properties of the oXL object in the Object Visualizer during execution, it looks like about half the properties of the object are getting some kind of COM exception. For instance, the Calculation property has a type mismatch HRESULT 0x80020005. And many other properties of the object have the same HRESULT that I listed in my first post.

What am I doing wrong here?
Reply With Quote
  #3  
Old 08-17-2011, 03:25 PM
Roger_Wgnr's Avatar
Roger_Wgnr Roger_Wgnr is offline
CodeASaurus Hex

Forum Leader
* Expert *
 
Join Date: Jul 2006
Location: San Antonio TX
Posts: 2,427
Default

I belive that you need to create a New workbook/worksheet object first as the calcualtion mode is applied to the workbook/worksheet not the excel application.
__________________
Code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. ~Martin Golding
The user is a peripheral that types when you issue a read request. ~Peter Williams
MSDN Visual Basic .NET General FAQ
Reply With Quote
  #4  
Old 08-17-2011, 03:38 PM
stevenw1956 stevenw1956 is offline
Newcomer
 
Join Date: Jul 2011
Posts: 21
Default

I tried what you suggested even though Intellisense was not showing me a calculation property on the workbook object. I got a MissingMember Exception when I tried it. Intellisense shows the Calculation property and the XlCalcuationManual constant on the Excel Application object.
Reply With Quote
  #5  
Old 08-17-2011, 05:34 PM
Roger_Wgnr's Avatar
Roger_Wgnr Roger_Wgnr is offline
CodeASaurus Hex

Forum Leader
* Expert *
 
Join Date: Jul 2006
Location: San Antonio TX
Posts: 2,427
Default

Yes the property is on the Application object but there must be a worksheet available for the application to apply it to (at least theat is what I understand from my reading so far).

What version VS and excel interop are you using?
If you post more of your codi I will try to duplicate.
__________________
Code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. ~Martin Golding
The user is a peripheral that types when you issue a read request. ~Peter Williams
MSDN Visual Basic .NET General FAQ
Reply With Quote
  #6  
Old 08-18-2011, 03:06 AM
stevenw1956 stevenw1956 is offline
Newcomer
 
Join Date: Jul 2011
Posts: 21
Default

Please understand that I posted only what I considered to be the essential code to show the problem. The fact is that I do have two workbooks in my application. I thought posting the rest of the Sub in which the application was declared would only complicate the question unnecessarily. But since you seem to want to see it, here it is:

Code:
Option Strict Off
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop.Word
Imports System.Runtime.InteropServices
Imports System.Reflection

Public Class Form1
    'Define Microsoft Excel Objects
    Dim oXL As Excel.Application
    Dim oWB, oWBOld As Excel.Workbook
    Dim oMLSheet, oAct1Sheet, oOldSheet, oAddressSheet As Excel.Worksheet
    Dim oRng As Excel.Range
    'Define Microsoft Word objects
    Dim wrdApp As Word.Application
    Dim WrdDoc As Word.Document
    Dim iFinalRow As Integer
    Private Const MainListFileName As String = "E:\My Documents\Excel Files\Michael Hughes\Test Folder\MainList.xls"
    Private Const OldFileName As String = "E:\My Documents\Excel Files\Michael Hughes\Test Folder\OldFile.xls"

    Private Sub bttnGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bttnGo.Click
        Dim style As MsgBoxStyle
        bttnGo.Enabled = False    'Disable the button so the user cannot click it anymore without restarting the program
        ' Start Excel and get Application object.
        oXL = New Excel.Application()
        With oXL
            .Visible = True
            .ScreenUpdating = True    'Turn off screen updating to speed up the execution
            .DisplayAlerts = False      'To stop the automatic Save dialog from appearing
            '.Calculation = XlCalculation.xlCalculationManual
        End With
        ' Open MainList Workbook and Create Worksheet Objects
        oWB = oXL.Workbooks.Open(MainListFileName)
        oWBOld = oXL.Workbooks.Open(OldFileName)
        oMLSheet = CType(oWB.Worksheets("MainList"), Worksheet)
        oAct1Sheet = CType(oWB.Worksheets("Act-1"), Worksheet)
        oOldSheet = CType(oWBOld.Worksheets(1), Worksheet)         'Sheet1
        oAddressSheet = CType(oWB.Worksheets("Addresses"), Worksheet)
        DropDuplicates()
        ExtractAddresses()
        AddAct1toMainList()
        ShortenMainList()
        oXL.Visible = True
        oXL.ScreenUpdating = True
        'By Or'ing all the desired MsgBoxStyle values together we activate all of them
        style = MsgBoxStyle.DefaultButton2 Or MsgBoxStyle.YesNo Or MsgBoxStyle.Question Or MsgBoxStyle.MsgBoxSetForeground
        If MsgBox("Please review the data. If you approve and want to continue to the Mail Merge, click Yes, If not, click No, and the program will exit without saving.", _
                  style, "Save Files & Continue?") = MsgBoxResult.Yes Then
            oWB.Save()
            oWB.Saved = True
            oWBOld.Save()
            oWBOld.Saved = True
            MailMerge()
            CleanupExcel()
            CleanupWord()
        Else
            CleanupExcel() 'If the user clicked no, Word will not open, so cleanup only Excel
        End If
        Label1.Text = "Merge complete"
    End Sub
I'm afraid doing so may deflect attention from the problem at hand, which is why I can't get manual calculation mode to work, and why so many of the properties of the application object are showing exceptions in the Object Visualizer.

Please understand that this is still not the whole class, but only the Sub where the execution starts.

Thanks
Reply With Quote
  #7  
Old 08-18-2011, 03:12 AM
stevenw1956 stevenw1956 is offline
Newcomer
 
Join Date: Jul 2011
Posts: 21
Default

Oh, I am using Office XP 2002. Its the Version 10.0 Object Libraries.
Reply With Quote
  #8  
Old 08-18-2011, 07:42 AM
stevenw1956 stevenw1956 is offline
Newcomer
 
Join Date: Jul 2011
Posts: 21
Default

I used the clue you gave me about the necessity of having a workbook created before trying to set the Calculation property. I moved the code to set the property after the creation of the workbooks, and it works now.

Being fairly new to Excel Automation, how could I have discovered that fact? Is it written in the documentation somewhere? Or is it just another one of those gotcha's that we have to discover by trial and error?
Reply With Quote
  #9  
Old 08-18-2011, 09:53 PM
Roger_Wgnr's Avatar
Roger_Wgnr Roger_Wgnr is offline
CodeASaurus Hex

Forum Leader
* Expert *
 
Join Date: Jul 2006
Location: San Antonio TX
Posts: 2,427
Default

I don't know if it is documented anywhere, however, if you open Excel without a workbook being opened (this includes a blank workbook) you will notice that all settings are greyed out indicating you can not set them.

Since you can not set the options from Excel without a workbook open you can also not set them in code without a workbook open.
Another indication is that the settings for a workbook are maintained by the workbook so that each workbook can have a different set of options. If this was not true every workbook would have to use the same settings and you would have to adjust them eveytime you opened a workbook.
__________________
Code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. ~Martin Golding
The user is a peripheral that types when you issue a read request. ~Peter Williams
MSDN Visual Basic .NET General FAQ
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Setting Calculation Mode to Manual in Excel
Setting Calculation Mode to Manual in Excel
Setting Calculation Mode to Manual in Excel Setting Calculation Mode to Manual in Excel
Setting Calculation Mode to Manual in Excel
Setting Calculation Mode to Manual in Excel
Setting Calculation Mode to Manual in Excel Setting Calculation Mode to Manual in Excel Setting Calculation Mode to Manual in Excel Setting Calculation Mode to Manual in Excel Setting Calculation Mode to Manual in Excel Setting Calculation Mode to Manual in Excel Setting Calculation Mode to Manual in Excel
Setting Calculation Mode to Manual in Excel
Setting Calculation Mode to Manual in Excel
 
Setting Calculation Mode to Manual in Excel
Setting Calculation Mode to Manual in Excel
 
-->