469,319 Members | 2,421 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,319 developers. It's quick & easy.

DISP_E_BADVARTYPE error at runtime

I'm getting this error from the JIT compiler at runtime, but only on my boss'
machine, not my development machine. I believe the error is generated from a
call to an Excel object, but I can't tell why or what specific action on the
object is causing it.

We both are running version 2.0 of the .net framework with Office 2003
installed, though I am running XP and she has Win2000. Also, I do not get
this error when running another sub in the same application that also calls
Excel and is otherwise very similar, sharing much of the same code.

This is the offending code (please take it easy, this is my first attempt at
..net and is a port from a VBA app at that):

Module QuickViewer
Public objChosenOne(4) As Object
Public strCode As String
Public strWS As String

Sub QuickView()
Dim e As New Microsoft.Office.Interop.Excel.Application
Dim rngCell As Microsoft.Office.Interop.Excel.Range
Dim bkQuery As Microsoft.Office.Interop.Excel.Workbook
Dim bkLookup As Microsoft.Office.Interop.Excel.Workbook
Dim shQuery As Microsoft.Office.Interop.Excel.Worksheet
Dim intCount As Integer
Dim intSplit(1) As Integer
Dim strTable As String
Dim intColCount As Integer

'query the appropriate database and return to a new workbook
strTable = objChosenOne(2).ToString & "-" & objChosenOne(1).ToString

bkQuery = e.Workbooks.Add

'delete all worksheets except the first
e.DisplayAlerts = False
Do
If bkQuery.Sheets(bkQuery.Sheets.Count).Index 1 Then
bkQuery.Sheets(bkQuery.Sheets.Count).Delete()
Loop Until bkQuery.Sheets.Count = 1
e.DisplayAlerts = True

shQuery = CType(bkQuery.ActiveSheet,
Microsoft.Office.Interop.Excel.Worksheet)

With shQuery.QueryTables.Add(Connection:="ODBC;DSN=MS Access
Database;DBQ=" & strPath & "\" & objChosenOne(1) & ".mdb;DefaultDir=" &
strPath & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;",
Destination:=shQuery.Range("$A$1"))
.CommandText = "SELECT `" & strTable & "`.`Record Number`, `" &
strTable & "`.`Worksheet Code`, `" & strTable & "`.Line, `" & strTable &
"`.Column, `" & strTable & "`.Value" & vbCrLf & "FROM `" & strPath & "\" &
objChosenOne(1) & "`.`" & strTable & "` `" & strTable & "`" & vbCrLf & "WHERE
(`" & strTable & "`.`Record Number`=" & objChosenOne(0) & ")"
.Name = "Query from MS Access Database_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle =
Microsoft.Office.Interop.Excel.XlCellInsertionMode .xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh(BackgroundQuery:=False)
End With

shQuery.Columns(2).Insert()
shQuery.Range("B2",
shQuery.Range("A2").End(Microsoft.Office.Interop.E xcel.XlDirection.xlDown).Offset(0, 1)).Formula = "=C2&D2&E2"

