Inserting new row in Excel Worksheet

[pfildes]

I have the following code for processing the contents of an Excel worksheet, however I'm having a little difficulty inserting a new row .

  Dim myExcel As New Excel.Application
  Dim myWorkBook As Excel.Workbook
  Dim myWorkSheet As Excel.Worksheet
  Dim XLData As String
  Dim IRow As Integer

  myWorkBook = myExcel.Workbooks.Open(myXLSFile)
  myWorkSheet = myWorkBook.Worksheets.Item(1)
        
  IRow = 1
  XLData = CType(myWorkSheet.Cells(IRow, 1), Excel.Range).Text
  Do While (XLData <> "A New Record")
    IRow = IRow + 1
    XLData = CType(myWorkSheet.Cells(IRow, 1), Excel.Range).Text
  Loop

  'Add new record just prior to row with "A New Record"
  myWorkSheet.Rows.Insert(IRow)

Its at the last line that the code fails with the exception message;
"Cannot shift objects off sheet"

Can anybody please tell me what I'm doing wrong?.

 

[infinitelo]

It sounds to me that your loop is not finding
"A New Record" and going to the end of the sheet.




if it is to be it's up to me

 

[pfildes]

I've debugged the code and the value of IRow is in fact the row id where the 1st cell contains the text "A New Record".

I've also manually inserted the row within Excel to prove that the spreadsheet can indeed handle an inserted row.

Am I actually inserting a row using the correct method?. Do I need to select the row first, and if so, how?.

Any other idea's?

 

[pfildes]

I've resolved my problem by using the function;

myWorksheet.Rows(IRow).Insert()

Paul