Home |
Search |
Today's Posts |
#1
|
|||
|
|||
type mismatch on saving excel calculation mode via VB app
Well, I'm just plain stumped. I get type mismatch errors early on trying to
save the calculation mode of excel object and a different error when trying to set it to manual: Dim CalculationMode As Variant Private StatusBarSetting As Boolean Dim xl As Excel.Application Dim Argv As Variant Sub Main() Dim n As Integer Dim sArgv As String Argv = Parse_Args(5) sArgv = "" For n = 1 To UBound(Argv) sArgv = sArgv & Argv(n) & vbCrLf Next n MsgBox "Arguments found: " & vbCrLf & sArgv Start_Excel Quit_Excel End Sub Private Sub Start_Excel() Set xl = CreateObject("Excel.Application") If Err.Number < 0 Then End Call SaveSettings End Sub Private Sub Quit_Excel() RestoreSettings xl.Quit Set xl = Nothing End Sub Private Sub SaveSettings() 'On Error Resume Next If xl Is Nothing Then MsgBox "Internal error!!! xl is Nothing!" End End If xl.Interactive = False xl.AskToUpdateLinks = False CalculationMode = xl.Calculation <<-- type mismatch error on this statement but don't know why. xl.Calculation = xlCalculationManual <<- error 1004 on this statement, "Method 'Calculation' of object '_Application' failed and don't know why StatusBarSetting = xl.DisplayStatusBar xl.DisplayAlerts = False xl.Cursor = xlWait xl.ScreenUpdating = False xl.DisplayStatusBar = True End Sub Ken Shaffer |
#2
|
|||
|
|||
type mismatch on saving excel calculation mode via VB app
Try this manual test.
Open excel. Close any workbook that you see (even book1). Then try tools|options. You'll see that's greyed out. Calculationmode is one of those settings that needs an open workbook. Private Sub Start_Excel() Dim xlWkbk As Workbook Set xl = CreateObject("Excel.Application") If Err.Number < 0 Then Exit Sub Set xlWkbk = xl.Workbooks.Add Call SaveSettings End Sub Ken Shaffer wrote: Well, I'm just plain stumped. I get type mismatch errors early on trying to save the calculation mode of excel object and a different error when trying to set it to manual: Dim CalculationMode As Variant Private StatusBarSetting As Boolean Dim xl As Excel.Application Dim Argv As Variant Sub Main() Dim n As Integer Dim sArgv As String Argv = Parse_Args(5) sArgv = "" For n = 1 To UBound(Argv) sArgv = sArgv & Argv(n) & vbCrLf Next n MsgBox "Arguments found: " & vbCrLf & sArgv Start_Excel Quit_Excel End Sub Private Sub Start_Excel() Set xl = CreateObject("Excel.Application") If Err.Number < 0 Then End Call SaveSettings End Sub Private Sub Quit_Excel() RestoreSettings xl.Quit Set xl = Nothing End Sub Private Sub SaveSettings() 'On Error Resume Next If xl Is Nothing Then MsgBox "Internal error!!! xl is Nothing!" End End If xl.Interactive = False xl.AskToUpdateLinks = False CalculationMode = xl.Calculation <<-- type mismatch error on this statement but don't know why. xl.Calculation = xlCalculationManual <<- error 1004 on this statement, "Method 'Calculation' of object '_Application' failed and don't know why StatusBarSetting = xl.DisplayStatusBar xl.DisplayAlerts = False xl.Cursor = xlWait xl.ScreenUpdating = False xl.DisplayStatusBar = True End Sub Ken Shaffer -- Dave Peterson |
#3
|
|||
|
|||
type mismatch on saving excel calculation mode via VB app
Well that explains a lot (always wondered why when I set automatic
calculation that other spreadsheets were manual when opened). Now, why is Calculation property under Application instead of Workbook? Ken PS: cross-posted in programming after realizing that my question was more programming-related. "Dave Peterson" wrote in message ... Try this manual test. Open excel. Close any workbook that you see (even book1). Then try tools|options. You'll see that's greyed out. Calculationmode is one of those settings that needs an open workbook. Private Sub Start_Excel() Dim xlWkbk As Workbook Set xl = CreateObject("Excel.Application") If Err.Number < 0 Then Exit Sub Set xlWkbk = xl.Workbooks.Add Call SaveSettings End Sub |
#4
|
|||
|
|||
type mismatch on saving excel calculation mode via VB app
You'll have to ask MS why they designed excel that way <bg.
And it looks like you multi-posted into the other groups. Crossposting means you sent one message with lots of groups in the header. Multiposting means you sent multiple messages. The nice thing about crossposting is that anyone's reply to any of the crossposts will be seen in all the newsgroups. The bad thing about multiposting is that each is separate and distinct. You have to check multiple spots and others may spend time answering a question that was answered elsewhere. Ken Shaffer wrote: Well that explains a lot (always wondered why when I set automatic calculation that other spreadsheets were manual when opened). Now, why is Calculation property under Application instead of Workbook? Ken PS: cross-posted in programming after realizing that my question was more programming-related. "Dave Peterson" wrote in message ... Try this manual test. Open excel. Close any workbook that you see (even book1). Then try tools|options. You'll see that's greyed out. Calculationmode is one of those settings that needs an open workbook. Private Sub Start_Excel() Dim xlWkbk As Workbook Set xl = CreateObject("Excel.Application") If Err.Number < 0 Then Exit Sub Set xlWkbk = xl.Workbooks.Add Call SaveSettings End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation Setting in Excel | Excel Discussion (Misc queries) | |||
Font and size are in Excel has gone shaded. Will not let me type. | Excel Worksheet Functions | |||
Provide a way to turn off auto-checking excel formulas as I type t | Excel Discussion (Misc queries) | |||
Saving a Excel 97 file into Excel 2003 file | Excel Discussion (Misc queries) | |||
Opening and saving Excel 2003 file from Excel 97. | Excel Discussion (Misc queries) |