Outline ·
[ Standard ] ·
Linear+
unable export data to excel, VB.Net 2010 & Excel 2013
TSemperror123
|
Apr 3 2016, 09:46 AM, updated 8y ago
|
Getting Started
|
hi all i am facing a problem which my data unable export out to excel, when i keep trying many solution in given in google, but no solution help. i hope here can help CODE Dim xlApp As Microsoft.Office.Interop.Excel.Application Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook Dim xlWorksheet As Microsoft.Office.Interop.Excel.Worksheets Dim misvalue As Object = System.Reflection.Missing.Value Dim i As Integer Dim j As Integer
xlApp = New Microsoft.Office.Interop.Excel.Application xlWorkBook = xlApp.Workbooks.Add(misvalue) xlWorksheet = CType(xlWorkBook.Sheets(), Excel.Worksheet) 'having error here
For i = 0 To OMtrackingDataGridView.ColumnCount - 1 For j = 0 To OMtrackingDataGridView.ColumnCount - 1 For k As Integer = 0 To OMtrackingDataGridView.Columns.Count xlWorksheet.cells(1, k) = OMtrackingDataGridView.Columns(k - 1).HeaderText xlWorksheet.cells(i + 2, j + 1) = OMtrackingDataGridView(j, i).Value.ToString() Next Next Next
xlWorksheet.SaveAs("C:\E-tracking.xlsx") xlWorkBook.Close() xlApp.Quit()
releaseObject(xlApp) releaseObject(xlWorkBook) releaseObject(xlWorksheet)
MsgBox("You Can find the file at C:\E-tracking.xlsx") Dim res As MsgBoxResult
res = MsgBox("Process completed, would you like to open file?", MsgBoxStyle.YesNo)
If (res = MsgBoxResult.Yes) Then Process.Start("C:\E-tracking.xlsx") End If
This post has been edited by emperror123: Apr 3 2016, 09:47 AM Attached thumbnail(s)
|
|
|
|
Eventless
|
Apr 3 2016, 02:24 PM
|
|
CODE xlWorksheet = CType(xlWorkBook.Sheets(), Excel.Worksheet) xlWorkBook.sheets isn't a worksheet. There is no way you could convert it into a worksheet. You would need to specify which sheet inside the workbook that you want to access and pass it into xlWorksheet. https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.sheets.aspxCODE xlWorksheet = xlWorkBook.Sheets(0) This should work assuming that that xlWorkBook.Sheets actually contain some worksheets.
|
|
|
|
TSemperror123
|
Apr 3 2016, 05:53 PM
|
Getting Started
|
Hi Eventless
i have tried the code u have send me and it is having the error as below.
Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
|
|
|
|
Eventless
|
Apr 3 2016, 09:01 PM
|
|
QUOTE(emperror123 @ Apr 3 2016, 09:46 AM) CODE Dim xlWorksheet As Microsoft.Office.Interop.Excel.Worksheets
xlWorksheet = CType(xlWorkBook.Sheets(), Excel.Worksheet) 'having error here
Noticed another thing wrong with your code, xlWorksheet shoud be worksheet, not worksheets. QUOTE(emperror123 @ Apr 3 2016, 05:53 PM) Hi Eventless i have tried the code u have send me and it is having the error as below. Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX)) My code is also probably broken. CODE xlWorksheet = xlWorkBook.Sheets(1) Workbooks start counting from 1 instead of 0. This could also be the case for sheets. https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbook.aspxThe other reason why it doesn't work is because you have not added any worksheet to sheets. CODE xlWorksheet = CType(xlWorkBook.Sheets(), Excel.Worksheet) I think I have found what the above code is supposed to look like. That version of the code does return a newly created and added sheet. https://msdn.microsoft.com/en-us/library/6fczc37s.aspx
|
|
|
|
TSemperror123
|
Apr 4 2016, 10:53 PM
|
Getting Started
|
hi eventless, i am still facing the same error.
|
|
|
|
Eventless
|
Apr 4 2016, 11:35 PM
|
|
QUOTE(emperror123 @ Apr 4 2016, 10:53 PM) hi eventless, i am still facing the same error. What does your code currently look like?
|
|
|
|
jurkflash
|
Apr 9 2016, 05:15 PM
|
Getting Started
|
just wondering 1) the machine that run the code got excel installed? 2) 64/32bit mixed up?
|
|
|
|
averagehumanbeing
|
Apr 16 2016, 09:58 AM
|
Getting Started
|
This post has been overwritten by a script.
This post has been edited by averagehumanbeing: Jul 21 2017, 07:02 AM
|
|
|
|
Dothan
|
Apr 23 2016, 07:38 PM
|
|
Stop using Interop to call EXCEL.EXE for exporting data to Excel sheet. It is ugly and error prone.
You may use EPPlus (http://epplus.codeplex.com/) which does not requires Office installation at all.
|
|
|
|