'add worksheet descriptions
bkLookup = e.Workbooks.Open(strPath & "\HCRIS Codes.xls")
shQuery.Columns(2).Insert()
With shQuery.Range("B2:B" & shQuery.Cells(shQuery.Rows.Count,
1).End(Microsoft.Office.Interop.Excel.XlDirection. xlUp).Row)
.NumberFormat = "General"
.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[2],'" & bkLookup.Name &
"'!HCRISCodes,2,0)),"""",VLOOKUP(RC[2],'" & bkLookup.Name &
"'!HCRISCodes,2,0))"
.Copy()

..PasteSpecial(Microsoft.Office.Interop.Excel.XlPa steType.xlPasteValues)
End With

'sort the workbook
shQuery.Range("A1:G" & shQuery.Cells(shQuery.Rows.Count,
1).End(Microsoft.Office.Interop.Excel.XlDirection. xlUp).Row).Sort(Key1:=shQuery.Range("D1"),
Order1:=Microsoft.Office.Interop.Excel.XlSortOrder .xlAscending,
Key2:=shQuery.Range("F1"),
Order2:=Microsoft.Office.Interop.Excel.XlSortOrder .xlAscending,
Key3:=shQuery.Range("E1"),
Order3:=Microsoft.Office.Interop.Excel.XlSortOrder .xlAscending,
Header:=Microsoft.Office.Interop.Excel.XlYesNoGues s.xlYes,
Orientation:=Microsoft.Office.Interop.Excel.XlSort Orientation.xlSortColumns)

'convert query result from text to general format
For Each rngCell In shQuery.Range("G1:G" &
shQuery.Cells(shQuery.Rows.Count,
1).End(Microsoft.Office.Interop.Excel.XlDirection. xlUp).Row)
rngCell.Value = rngCell.Value
Next

'split into individual worksheets

For Each rngCell In shQuery.Range("D2:D" &
shQuery.Cells(shQuery.Rows.Count,
2).End(Microsoft.Office.Interop.Excel.XlDirection. xlUp).Row + 1)
If rngCell.Value <rngCell.Offset(-1, 0).Value Then
If rngCell.Value = strCode Then
intSplit(0) = rngCell.Row
End If
If rngCell.Offset(-1, 0).Value = strCode Then
intSplit(1) = rngCell.Offset(-1, 0).Row
End If
End If
Next

If intSplit(0) = 0 Then 'worksheet not found
e.DisplayAlerts = False
e.Quit()
frmQuickView.ssOut.ActiveSheet.Range("C2").Value = "Worksheet "
& strWS & " does not exist for this cost report"
frmQuickView.BringToFront()
Exit Sub
Else
End If

bkQuery.Worksheets.Add(After:=bkQuery.Sheets(1))
shQuery = CType(bkQuery.Sheets(1),
Microsoft.Office.Interop.Excel.Worksheet)
shQuery.Activate()
shQuery.Range(shQuery.Cells(intSplit(0), 4),
shQuery.Cells(intSplit(1), 4)).Copy()

shQuery = CType(bkQuery.Sheets(2),
Microsoft.Office.Interop.Excel.Worksheet)
shQuery.Activate()

shQuery.Range("A2").PasteSpecial(Microsoft.Office. Interop.Excel.XlPasteType.xlPasteValues)

shQuery = CType(bkQuery.Sheets(1),
Microsoft.Office.Interop.Excel.Worksheet)
shQuery.Activate()
shQuery.Range(shQuery.Cells(intSplit(0), 2),
shQuery.Cells(intSplit(1), 2)).Copy()

shQuery = CType(bkQuery.Sheets(2),
Microsoft.Office.Interop.Excel.Worksheet)
shQuery.Activate()

shQuery.Range("B2").PasteSpecial(Microsoft.Office. Interop.Excel.XlPasteType.xlPasteValues)

shQuery = CType(bkQuery.Sheets(1),
Microsoft.Office.Interop.Excel.Worksheet)
shQuery.Activate()
shQuery.Range(shQuery.Cells(intSplit(0), 5),
shQuery.Cells(intSplit(1), 6)).Copy()

shQuery = CType(bkQuery.Sheets(2),
Microsoft.Office.Interop.Excel.Worksheet)
shQuery.Activate()

shQuery.Range("C2").PasteSpecial(Microsoft.Office. Interop.Excel.XlPasteType.xlPasteValues)

'break the "column" column into actual columns

For Each rngCell In shQuery.Range("D2:D" &
shQuery.Cells(shQuery.Rows.Count,
4).End(Microsoft.Office.Interop.Excel.XlDirection. xlUp).Row)
If rngCell.Value <rngCell.Offset(-1, 0).Value Then
Try
shQuery.Cells(1, intColCount + 5).Value = "'" &
CStr(Format(CInt(rngCell.Value), "0000"))
Catch
End Try
intColCount = intColCount + 1
End If
Next
shQuery.Columns(4).Delete()

'sort by line number and eliminate duplicate lines
shQuery.Range("A2:C" & shQuery.Cells(shQuery.Rows.Count,
1).End(Microsoft.Office.Interop.Excel.XlDirection. xlUp).Row).Sort(Key1:=shQuery.Range("C2"),
Order1:=Microsoft.Office.Interop.Excel.XlSortOrder .xlAscending,
Header:=Microsoft.Office.Interop.Excel.XlYesNoGues s.xlNo,
Orientation:=Microsoft.Office.Interop.Excel.XlSort Orientation.xlSortColumns)
For intCount = CLng(shQuery.Cells(shQuery.Rows.Count,
1).End(Microsoft.Office.Interop.Excel.XlDirection. xlUp).Row) To 3 Step -1
If shQuery.Cells(intCount, 3).Value = shQuery.Cells(intCount -
1, 3).Value Then
shQuery.Rows(intCount).Delete()
End If
Next

'lookup values
shQuery = CType(bkQuery.Sheets(1),
Microsoft.Office.Interop.Excel.Worksheet)
shQuery.Activate()
bkQuery.Names.Add(Name:="LU", RefersTo:=shQuery.Range("$C$2:$G$" &
shQuery.Cells(shQuery.Rows.Count,
3).End(Microsoft.Office.Interop.Excel.XlDirection. xlUp).Row))

shQuery = CType(bkQuery.Sheets(2),
Microsoft.Office.Interop.Excel.Worksheet)
shQuery.Activate()
With shQuery.Range("D2",
shQuery.Cells(shQuery.Cells(shQuery.Rows.Count,
3).End(Microsoft.Office.Interop.Excel.XlDirection. xlUp).Row, shQuery.Cells(1,
shQuery.Columns.Count).End(Microsoft.Office.Intero p.Excel.XlDirection.xlToLeft).Column))
.Formula =
"=IF(ISERROR(VLOOKUP($A2&$C2&D$1,LU,5,0)),"""",VLO OKUP($A2&$C2&D$1,LU,5,0))"
.Copy()

