Adding data to Excel from VB.NET

B

Brian Conklin

Hello Eneryone,
I am having a problem. I have written a little app that will take a text
"pipe" delimited file and place all of the values in to an Excel
spreadsheet. It works great on any of my XP Pro machines. When I install the
app on a Win2K Pro machine, I get the following error message:

************** Exception Text **************
System.Runtime.InteropServices.COMException (0x80020008): Bad variable type.
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Excel.Range.set__Default(Object RowIndex, Object ColumnIndex, Object )
at Collections.frmMain.butXfer_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons
button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
IntPtr wparam, IntPtr lparam)


************** Loaded Assemblies **************
mscorlib
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/winnt/microsoft.net/framework/v1.1.4322/mscorlib.dll
----------------------------------------
System
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/winnt/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dll
----------------------------------------
System.Drawing
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/winnt/assembly/gac/system.drawing/1.0.5000.0__b03f5f7f11d50a3a/system.drawing.dll
----------------------------------------
Collections
Assembly Version: 1.0.1329.16914
Win32 Version: 1.0.1329.16914
CodeBase:
file:///C:/Program%20Files/Mason%20General%20Hospital/Collections/Collections.exe
----------------------------------------
System.Windows.Forms
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/winnt/assembly/gac/system.windows.forms/1.0.5000.0__b77a5c561934e089/system.windows.forms.dll
----------------------------------------
Microsoft.VisualBasic
Assembly Version: 7.0.5000.0
Win32 Version: 7.10.3052.4
CodeBase:
file:///c:/winnt/assembly/gac/microsoft.visualbasic/7.0.5000.0__b03f5f7f11d50a3a/microsoft.visualbasic.dll
----------------------------------------
Interop.Excel
Assembly Version: 1.4.0.0
Win32 Version: 1.4.0.0
CodeBase:
file:///C:/Program%20Files/Mason%20General%20Hospital/Collections/Interop.Excel.DLL
----------------------------------------
System.Xml
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/winnt/assembly/gac/system.xml/1.0.5000.0__b77a5c561934e089/system.xml.dll
----------------------------------------

The code I am using is:

Dim objExcel As New Excel.Application

Dim objWB As Excel.Workbook = objExcel.Workbooks.add

Dim objWS As Excel.Worksheet = CType(objWB.Worksheets(1), Excel.Worksheet)

objExcel.Visible = True

Cursor.Current = Cursors.WaitCursor

Dim sr As System.IO.StreamReader = New
System.IO.StreamReader(System.IO.Path.GetTempPath() & "Collections.txt")

Dim line As String, MyArray() As String, item As String, temp As String

Dim cnt As Int32

Dim lcol As Long, lrow As Long

lrow = 1

Do

line = sr.ReadLine()

MyArray = Split(line, "|", , CompareMethod.Text)

For lcol = 1 To UBound(MyArray)

Select Case lcol

Case Is = 2

objWS.Cells(lrow, lcol) =
Microsoft.VisualBasic.Left(MyArray(lcol), (Len(MyArray(lcol)) - 2)) & "." &
Microsoft.VisualBasic.Right(MyArray(lcol), 2)

Case Is = 11

If Len(MyArray(lcol)) > 0 Then

item = MyArray(lcol)

MyArray(lcol) = Microsoft.VisualBasic.Left(item, 2) & "/" &
Microsoft.VisualBasic.Mid(item, 3, 2) & "/" &
Microsoft.VisualBasic.Right(item, 4)

objWS.Cells(lrow, lcol).Value = MyArray(lcol)

End If

Case Is = 12

If Len(MyArray(lcol)) > 0 Then

item = MyArray(lcol)

MyArray(lcol) = Microsoft.VisualBasic.Left(item, 2) &
"/" & Microsoft.VisualBasic.Mid(item, 3, 2) & "/" &
Microsoft.VisualBasic.Right(item, 4)

objWS.Cells(lrow, lcol).Value = MyArray(lcol)

End If

Case Is = 15

If Len(MyArray(lcol)) > 0 Then

item = MyArray(lcol)

MyArray(lcol) = Microsoft.VisualBasic.Left(item, 2) &
"/" & Microsoft.VisualBasic.Mid(item, 3, 2) & "/" &
Microsoft.VisualBasic.Right(item, 4)

objWS.Cells(lrow, lcol).Value = MyArray(lcol)

End If

Case Else

objWS.Cells(lrow, lcol) = MyArray(lcol)

End Select

If lcol = 2 Then

Else

objWS.Cells(lrow, lcol) = MyArray(lcol)

End If

Next

lrow += 1

Loop Until line Is Nothing

Cursor.Current = Cursors.Default

sr.Close()

If System.IO.File.Exists(System.IO.Path.GetTempPath() & "Collections.txt")
Then

System.IO.File.Delete(System.IO.Path.GetTempPath() & "Collections.txt")

End If

objWS.Columns.AutoFit()

objWS = Nothing

objWB = Nothing

objExcel = Nothing
 
M

Matt Evans [MSFT]

If i had to guess i'd say you're using different versions of the .net
framework, or more probably, different versions of excel between the two
computers.

You may be able to change your code to use latebinding and avoid this
problem. Otherwise I think you'll need excel-version specifc COM interop
assemblies..


Matt Evans
--

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Note: For the benefit of the community-at-large, all responses to this
message are best directed to the newsgroup/thread from which they
originated.
--------------------
From: "Brian Conklin" <[email protected]>
Subject: Adding data to Excel from VB.NET
Date: Fri, 22 Aug 2003 13:59:30 -0700
Lines: 200
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <#[email protected]>
Newsgroups: microsoft.public.dotnet.languages.vb
NNTP-Posting-Host: list.masongeneral.com 66.119.204.11
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.vb:129795
X-Tomcat-NG: microsoft.public.dotnet.languages.vb

