Open an Excel File using VB.Net

Question

I'm trying to open an Excel workbook from VB.Net. I'm using VS 2005. The procedure that is trying to open the file looks like this:

    Public Sub DisplayExcelFile()

        '    ---------------------------------------------------------------------------------------
        '       Procedure:  DisplayExcelFile()
        '       DateTime  : 5/20/13
        '       Author:     snyderg()
        '       Purpose   : This routine displays the Excel spreadsheet at location strPath. 
        '                   To call:    Session("strExcelFilePath") = ExcelFilePath
        '                               Call DisplayExcelFile("Path")
        '    ---------------------------------------------------------------------------------------

        Dim xlsApp As Excel.Application = Nothing
        Dim xlsWorkBooks As Excel.Workbooks = Nothing
        Dim xlsWB As Excel.Workbook = Nothing

        Try

            xlsApp = New Excel.Application
            xlsApp.Visible = True
            xlsWorkBooks = xlsApp.Workbooks
            xlsWB = xlsWorkbooks.Open(Session("strExcelFilePath"))

        Catch ex As Exception

        Finally

            xlsWB.Close()
            xlsWB = Nothing
            xlsApp.Quit()
            xlsApp = Nothing

        End Try

    End Sub

I begin the class with the statements:

Imports Excel = Microsoft.Office.Interop.Exel

Imports Microsoft.Office

When I call the procedure, I get "Incorrect Syntax Near 'Microsoft'." I have commented the entire procedure except for the first Dim statement:

"Dim xlsApp = New.Excel.Application"

but I still get the error. The project builds without error.

Can anyone suggest why I get this syntax error and how to correct it?

---

Retred Air Force

Answer 1

That seems like an error in a macro that is starting when Excel loads.  I would look at your Excel setup rather than within VB.

You should not be using Try/Catch while you are still debugging - you want to see what your errors are so that you can resolve them. Leave the Try/Catch in only if there is a particular error that you cannot handle otherwise.

Answer 2

First place the import statements at the top of the form or code module you are working in

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office

Code to open Excel (read the MessageBox within the code)

Public Sub OpenExcelDemo(ByVal FileName As String, ByVal SheetName As String)
    If IO.File.Exists(FileName) Then
        Dim Proceed As Boolean = False
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim xlWorkSheets As Excel.Sheets = Nothing
        Dim xlCells As Excel.Range = Nothing
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)
        xlApp.Visible = True
        xlWorkSheets = xlWorkBook.Sheets
        For x As Integer = 1 To xlWorkSheets.Count
            xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
            If xlWorkSheet.Name = SheetName Then
                Console.WriteLine(SheetName)
                Proceed = True
                Exit For
            End If
            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
        Next
        If Proceed Then
            xlWorkSheet.Activate()
            MessageBox.Show("File is open, if you close Excel just opened outside of this program we will crash-n-burn.")
        Else
            MessageBox.Show(SheetName & " not found.")
        End If
        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()
        ReleaseComObject(xlCells)
        ReleaseComObject(xlWorkSheets)
        ReleaseComObject(xlWorkSheet)
        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xlApp)
    Else
        MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
    End If
End Sub
Public Sub ReleaseComObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    End Try
End Sub

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

Answer 3

Kevin -

Thanks for the response and well written example. Based on your advice, I opted to start out slowly by first adding the following:

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office

at the beginning of the page with my other Imports statements (actually already there). I then created a new procedure with only 2 lines of your code in it:

Public Sub DisplayExcelFile()


        Dim Proceed As Boolean = False
        Dim xlApp As Excel.Application = Nothing
        

End Sub

When I rebuilt, opened the project, and clicked the button that executes the new procedure, I once again got the message "Incorrect syntax near 'Microsoft'" Rebuilding the page and opening the project without calling the procedure DisplayExcelFile (i.e., with the Imports statements in place) did not result in the error appearing. It does not appear to like me declaring the variable xlApp as an Excel.Application.

By the way, my application is a .net web app and not a .net windows app. Also, this all began because one of my users had IE10 installed then uninstalled with IE9 reinstalled. After this action, she could no longer open an existing Excel file located in a server folder using the Response.Redirect statement. I'm attempting to investigate another means of opening the Excel file besides using Response.Redirect (which works for everyone else).

---

Retred Air Force

Answer 4

Just an FYI, since you mentioned that you are working on an ASP.NET app:

http://blogs.msdn.com/b/chayu/archive/2011/11/10/support-policy-considerations-for-server-side-automation-of-office.aspx

You may also want to try posting to one of the ASP.NET forums for assistance:

http://forums.asp.net/

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 5

So, you want to open an Excel  file?  Take a look at this.

http://vb.net-informations.com/excel-2007/vb.net_excel_2007_open_file.htm

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answer 6

Después de ver miles de foros, excelente! si funciona este ejemplo.

Answer 7

I used Spire.XLS to read Excel documents

Dim wb As New Workbook()
wb.LoadFromFile("sample.xlsx")