VB.net & excel 2000

  • Thread starter Thread starter Magnus
  • Start date Start date
M

Magnus

Im trying to program and create excel objects in vb.net, but I cannot seem
to get away from a consistent error, I have tried following some MS examples
to see if there was something wrong I had done, but those didn't work either
so I don't belive that there is any error with the code I have used so far.
The error I get is in my native language but translated it says something in
the order of ( " old format, invalid type library " ). I have added
reference to excel object library naturally, and I can see that visual
studio has automatically generated the interop assembly for excel.

This is one of the examples I have followed wich did not work:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q302094

Anyone know how I can successfully use excel objects ? Im using visual
studio 2003, and MS office 2000.

- Magnus
 
Here is some sample code:

I wrote this procedure to export a datatable to Excel.
It works pretty well, but has a minor problem with closing the instance of
Excel.
Let me know if it works for you and if you have solved the problem!

Public Sub Export2Excel(ByVal dt As DataTable, ByVal strPath As String,
ByRef pb As ProgressBar, ByVal MyOption As String)

'add reference to Microsoft Excel

Dim objXL As Excel.Application

Dim objWBS As Excel.Workbooks

Dim objWB As Excel.Workbook

Dim objWS As Excel.Worksheet

Dim mRow As DataRow

Dim colIndex As Integer

Dim rowIndex As Integer

Dim col As DataColumn

're-set pb so it displays correctly if called again

pb.Value = 0

pb.Minimum = 0

pb.Step = 1

Try

pb.Maximum = dt.Rows.Count

pb.Visible = True

If File.Exists(strPath) Then

File.Delete(strPath)

End If

Try

'get a running instance of Excel - this minimizes the number of instances of
Excel in memory!

objXL = CType(GetObject(, "Excel.Application"), Excel.Application)

Catch ex As Exception

'create a new instance of Excel if there isn't one running.

objXL = New Excel.Application

End Try

objWBS = objXL.Workbooks

objWB = objWBS.Add

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

'write column headers to Excel's first row from the dt.

For Each col In dt.Columns

colIndex += 1

objWS.Cells(1, colIndex) = col.ColumnName

Next col

If MyOption = "A" Then

'Bold and widen all the column headings

objWS.Range("A1:X1").Font.Bold = True

objWS.Columns.ColumnWidth = 10.5

'make some columns wider than the others

objWS.Range("A:A").ColumnWidth = 17

objWS.Range("C:C").ColumnWidth = 39

objWS.Range("E:F").ColumnWidth = 20

objWS.Range("L:O").ColumnWidth = 20

objWS.Range("T:T").ColumnWidth = 13

'rename the sheet to the file name w/o extension

objWS.Name = Mid(Path.GetFileName(strPath), 1,
Len(Path.GetFileName(strPath)) - 4)

ElseIf MyOption = "B" Then

'Bold and widen all the column headings

objWS.Range("A1:Q1").Font.Bold = True

objWS.Columns.ColumnWidth = 10.5

'make a column wider

objWS.Range("A:A").ColumnWidth = 17

'rename the sheet to B

objWS.Name = "B"

Else

'do nothing

End If

'write data starting on row with column headers because the first step is to
increment to the next row.

rowIndex = 1

For Each mRow In dt.Rows

pb.PerformStep()

rowIndex += 1

colIndex = 0

For Each col In dt.Columns

colIndex += 1

If col.ColumnName = "field1" Or col.ColumnName = "field2" Or col.ColumnName
= "field3" Then

'force to text data by adding a leading apostrophe

objWS.Cells(rowIndex, colIndex) = "'" & mRow(col.ColumnName).ToString()

Else

objWS.Cells(rowIndex, colIndex) = mRow(col.ColumnName).ToString()

End If

Next col

Next mRow

'make all rows same height

objWS.Range("A1:A" & CStr(dt.Rows.Count)).RowHeight = 12.75

objWB.SaveAs(strPath)

objWB.Close()

'minor problem - Excel is staying in Memory even after the Quit. It goes
away when the form is closed.

'if this procedure was local to the form that called it the problem might go
away. Untested.

'partially fixed by using a running instance instead of creating a new one
every time.

'Limited to 1 instance stuck in memory instead of unlimited number.

System.Runtime.InteropServices.Marshal.ReleaseComObject(objWS)

System.Runtime.InteropServices.Marshal.ReleaseComObject(objWB)

System.Runtime.InteropServices.Marshal.ReleaseComObject(objWBS)

objXL.Quit()

System.Runtime.InteropServices.Marshal.ReleaseComObject(objXL)

'this does not work to clear out the Excel instance from memory. Even though
it looks like "just the thing".

'http://www.dotnetinterop.com/faq/?q=OfficeCleanup

