Answered by:
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
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.
- Proposed as answer by Ed Price - MSFTMicrosoft employee Wednesday, May 22, 2013 6:12 AM
- Marked as answer by Youen Zen Wednesday, June 5, 2013 6:18 AM
Tuesday, May 21, 2013 1:09 AM -
Just an FYI, since you mentioned that you are working on an ASP.NET app:
You may also want to try posting to one of the ASP.NET forums for assistance:
Paul ~~~~ Microsoft MVP (Visual Basic)
- Edited by Paul P Clement IV Tuesday, May 21, 2013 4:59 PM
- Proposed as answer by Ed Price - MSFTMicrosoft employee Wednesday, May 22, 2013 6:12 AM
- Marked as answer by Youen Zen Wednesday, June 5, 2013 6:18 AM
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.
- Proposed as answer by Ed Price - MSFTMicrosoft employee Wednesday, May 22, 2013 6:12 AM
- Marked as answer by Youen Zen Wednesday, June 5, 2013 6:18 AM
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:
You may also want to try posting to one of the ASP.NET forums for assistance:
Paul ~~~~ Microsoft MVP (Visual Basic)
- Edited by Paul P Clement IV Tuesday, May 21, 2013 4:59 PM
- Proposed as answer by Ed Price - MSFTMicrosoft employee Wednesday, May 22, 2013 6:12 AM
- Marked as answer by Youen Zen Wednesday, June 5, 2013 6:18 AM
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
-
Monday, October 24, 2016 2:04 AM