question

faraz81 avatar image
faraz81 asked

VB.net application creating Excel file fails after Office 2013 upgrade

First the error: System.Runtime.InteropServices.COMException (0x8002000B): Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX)) at Microsoft.Office.Interop.Excel.Sheets.get__Default(Object Index) at QC_Audit_Results_generator.AuditResults.ExecuteBtn_Click(Object sender, EventArgs e) at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) Below is the code for my vb.net application that was working fine until Office 2013 was installed on all computers in our department. This runs a couple of SQL queries and puts the results in 2 separate worksheets in the excel workbook. I added the reference to the version 15 .0 of the Microsoft.Office.Interop.Excel but then it threw different errors that “Application”/"Workbook"/"Worksheet" is ambiguous in the namespace 'microsoft.office.interop.excel'. To correct that error, someone suggested that I need to remove the reference to "Microsoft.Office.Interop.Excel" and add "Microsoft Excel 15.0 Object Library" under COM in add reference window. But when I try that it won't remove the "Microsoft.Office.Interop.Excel" reference. Every time I uncheck it and remove from reference list and go to check "Microsoft Excel 15.0 Object Library" under COM, it throws following error and the reference is checked again. "a reference to microsoft excel 15.0 object library could not be added. you must remove reference Microsoft.Office.Interop.Excel" **My Code:** Private Sub ExecuteBtn_Click(sender As Object, e As EventArgs) Handles ExecuteBtn.Click Dim MyConnection As New SqlClient.SqlConnection("server=VMBKSA69901MAL; database=" + Databases.Text + "; Integrated Security=True") Dim FileSaveLoc As String = CStr(FolderPath.Text) ' Location where Excel File will be saved ' Create Excel Workbook Dim oExcel As New Excel.Application Dim oWorkBook As Excel.Workbook Dim oWorkSheetAudit As Excel.Worksheet Dim oWorkSheetPermissions As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value oExcel = CreateObject("Excel.Application") oWorkBook = oExcel.Workbooks.Add(misValue) oWorkSheetAudit = oWorkBook.Worksheets.Add() oWorkSheetPermissions = oWorkBook.Worksheets.Add() oWorkSheetAudit.Name = "Audit" oWorkSheetPermissions.Name = "Permissions" 'Delete unused sheets in excel file ' oWorkBook.Sheets("sheet1").Delete() ' oWorkBook.Sheets("sheet2").Delete() ' oWorkBook.Sheets("sheet3").Delete() ' Process Audit .sql file Dim AuditFile As String = CStr(AuditQryFileBrowser.Text) ' Grab Audit Query File Path from text box Dim SQLFileAudit As FileInfo = New FileInfo(AuditFile) ' Pass the audit file path here Dim AuditQryTxt As String = SQLFileAudit.OpenText.ReadToEnd ' Read the audit .sql file entirely Dim SqlCmd As New SqlClient.SqlCommand(AuditQryTxt) SqlCmd.Connection = MyConnection SqlCmd.CommandType = CommandType.Text Dim AuditDS As New DataSet() Dim AdapterAudit As New SqlClient.SqlDataAdapter(SqlCmd) AdapterAudit.SelectCommand.CommandTimeout = 300 AdapterAudit.Fill(AuditDS) 'MyConnection.Close() Dim i, j As Integer For i = 0 To AuditDS.Tables(0).Columns.Count - 1 oWorkSheetAudit.Cells(1, i + 1) = AuditDS.Tables(0).Columns(i).ToString() Next For i = 0 To AuditDS.Tables(0).Rows.Count - 1 For j = 0 To AuditDS.Tables(0).Columns.Count - 1 oWorkSheetAudit.Cells(i + 2, j + 1) = _ AuditDS.Tables(0).Rows(i).Item(j) Next Next ' Process Permissions .sql file Dim PermFile As String = CStr(PermQryFileBrowser.Text) ' Grab Permissions Query File Path from text box Dim SQLFilePerm As FileInfo = New FileInfo(PermFile) ' Pass the permissions file path here Dim PermQryTxt As String = SQLFilePerm.OpenText.ReadToEnd ' Read the permissions .sql file entirely Dim SqlCmdPerm As New SqlClient.SqlCommand(PermQryTxt) SqlCmdPerm.Connection = MyConnection SqlCmdPerm.CommandType = CommandType.Text Dim PermDS As New DataSet() Dim AdapterPerm As New SqlClient.SqlDataAdapter(SqlCmdPerm) AdapterPerm.SelectCommand.CommandTimeout = 300 AdapterPerm.Fill(PermDS) MyConnection.Close() Dim a, b As Integer For a = 0 To PermDS.Tables(0).Columns.Count - 1 oWorkSheetPermissions.Cells(1, a + 1) = PermDS.Tables(0).Columns(a).ToString() Next For a = 0 To PermDS.Tables(0).Rows.Count - 1 For b = 0 To PermDS.Tables(0).Columns.Count - 1 oWorkSheetPermissions.Cells(a + 2, b + 1) = _ PermDS.Tables(0).Rows(a).Item(b) Next Next oExcel.Columns.AutoFit() If FileSaveLoc <> "" Then If IO.File.Exists(FileSaveLoc + "\" + Databases.Text + "_" + ReleaseTxtBox.Text + "_Audit_Results.xlsx") Then If MsgBox(Databases.Text + "_" + ReleaseTxtBox.Text + "_Audit_Results.xlsx already exists. Replace?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then IO.File.Delete(FileSaveLoc + "\" + Databases.Text + "_" + ReleaseTxtBox.Text + "_Audit_Results.xlsx") oWorkBook.SaveAs(FileSaveLoc + "\" + Databases.Text + "_" + ReleaseTxtBox.Text + "_Audit_Results.xlsx") oWorkBook.Close() oExcel.Quit() MsgBox("Saved To " + FileSaveLoc) Else oWorkBook.Close() oExcel.Quit() End If Else oWorkBook.SaveAs(FileSaveLoc + "\" + Databases.Text + "_" + ReleaseTxtBox.Text + "_Audit_Results.xlsx") oWorkBook.Close() oExcel.Quit() MsgBox("Saved To " + FileSaveLoc) End If Else MsgBox("Save Excel File To Path Cannot Be Empty") End If releaseObject(oExcel) releaseObject(oWorkBook) releaseObject(oWorkSheetPermissions) releaseObject(oWorkSheetAudit) End Sub
vb.net
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.