locked
Open an Excel File using VB.Net RRS feed

  • 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

    Monday, May 20, 2013 11:25 PM

Answers

  • 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.

    Tuesday, May 21, 2013 1:09 AM
  • 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)


    Tuesday, May 21, 2013 4:58 PM

All replies

  • 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.

    Monday, May 20, 2013 11:38 PM
  • 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.

    Tuesday, May 21, 2013 1:09 AM
  • 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

    Tuesday, May 21, 2013 3:45 PM
  • 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)


    Tuesday, May 21, 2013 4:58 PM
  • 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.

    Saturday, May 25, 2013 3:27 AM
  • Después de ver miles de foros, excelente! si funciona este ejemplo.
    Saturday, October 22, 2016 12:30 AM
  • I used Spire.XLS to read Excel documents

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

    Monday, October 24, 2016 2:04 AM