'http://support.microsoft.com/default.aspx?scid=kb;en-us;317109

'release other objects too! Still does not work. May be the remote call
issue.


Catch exc As Exception

Throw

Finally

objWS = Nothing

objWB = Nothing

objWBS = Nothing

objXL = Nothing

pb.Visible = False

'this does not work to clear out the Excel instance from memory either.

'GC.Collect()

'GC.WaitForPendingFinalizers()

End Try

End Sub
 
Hi Magnus,

On my system (VS2002, Office 2000), I've got various COMs

Microsoft Excel 9.0 Object Library
C:\Program Files\Microsoft Office\Office\Excel9.tlb
Interop.Excel in my project directory.

Microsoft Office 9.0 Object Library
C:\Program Files\Microsoft Office\Office\MSO9.tlb
Interop.Office in my project directory.

Microsoft Office 10.0 Object Library
C:\Program Files\Common Files\Microsoft Shared\Office\MSO.tlb
Microsoft.Office.Core in the GAC
C:\WINNT\assembly\GAC\Office\7.0.3300.0__b03f5f7f11d50a3a\Office.dll

And one .NET
office
C:\WINNT\Microsoft.NET\Framework\v1.0.3705\office.dll

Do you have a choice or just the one?

Regards,
Fergus
 
I used VS .NET 2002 and Excel 2002 to run the code in the KB article, using
the Office XP PIAs.

I expect that the problem is due to the libraries used by .NET 2003 with
Office 2000.
I'm not aware of PIAs for Office 2000. Do such critters exist?
 
Fergus Cooney said:
Hi Magnus,

On my system (VS2002, Office 2000), I've got various COMs

Microsoft Excel 9.0 Object Library
C:\Program Files\Microsoft Office\Office\Excel9.tlb
Interop.Excel in my project directory.

Microsoft Office 9.0 Object Library
C:\Program Files\Microsoft Office\Office\MSO9.tlb
Interop.Office in my project directory.

Microsoft Office 10.0 Object Library
C:\Program Files\Common Files\Microsoft Shared\Office\MSO.tlb
Microsoft.Office.Core in the GAC
C:\WINNT\assembly\GAC\Office\7.0.3300.0__b03f5f7f11d50a3a\Office.dll

And one .NET
office
C:\WINNT\Microsoft.NET\Framework\v1.0.3705\office.dll

Do you have a choice or just the one?

Regards,
Fergus
Click to expand...

I have these choices available to me in the COM reference window, for office
and excel.

Microsoft Excel 9.0 Object Library,
Microsoft Excel 5.0 Object Library,
Microsoft Office 10.0 Object Library,
Microsoft Office 9.0 Object Library

I have tried both 9.0 and 5.0 for excel, I am not aware of any possibilities
for excel in office, but tried adding reference to 10 object library without
any luck. I have also tried adding reference to office in .NET.

Took out relevant code from Joe Fallon's code, and this is what I have now
which also fails. I have added some (" '#Comment "), be sure to check it
out.

Hope someone can help.

<CODE>

Dim oExcel As Excel.Application
Dim oBooks As Excel.Workbooks
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Try

'get a running instance of Excel - this minimizes the number of instances
of Excel in memory!

oExcel = CType(GetObject(, "Excel.Application"), Excel.Application)

Catch ex As Exception

'create a new instance of Excel if there isn't one running.

oExcel = New Excel.Application ' #Comment: I get "new cannot be used on an
interface" on this one, how is this a interface ?

End Try

oBooks = oExcel.Workbooks

oBook = oBooks.Add

oSheet = CType(oBook.Worksheets(1), Excel.Worksheet) ' #Comment: Isn't this
sort of redundant, as the above CType statement ?


oSheet.Range("A1").Value = "Last Name"
oSheet.Range("B1").Value = "First Name"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A2").Value = "Doe"
oSheet.Range("B2").Value = "John"

