(RESOLVED)accessing excel 2003 from visual studio 2005 express

[cassie1]

can someone assist i am trying to access excel 2003 from Visual Studio Express 2005 express i am using the code below which opens excel but then gives me an error of com exception was unhandled and highlights the code line " owb= oxl.workbooks.add", any ideas.

Public Class Form1
 
    Public Sub New()
 
        ' This call is required by the Windows Form Designer.
        InitializeComponent()
 
        ' Add any initialization after the InitializeComponent() call.
 
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
        Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Dim oRng As Excel.Range
 
        ' Start Excel and get Application object.
        oXL = CreateObject("Excel.Application")
        oXL.Visible = True
 
        ' Get a new workbook.
        oWB = oXL.Workbooks.Add
        oSheet = oWB.ActiveSheet
 
        ' Make sure Excel is visible and give the user control
        ' of Excel's lifetime.
        oXL.Visible = True
        oXL.UserControl = True
 
        ' Make sure that you release object references.
        oRng = Nothing
        oSheet = Nothing
        oWB = Nothing
        oXL.Quit()
        oXL = Nothing
 
        Exit Sub
Err_Handler:
        MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
    End Sub
 
End Class

Edit: Added [vbcode][/vbcode] tags for clarity. - Hack

[Hack]

Moved to .NET

[SonicBoomAu]

Have you added a reference to Excel?

This site explains how to automate excel utilising .Net

http://support.microsoft.com/kb/301982/EN-US/

Hope this helps.

[cassie1]

Hi sonic Boom
yes ive tried that but it doesnt work i keep getting (com exception was unhandled ) and it highlights the code "owb=oxl.workbooks.add", but it does open excel. Any suggestions

[Asgorath]

Maybe your excel application is not properly created using the CreateObject("Excel.Application") and use this

Dim oXL As New Excel.Application

Regards
Jorge

[cassie1]

Hi Jorge,tried that but gave me (New cannot be used on an interface)

Cassie 1

[SonicBoomAu]

This is how I normally connect to an Excel spreadsheet

' Start Adding Details to a New Excel Workbook
        Dim objApp As Excel.Application
        Dim objBook As Excel._Workbook
        Dim objBooks As Excel.Workbooks
        Dim objSheets As Excel.Sheets
        Dim objSheet As Excel._Worksheet
        Dim range As Excel.Range
        Dim PR As DialogResult
Try
' Create a new instance of Excel and start a new workbook.
            objApp = New Excel.Application
            objBooks = objApp.Workbooks
            objBook = objBooks.Add
            objSheets = objBook.Worksheets
            objSheet = objSheets(1)
 
' Your code goes here
 
' Exit out of Excel
            objsheets = nothing
            objSheet = Nothing
            objBook = Nothing
            objBooks = Nothing
            objApp.Quit()
            objApp = Nothing
            
Catch ex As Exception
            ' Inform the user of the error.
            MessageBox.Show("An error occured! Error: " & ex.Message, _
            "Title", MessageBoxButtons.OK, MessageBoxIcon.Error)
 
            ' Exit out of Excel
            objsheets = nothing
            objSheet = Nothing
            objBook = Nothing
            objBooks = Nothing
            objApp.Quit()
            objApp = Nothing
 
        End Try

Hope this helps