473,394 Members | 1,699 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,394 software developers and data experts.

Adding data to Excel from VB.NET

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(Strin g 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(MouseEventAr gs 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(M essage& 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


Nov 19 '05 #1
1 15849
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" <bc******@masongeneral.com>
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: <#r**************@TK2MSFTNGP09.phx.gbl>
Newsgroups: microsoft.public.dotnet.languages.vb
NNTP-Posting-Host: list.masongeneral.com 66.119.204.11
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.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 theapp 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(Strin g 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(MouseEventAr gs 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(M essage& 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



Nov 20 '05 #2

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

Similar topics

4
by: sberk44 | last post by:
I am trying to take data submited from a web form and convert that to an excel spreadsheet. Each new form submitted i would line a new row or column in that same spreadsheet. Eventually I would...
5
by: DavidGeorge | last post by:
I have made a fundamental error in design and ignored security until the end of the project. This is largely because the whole process was a steep learning curve (helped enormously by this group)...
5
by: Przemek Wrzesinski | last post by:
Hi, I'm trying to add additional column using 'alter table' command via OleDB to Excel workbook (one sheet called queExportBOND): Dim strConn As String strConn =...
4
by: e.h.doxtator | last post by:
All I'm a Python newbie, and I'm just getting to the wonders of COM programming. I am trying to programmatically do the following: 1. Activate Excel 2. Add a Workbook 3. Add a Worksheet...
3
by: saragoesnuts | last post by:
I have a program that automates to Excel with information in tables. I want to add charts to the excel file (pie charts, bar charts, etc). Is this possible?
5
by: nkechifesie | last post by:
Hi, I have written a VBA program that runs on Excel and puts data on the excel sheet. This runs everyday. I want to be adding the dates to the files, this date is gotten from the excel sheet that...
1
by: rishiyo | last post by:
Good ones!! I want to retrieve and add datas with excel. I've tried one by adding two textboxes and two command buttons. THE code is: 'do declare these variables you need to add a reference'to...
1
by: rameshmandapati | last post by:
hi i have a problem with Excel. iam adding data to a cell in Excel using java script var excel = new ActiveXObject ("Excel.Application"); but i want image also i search in google i...
3
by: chirag1989 | last post by:
how can i add data to database using excel through asp actual i wanted to use datagrid but as its not supported by asp so i want to try dat by excel can any one just provide me a short code...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
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
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
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
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
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

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.