0x80020003 DISP_E_MEMBERNOTFOUND When working with Excel Spreadsheet

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi,
I have a table in an Access database which I loop through in order to delete
columns in an Excel Spreadsheeet. The issue I have is that the first time
this subroutine is run, I get the error "0x80020003 DISP_E_MEMBERNOTFOUND"
on the line: rg.Delete() . If the user clicks the button to run the
function a 2nd time it works fine. The error only occurs on the first
attempt to run it. I have a reference to the Excel 11.0 Object Library.
Any suggestions?

Thanks in advance

(Sub below)


Public Sub TrimExcelColumns(ByVal SheetLocation As String)

Dim xlApp As Excel.Application

Dim xlBook As Excel.Workbook

Dim xlSheet As Excel.Worksheet

xlApp = CreateObject("Excel.Application")

xlBook = xlApp.Workbooks.Open(SheetLocation)

xlSheet = xlBook.Worksheets(1)

Dim objConnection As OleDb.OleDbConnection

objConnection = New OleDb.OleDbConnection(My.Settings.myQConnectionString)

objConnection.Open() 'open the connection

'tblColumnsToDelete contains columns to remove - work from right to left

Dim strSQL As String = "SELECT ColumnName, ColumnNo FROM tblClmnsToDel
"

strSQL &= "ORDER BY ColumnNo DESC"

'Create the Command object

Dim objCommand As OleDb.OleDbCommand

objCommand = New OleDb.OleDbCommand(strSQL, objConnection)

' Set an OleDbDataReader to the command's results

Dim objDataReader As OleDb.OleDbDataReader

objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)

Dim rg As Excel.Range

Dim colName As String

While objDataReader.Read()

colName = objDataReader("ColumnName")

rg = xlSheet.Columns(colName)

rg.Select()

rg.Delete()

End While

objConnection.Close()

xlBook.Save()

xlApp.Quit()

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)

xlSheet = Nothing

xlBook = Nothing

xlApp = Nothing

End Sub
 
Back
Top