Click here to Skip to main content
15,936,709 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I'm trying to create sheet4 with name as "AutoIntegration" but it showing error:" DISP_E_BADINDEX", its working fine upto sheet3, but failing at sheet4

Please help


If dlgSave.ShowDialog = Windows.Forms.DialogResult.OK Then
Try

xlApp = New Microsoft.Office.Interop.Excel.Application
xlBook = xlApp.Workbooks.Add(oValue)

'############### EXPORT TO SHEET1 ##########################

Dim xlRow As Long = 2
Dim xlCol As Short = 1
xlSheet = xlBook.Worksheets("Sheet1")
xlSheet.Name = "General"

For Each col As DataColumn In dtgl.Columns
xlSheet.Cells(1, xlCol) = col.ColumnName
xlCol += 1
Next
For Each row As DataRow In dtgl.Rows
xlCol = 1
For Each col As DataColumn In dtgl.Columns
xlSheet.Cells(xlRow, xlCol) = row(xlCol - 1)
xlCol += 1
Next
xlRow += 1
Next
'############### EXPORT TO SHEET2 ##########################
Dim xlRow2 As Long = 2
Dim xlCol2 As Short = 1
xlSheet = xlBook.Worksheets("Sheet2")
xlSheet.Name = "Transport"

For Each col As DataColumn In dttt.Columns
xlSheet.Cells(1, xlCol2) = col.ColumnName
xlCol2 += 1
Next

For Each row As DataRow In dttt.Rows
xlCol2 = 1
For Each col As DataColumn In dttt.Columns
xlSheet.Cells(xlRow2, xlCol2) = row(xlCol2 - 1)
xlCol2 += 1
Next
xlRow2 += 1
Next

'############### EXPORT TO SHEET3 ##########################
Dim xlRow3 As Long = 2
Dim xlCol3 As Short = 1
xlSheet = xlBook.Worksheets("Sheet3")
xlSheet.Name = "Radio"

For Each col As DataColumn In dtr.Columns
xlSheet.Cells(1, xlCol3) = col.ColumnName
xlCol3 += 1
Next

For Each row As DataRow In dtr.Rows
xlCol3 = 1
For Each col As DataColumn In dtr.Columns
xlSheet.Cells(xlRow3, xlCol3) = row(xlCol3 - 1)
xlCol3 += 1
Next
xlRow3 += 1
Next

'############### EXPORT TO SHEET4 ##########################

Dim xlRow4 As Long = 2
Dim xlCol4 As Short = 1
xlSheet = xlBook.Worksheets("Sheet4")
xlSheet.Name = "AutoIntegrate"

For Each col As DataColumn In dtau.Columns
xlSheet.Cells(1, xlCol4) = col.ColumnName
xlCol4 += 1
Next

For Each row As DataRow In dtau.Rows
xlCol4 = 1
For Each col As DataColumn In dtau.Columns
xlSheet.Cells(xlRow4, xlCol4) = row(xlCol4 - 1)
xlCol4 += 1
Next
xlRow4 += 1
Next
Posted
Updated 24-Sep-22 4:26am
Comments
DamithSL 14-Jan-15 8:13am    
try by SheetsInNewWorkbook as 4

xlApp = New Microsoft.Office.Interop.Excel.Application
xlApp.SheetsInNewWorkbook = 4
Member 11338695 14-Jan-15 8:35am    
It's working , thank you very much :)
ZurdoDev 14-Jan-15 8:30am    
The error means there aren't 4 sheets.

try with below code
VB
xlApp = New Microsoft.Office.Interop.Excel.Application
Dim iSheetsPerBook As Integer
iSheetsPerBook = xlApp.SheetsInNewWorkbook
xlApp.SheetsInNewWorkbook = 4
xlBook = xlApp.Workbooks.Add
xlApp.SheetsInNewWorkbook = iSheetsPerBook
 
Share this answer
 
You can use a generic solution by using open XML instead of using interop library.
http://exportaspnetlistdatatoexcelworkbook.blogspot.com/
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900