'Save the Workbook and quit Excel.
oBook.SaveAs("c:\temp\" & "Book1.xls")
oSheet = Nothing
oBook = Nothing
oBooks = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()

</CODE>

<DEBUG>

'DefaultDomain': Loaded
'c:\winnt\microsoft.net\framework\v1.1.4322\mscorlib.dll', No symbols
loaded.
'ExcelApp': Loaded 'C:\Documents and Settings\magnus\Mine Dokumenter\Visual
Studio Projects\ExcelApp\bin\ExcelApp.exe', Symbols loaded.
'ExcelApp.exe': Loaded
'c:\winnt\assembly\gac\system.windows.forms\1.0.5000.0__b77a5c561934e089\sys
tem.windows.forms.dll', No symbols loaded.
'ExcelApp.exe': Loaded
'c:\winnt\assembly\gac\system\1.0.5000.0__b77a5c561934e089\system.dll', No
symbols loaded.
'ExcelApp.exe': Loaded
'c:\winnt\assembly\gac\system.drawing\1.0.5000.0__b03f5f7f11d50a3a\system.dr
awing.dll', No symbols loaded.
'ExcelApp.exe': Loaded
'c:\winnt\assembly\gac\microsoft.visualbasic\7.0.5000.0__b03f5f7f11d50a3a\mi
crosoft.visualbasic.dll', No symbols loaded.
'ExcelApp.exe': Loaded 'c:\documents and settings\magnus\mine
dokumenter\visual studio projects\excelapp\bin\interop.excel.dll', No
symbols loaded.
An unhandled exception of type 'System.NullReferenceException' occurred in
ExcelApp.exe

Additional information: Object reference not set to an instance of an
object.


Unhandled Exception: System.NullReferenceException: Object reference not set
to an instance of an object.
at ExcelApp.Form1.Import_Click(Object sender, EventArgs e) in
C:\Documents and Settings\magnus\Mine Dokumenter\Visual Studio
Projects\ExcelApp\Form1.vb:line 81
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.DebuggableCallback(IntPtr hWnd,
Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at
System.Windows.Forms.ComponentManager.System.Windows.Forms.UnsafeNativeMetho
ds+IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason,
Int32 pvLoopData)
at System.Windows.Forms.ThreadContext.RunMessageLoopInner(Int32 reason,
ApplicationContext context)
at System.Windows.Forms.ThreadContext.RunMessageLoop(Int32 reason,
ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at ExcelApp.Form1.Main() in C:\Documents and Settings\magnus\Mine
Dokumenter\Visual Studio Projects\ExcelApp\Form1.vb:line 1The program
'[1168] ExcelApp.exe' has exited with code 0 (0x0).

</DEBUG>
 
Fergus Cooney said:
Hi Magnus,

On my system (VS2002, Office 2000), I've got various COMs

Microsoft Excel 9.0 Object Library
C:\Program Files\Microsoft Office\Office\Excel9.tlb
Interop.Excel in my project directory.
Click to expand...

In fact, adding reference to this creates the PIA, and I can create excel
objects seemingly.

Dim oExcel As Excel.Application
oExcel.Visible = True

Will however fail with the message stated in my OP, "old format, or invalid
type library". and the same message I get if I try to create a workbook,
using the workbooks.add method.

<DEBUG>

'DefaultDomain': Loaded
'c:\winnt\microsoft.net\framework\v1.1.4322\mscorlib.dll', No symbols
loaded.
'ExcelApp': Loaded 'C:\Documents and Settings\magnus\Mine Dokumenter\Visual
Studio Projects\ExcelApp\bin\ExcelApp.exe', Symbols loaded.
'ExcelApp.exe': Loaded
'c:\winnt\assembly\gac\system.windows.forms\1.0.5000.0__b77a5c561934e089\sys
tem.windows.forms.dll', No symbols loaded.
'ExcelApp.exe': Loaded
'c:\winnt\assembly\gac\system\1.0.5000.0__b77a5c561934e089\system.dll', No
symbols loaded.
'ExcelApp.exe': Loaded
'c:\winnt\assembly\gac\system.drawing\1.0.5000.0__b03f5f7f11d50a3a\system.dr
awing.dll', No symbols loaded.
'ExcelApp.exe': Loaded
'c:\winnt\assembly\gac\microsoft.visualbasic\7.0.5000.0__b03f5f7f11d50a3a\mi
crosoft.visualbasic.dll', No symbols loaded.
'ExcelApp.exe': Loaded 'c:\documents and settings\magnus\mine
dokumenter\visual studio projects\excelapp\bin\excel.dll', No symbols
loaded.
An unhandled exception of type 'System.Runtime.InteropServices.COMException'
occurred in ExcelApp.exe

Additional information: Gammelt format eller ugyldig typebibliotek. //
translates: Old format or invalid type library


Unhandled Exception: System.Runtime.InteropServices.COMException
(0x80028018): Gammelt format eller ugyldig typebibliotek.
at Excel._Application.set_Visible(Boolean RHS)
at ExcelApp.Form1.Import_Click(Object sender, EventArgs e) in
C:\Documents and Settings\magnus\Mine Dokumenter\Visual Studio
Projects\ExcelApp\Form1.vb:line 81
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.DebuggableCallback(IntPtr hWnd,
Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at
System.Windows.Forms.ComponentManager.System.Windows.Forms.UnsafeNativeMetho
ds+IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason,
Int32 pvLoopData)
at System.Windows.Forms.ThreadContext.RunMessageLoopInner(Int32 reason,
ApplicationContext context)
at System.Windows.Forms.ThreadContext.RunMessageLoop(Int32 reason,
ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at ExcelApp.Form1.Main() in C:\Documents and Settings\magnus\Mine
Dokumenter\Visual Studio Projects\ExcelApp\Form1.vb:line 1The program
'[1652] ExcelApp.exe' has exited with code 0 (0x0).

</DEBUG>
 
Hi Magnus,

You may have a look below.
320369 BUG: "Old Format or Invalid Type Library" Error When Automating Excel
http://support.microsoft.com/?id=320369

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
From: "Magnus" <[email protected]>
Newsgroups: microsoft.public.dotnet.languages.vb
References: <[email protected]>
Click to expand...
Subject: Re: VB.net & excel 2000
Date: Tue, 30 Sep 2003 18:36:24 +0200
Lines: 86
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: 229.80-202-64.nextgentel.com
X-Original-NNTP-Posting-Host: 229.80-202-64.nextgentel.com
Message-ID: <[email protected]>
X-Trace: news.broadpark.no 1064939813 229.80-202-64.nextgentel.com (30 Sep 2003 18:36:53 +0200)
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!newsfeed.freenet.de!nntp.gblx.net!news.broadpark.no
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.vb:142586
X-Tomcat-NG: microsoft.public.dotnet.languages.vb


Fergus Cooney said:
Hi Magnus,

On my system (VS2002, Office 2000), I've got various COMs

Microsoft Excel 9.0 Object Library
C:\Program Files\Microsoft Office\Office\Excel9.tlb
Interop.Excel in my project directory.
Click to expand...

In fact, adding reference to this creates the PIA, and I can create excel
objects seemingly.

Dim oExcel As Excel.Application
oExcel.Visible = True

Will however fail with the message stated in my OP, "old format, or invalid
type library". and the same message I get if I try to create a workbook,
using the workbooks.add method.

<DEBUG>

'DefaultDomain': Loaded
'c:\winnt\microsoft.net\framework\v1.1.4322\mscorlib.dll', No symbols
loaded.
'ExcelApp': Loaded 'C:\Documents and Settings\magnus\Mine Dokumenter\Visual
Studio Projects\ExcelApp\bin\ExcelApp.exe', Symbols loaded.
'ExcelApp.exe': Loaded
'c:\winnt\assembly\gac\system.windows.forms\1.0.5000.0__b77a5c561934e089\sy s
tem.windows.forms.dll', No symbols loaded.
'ExcelApp.exe': Loaded
'c:\winnt\assembly\gac\system\1.0.5000.0__b77a5c561934e089\system.dll', No
symbols loaded.
'ExcelApp.exe': Loaded
'c:\winnt\assembly\gac\system.drawing\1.0.5000.0__b03f5f7f11d50a3a\system.d r
awing.dll', No symbols loaded.
'ExcelApp.exe': Loaded
'c:\winnt\assembly\gac\microsoft.visualbasic\7.0.5000.0__b03f5f7f11d50a3a\m i
crosoft.visualbasic.dll', No symbols loaded.
'ExcelApp.exe': Loaded 'c:\documents and settings\magnus\mine
dokumenter\visual studio projects\excelapp\bin\excel.dll', No symbols
loaded.
An unhandled exception of type 'System.Runtime.InteropServices.COMException'
occurred in ExcelApp.exe

Additional information: Gammelt format eller ugyldig typebibliotek. //
translates: Old format or invalid type library


Unhandled Exception: System.Runtime.InteropServices.COMException
(0x80028018): Gammelt format eller ugyldig typebibliotek.
at Excel._Application.set_Visible(Boolean RHS)
at ExcelApp.Form1.Import_Click(Object sender, EventArgs e) in
C:\Documents and Settings\magnus\Mine Dokumenter\Visual Studio
Projects\ExcelApp\Form1.vb:line 81
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.DebuggableCallback(IntPtr hWnd,
Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at
System.Windows.Forms.ComponentManager.System.Windows.Forms.UnsafeNativeMeth o
ds+IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason,
Int32 pvLoopData)
at System.Windows.Forms.ThreadContext.RunMessageLoopInner(Int32 reason,
ApplicationContext context)
at System.Windows.Forms.ThreadContext.RunMessageLoop(Int32 reason,
ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at ExcelApp.Form1.Main() in C:\Documents and Settings\magnus\Mine
Dokumenter\Visual Studio Projects\ExcelApp\Form1.vb:line 1The program
'[1652] ExcelApp.exe' has exited with code 0 (0x0).

</DEBUG>
Click to expand...
 
Peter Huang said:
Hi Magnus,

You may have a look below.
320369 BUG: "Old Format or Invalid Type Library" Error When Automating Excel
http://support.microsoft.com/?id=320369

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Click to expand...

I am actually not using excel 2002, but certainly applies to excel 2000
also.. it works, thanks !

- Magnus
 
Back
Top