Hello Eneryone,
I am having a problem. I have written a little app that will take a text
"pipe" delimited file and place all of the values in to an Excel
spreadsheet. It works great on any of my XP Pro machines. When I install the
app on a Win2K Pro machine, I get the following error message:

************** Exception Text **************
System.Runtime.InteropServices.COMException (0x80020008): Bad variable type.
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Excel.Range.set__Default(Object RowIndex, Object ColumnIndex, Object )
at Collections.frmMain.butXfer_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons
button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
IntPtr wparam, IntPtr lparam)


************** Loaded Assemblies **************
mscorlib
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/winnt/microsoft.net/framework/v1.1.4322/mscorlib.dll
----------------------------------------
System
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/winnt/assembly/gac/system/1.0.5000.0__b77a5c561934e089/system.dl l
----------------------------------------
System.Drawing
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/winnt/assembly/gac/system.drawing/1.0.5000.0__b03f5f7f11d50a3a/s ystem.drawing.dll
----------------------------------------
Collections
Assembly Version: 1.0.1329.16914
Win32 Version: 1.0.1329.16914
CodeBase:
file:///C:/Program%20Files/Mason%20General%20Hospital/Collections/Collectio ns.exe
----------------------------------------
System.Windows.Forms
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/winnt/assembly/gac/system.windows.forms/1.0.5000.0__b77a5c561934 e089/system.windows.forms.dll
----------------------------------------
Microsoft.VisualBasic
Assembly Version: 7.0.5000.0
Win32 Version: 7.10.3052.4
CodeBase:
file:///c:/winnt/assembly/gac/microsoft.visualbasic/7.0.5000.0__b03f5f7f11d 50a3a/microsoft.visualbasic.dll
----------------------------------------
Interop.Excel
Assembly Version: 1.4.0.0
Win32 Version: 1.4.0.0
CodeBase:
file:///C:/Program%20Files/Mason%20General%20Hospital/Collections/Interop.E xcel.DLL
----------------------------------------
System.Xml
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase:
file:///c:/winnt/assembly/gac/system.xml/1.0.5000.0__b77a5c561934e089/syste m.xml.dll
----------------------------------------

The code I am using is:

Dim objExcel As New Excel.Application

Dim objWB As Excel.Workbook = objExcel.Workbooks.add

Dim objWS As Excel.Worksheet = CType(objWB.Worksheets(1), Excel.Worksheet)

objExcel.Visible = True

Cursor.Current = Cursors.WaitCursor

Dim sr As System.IO.StreamReader = New
System.IO.StreamReader(System.IO.Path.GetTempPath() & "Collections.txt")

Dim line As String, MyArray() As String, item As String, temp As String

Dim cnt As Int32

Dim lcol As Long, lrow As Long

lrow = 1

Do

line = sr.ReadLine()

MyArray = Split(line, "|", , CompareMethod.Text)

For lcol = 1 To UBound(MyArray)

Select Case lcol

Case Is = 2

objWS.Cells(lrow, lcol) =
Microsoft.VisualBasic.Left(MyArray(lcol), (Len(MyArray(lcol)) - 2)) & "." &
Microsoft.VisualBasic.Right(MyArray(lcol), 2)

Case Is = 11

If Len(MyArray(lcol)) > 0 Then

item = MyArray(lcol)

MyArray(lcol) = Microsoft.VisualBasic.Left(item, 2) & "/" &
Microsoft.VisualBasic.Mid(item, 3, 2) & "/" &
Microsoft.VisualBasic.Right(item, 4)

objWS.Cells(lrow, lcol).Value = MyArray(lcol)

End If

Case Is = 12

If Len(MyArray(lcol)) > 0 Then

item = MyArray(lcol)

MyArray(lcol) = Microsoft.VisualBasic.Left(item, 2) &
"/" & Microsoft.VisualBasic.Mid(item, 3, 2) & "/" &
Microsoft.VisualBasic.Right(item, 4)

objWS.Cells(lrow, lcol).Value = MyArray(lcol)

End If

Case Is = 15

If Len(MyArray(lcol)) > 0 Then

item = MyArray(lcol)

MyArray(lcol) = Microsoft.VisualBasic.Left(item, 2) &
"/" & Microsoft.VisualBasic.Mid(item, 3, 2) & "/" &
Microsoft.VisualBasic.Right(item, 4)

objWS.Cells(lrow, lcol).Value = MyArray(lcol)

End If

Case Else

objWS.Cells(lrow, lcol) = MyArray(lcol)

End Select

If lcol = 2 Then

Else

objWS.Cells(lrow, lcol) = MyArray(lcol)

End If

Next

lrow += 1

Loop Until line Is Nothing

Cursor.Current = Cursors.Default

sr.Close()

If System.IO.File.Exists(System.IO.Path.GetTempPath() & "Collections.txt")
Then

System.IO.File.Delete(System.IO.Path.GetTempPath() & "Collections.txt")

End If

objWS.Columns.AutoFit()

objWS = Nothing

objWB = Nothing

objExcel = Nothing
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

hibernated and menu 2
MSMAPI, or one of its dependencies, was not found 1
CDO exception with Web.Mail 4
Safe Printing 9
Framework Versions problem? 1
Start a process with a parameter in the file name? 4
Error while writing to the registry... OpenSubKey / SetValue 2
VS.Net Win Forms app - cannot access SQLServer with one computer 6

Top