473,397 Members | 2,116 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,397 software developers and data experts.

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 3211

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Don Stevic | last post by:
I am running VS2005 and using VSTO for Word. I keep getting this error saying that there is no source code available for this location when I try and run this application. I am going to...
2
by: Maximus | last post by:
Hi Everyone, I was using Inprocess session objects, but incase of aspnet process crashes the session objects were lost as a result I decided to shift to out of porocess session objects. For this...
2
by: Kevin R. | last post by:
I have been ignoring this problem for a few weeks now, but it's becoming a bit annoying not to mention unproductive. Here it goes: I compile my project with no errors. Then after I debug/run it,...
16
by: | last post by:
Hi all, I have a website running on beta 2.0 on server 2003 web sp1 and I keep getting the following error:- Error In:...
0
by: aartinawani | last post by:
Hi I have an asp.net application running under Windows 2003 Server, IIS 6, for 2 years. Today, suddenly, the site went down at 3:38 pm and the application raised an error entitled "Server too...
2
by: Lonewolf | last post by:
hi all, I realize the example on MSDN for IPCChannel has compile error in VS2005 pro. Either I'm missing something or there's something seriously wrong with MSDN on that section. I reproduce the...
669
by: Xah Lee | last post by:
in March, i posted a essay “What is Expressiveness in a Computer Language”, archived at: http://xahlee.org/perl-python/what_is_expresiveness.html I was informed then that there is a academic...
7
by: Norman Diamond | last post by:
A project depends on VC runtime from Visual Studio 2005 SP1, and DotNet Framework 2. Options are set in the setup project properties, so if these two dependencies are not already installed then...
1
by: BL3WC | last post by:
Hi, I'd created a MDE under Access 2003. It is now under testing stage. Some of the users will use Access 2003 runtime and some will use Access 2007 runtime to run this MDE. I installed the...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.