Community discussion forum

Linking to Excel from VB.Net

• • Reply

  2 years ago

  by ErwinB

  ErwinB

  Amsterdam, Netherlands

  Joined 4 years ago

  Hi,

  I want to use Excel as a reporting tool for a little application I'm developing.  I've done this a zillion times with VB6 and it works like a charm: use a VB front-end to get input from the user and pull the data from a database, create a link to Excel and post and format the data in the spreadsheet.

  However, when I try this in combination with VB.Net (version 2005, Express edition), I can't get it to work.  No matter what I try, I keep on getting a COMException error when opening a workbook.  I've been searching the web for a solution, but have not found anything but a few stories of people with a similar problem, all concluding to go back to VB6...

  Now I don't want to give up that easily, so am hoping that somebody in this forum knows how to fix this.

  I'm using Excel 2003, have installed the relevant InterOp stuff for Office 2003 from the Microsoft site, and created a reference to the Microsoft Excel 11.0 Object Library in my project.  My code looks like this:

  Imports Microsoft.Office.Interop

  Private Sub btnCreateReportDeck_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateReportDeck.Click

     Dim oExcel As Excel.Application
  
     Dim i As Short

     'Create an instance of Excel
  
     oExcel = New Excel.Application

     oExcel.Visible =

  True
     
  oExcel.Workbooks.Open("C:\Test.xls")
  
     <more code follows but has been left out to keep things readable>
  
  

  End Sub

  I've already tried alternatives like

  - Dim oExcel As New Excel.Application
  - oExcel = CreateObject("Excel.Application")
  - Defining separate objects for the Excel-object Workbook, Workbooks, etc.
  - Adding an object variable of type Missing for all other optional variables in the open-method
  - ...
  
  
  
  

  No matter what, the oExcel.Workbooks.Open line generates the COMException error.

  Anyone a good suggestion ?

  Thanks in advance,

  Erwin

  Report abuse
• • Reply

  2 years ago

  by Zorro_ot

  Oliver Tell

  Hamburg, Germany

  Joined 3 years ago

  The problem seems to be the Import statement and the Dim statement. Please try to use something like this instead:

  Imports Excel = Microsoft.Office.Interop.Excel
  ...
  Private Sub Whatever...
  ...
  Dim oExcel As Excel.Application
  oExcel = New Excel.Application
  oExcel.Visible = True
  ...
  End Sub

  Report abuse
• • Reply

  2 years ago

  by ErwinB

  ErwinB

  Amsterdam, Netherlands

  Joined 4 years ago

  Zorro,

  Thanks for the suggestion.  Unfortunately I already tried that approach, but the problem as described in my post still was happening.

  I have the feeling there is a problem with the Interop services, but don't know what to do to fix it.  I'm starting to wonder if it isn't better to stick to VB6 for applications that do a lot with COM-objects...

  Regards,

  Erwin

  Report abuse
• • Reply

  2 years ago

  by Nimpo

  Mitch Dusina

  Littleton (Denver), Colorado, USA, United States

  Joined 5 years ago
  I went hunting through some code I wrote a long while ago which interfaced with Excel, here it is:
  
  'Excel Objects
Dim exApp As New Excel.Application
Application.DoEvents()
Dim exWB As Excel.Workbook = exApp.Workbooks.Open(strPath)
Application.DoEvents()
Dim exWS As Excel.Worksheet = DirectCast(exWB.Worksheets(1), Excel.Worksheet)
Application.DoEvents()
exApp.Visible = False
exApp.DisplayAlerts = False








  
  I can't remember if the DoEvents were required to prevent errors like yours or if they were a result of my inexperience =)
  
  PS: This was in Visual Studio '03, but give it a shot.
  
  
  
  
  
  
  
  
  Report abuse
• • Reply

  2 years ago

  by ErwinB

  ErwinB

  Amsterdam, Netherlands

  Joined 4 years ago

  Thanks Nimpo.

  Unfortunately it doesn't change things. :-(

  I don't know what's wrong, but I have the same problem on a Win XP machine with Office XP, and on a Vista machine with Office 2007.  In a few articles during my search, I read something about forcing English-US settings (or something) that fixed the problem (in an early .Net version), but that's too much of a stretch I find.

  I'll let .Net for what it is, and use VB6 for this.  It's too much of a pain to get it to work; I lost too much time already.  Maybe I'll give it another try in the future when Office is no longer COM based.

  Erwin

  Report abuse