Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

 unable export data to excel, VB.Net 2010 & Excel 2013

views
     
TSemperror123
post Apr 3 2016, 09:46 AM, updated 8y ago

Getting Started
**
Junior Member
216 posts

Joined: Apr 2010


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)
Attached Image
Eventless
post Apr 3 2016, 02:24 PM

Look at all my stars!!
*******
Senior Member
2,641 posts

Joined: Jan 2003
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.aspx

CODE
      xlWorksheet = xlWorkBook.Sheets(0)

This should work assuming that that xlWorkBook.Sheets actually contain some worksheets.
TSemperror123
post Apr 3 2016, 05:53 PM

Getting Started
**
Junior Member
216 posts

Joined: Apr 2010


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
post Apr 3 2016, 09:01 PM

Look at all my stars!!
*******
Senior Member
2,641 posts

Joined: Jan 2003
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.aspx

The 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
post Apr 4 2016, 10:53 PM

Getting Started
**
Junior Member
216 posts

Joined: Apr 2010


hi eventless,
i am still facing the same error.
Eventless
post Apr 4 2016, 11:35 PM

Look at all my stars!!
*******
Senior Member
2,641 posts

Joined: Jan 2003
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
post Apr 9 2016, 05:15 PM

Getting Started
**
Junior Member
95 posts

Joined: Sep 2010
just wondering
1) the machine that run the code got excel installed?
2) 64/32bit mixed up?
averagehumanbeing
post Apr 16 2016, 09:58 AM

Getting Started
**
Junior Member
51 posts

Joined: Apr 2015
From: Utah, US of A
This post has been overwritten by a script.

This post has been edited by averagehumanbeing: Jul 21 2017, 07:02 AM
Dothan
post Apr 23 2016, 07:38 PM

Dingle Berries
******
Senior Member
1,020 posts

Joined: Jan 2003


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.

 

Change to:
| Lo-Fi Version
0.0147sec    0.68    6 queries    GZIP Disabled
Time is now: 29th March 2024 - 10:18 AM