..PasteSpecial(Microsoft.Office.Interop.Excel.XlPa steType.xlPasteValues)
End With

'add headings and formatting

shQuery.Range("A1:C1").Value = "Worksheet Code"
shQuery.Range("A1:C1").Value = "Worksheet"
shQuery.Range("A1:C1").Value = "Line"
shQuery.Rows(1).Font.Bold = True
shQuery.Cells.Columns.AutoFit()
shQuery.Columns(1).Hidden = True
shQuery.Range("D2").Select()
e.ActiveWindow.FreezePanes = True

For Each rngCell In shQuery.Range("D2",
shQuery.Cells(shQuery.Cells(shQuery.Rows.Count,
3).End(Microsoft.Office.Interop.Excel.XlDirection. xlUp).Row, shQuery.Cells(1,
shQuery.Columns.Count).End(Microsoft.Office.Intero p.Excel.XlDirection.xlToLeft).Column))
If IsNumeric(rngCell.Value) Then
If rngCell.Value - Int(rngCell.Value) <0 Then
rngCell.NumberFormat = "_(* #,##0.000000_);_(*
(#,##0.000000);_(* ""-""??_);_(@_)"
Else
rngCell.NumberFormat = "_(* #,##0_);_(* (#,##0);_(*
""-""??_);_(@_)"
End If
End If
Next

e.DisplayAlerts = False
bkQuery.Sheets(1).Delete()
bkQuery.ActiveSheet.Cells.Copy()

With frmQuickView.ssOut.ActiveSheet
.Range("A1").Paste()
.Cells.Interior.ColorIndex = 36
.Range("C2").Select()
.Cells.AutoFit()
End With

'CLEANUP CODE
'brute force method of releasing the COM object may generate error
when object is set to nothing twice
Try
If Not bkQuery Is Nothing Then bkQuery.Close(False)
Catch
End Try
Try
If Not bkLookup Is Nothing Then bkLookup.Close(False)
Catch
End Try
Try
If Not e Is Nothing Then e.Quit()
Catch
End Try

Try
rngCell = Nothing
Catch
End Try
Try
shQuery = Nothing
Catch
End Try
Try
bkQuery = Nothing
Catch
End Try
Try
bkLookup = Nothing
Catch
End Try
Try
e = Nothing
Catch
End Try

If Not rngCell Is Nothing Then
Do
If
(System.Runtime.InteropServices.Marshal.ReleaseCom Object(rngCell) = 0) Then
Exit Do
Loop
End If
If Not shQuery Is Nothing Then
Do
If
(System.Runtime.InteropServices.Marshal.ReleaseCom Object(shQuery) = 0) Then
Exit Do
Loop
End If
If Not bkQuery Is Nothing Then
Do
If
(System.Runtime.InteropServices.Marshal.ReleaseCom Object(bkQuery) = 0) Then
Exit Do
Loop
End If
If Not bkLookup Is Nothing Then
Do
If
(System.Runtime.InteropServices.Marshal.ReleaseCom Object(bkLookup) = 0) Then
Exit Do
Loop
End If
If Not e Is Nothing Then
Do
If
(System.Runtime.InteropServices.Marshal.ReleaseCom Object(e) = 0) Then Exit Do
Loop
End If

rngCell = Nothing
shQuery = Nothing
bkQuery = Nothing
bkLookup = Nothing
e = Nothing

System.GC.Collect()

End Sub

End Module

Thanks!
Aug 9 '06 #1
0 2997

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Don Stevic | last post: by
2 posts views Thread by Maximus | last post: by
16 posts views Thread by | last post: by
reply views Thread by aartinawani | last post: by
669 posts views Thread by Xah Lee | last post: by
7 posts views Thread by Norman Diamond | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.