none
Insert a row in Excel RRS feed

  • Question

  • I am trying to insert a row at the begining of my excel file with VB.Net in VS2010. I don't get any errors, but when I open the excel file, I see that this code inserts the row, but deletes most of the existing data. I am not sure how to debug this. Any help is greatly appreciated.

    Dim xlApp As Microsoft.Office.Interop.Excel.Application
    Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
    Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet

    xlApp = New Microsoft.Office.Interop.Excel.Application
    xlBook = xlApp.Workbooks.Open("c:\temp\erep.xls")
    xlSheet = xlBook.Worksheets(1)

    xlSheet.Range("A1", "S1").Insert()
    xlSheet.Cells.Cells(1, 1) = "Report"
    xlSheet.Cells.Cells(1, 2) = "Report Line"
    xlSheet.Cells.Cells(1, 3) = "Program"
    xlSheet.Cells.Cells(1, 4) = "Service"


    donsls

    Monday, August 27, 2012 3:26 PM

Answers

  • Hi Donsls,

    Thank you for posting in the MSDN Forum.

    The code below may be what you want. 

    Imports Excel = Microsoft.Office.Interop.Excel
    Module Module1
    
        Sub Main()
            Dim fName As String
            Dim xlApp As Excel._Application
            Dim xlBook As Excel.Workbook
            Dim xlSheet As Excel.Worksheet
    
            'Suppose that you have a workbook named Workbook.xls in D:\
            fName = "D:\Workbook.xls"
    
            xlApp = New Excel.Application
            xlApp.Visible = True
            xlBook = xlApp.Workbooks.Open(fName)
            xlSheet = xlBook.Sheets(1)
    
            xlSheet.Rows(1).Insert()
            xlSheet.Cells(1, 1).value = "Report"
            xlSheet.Cells(1, 2).value = "Report Line"
            xlSheet.Cells(1, 3).value = "Program"
            xlSheet.Cells(1, 4).value = "Service"
    
            xlBook.Save()
            xlBook.Close()
            xlApp.Quit()
    
        End Sub
    
    End Module
    

    Effect of code above:

    Hope it helps.

    Best regards,
    Quist


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us

    • Proposed as answer by Quist ZhangModerator Tuesday, August 28, 2012 8:52 AM
    • Marked as answer by Donsls Tuesday, August 28, 2012 12:46 PM
    Tuesday, August 28, 2012 8:51 AM
    Moderator

All replies

  • I think you want to insert a new row.....

    xlSheet.Rows(1).Insert

    Monday, August 27, 2012 7:46 PM
  • Hi Donsls,

    Thank you for posting in the MSDN Forum.

    The code below may be what you want. 

    Imports Excel = Microsoft.Office.Interop.Excel
    Module Module1
    
        Sub Main()
            Dim fName As String
            Dim xlApp As Excel._Application
            Dim xlBook As Excel.Workbook
            Dim xlSheet As Excel.Worksheet
    
            'Suppose that you have a workbook named Workbook.xls in D:\
            fName = "D:\Workbook.xls"
    
            xlApp = New Excel.Application
            xlApp.Visible = True
            xlBook = xlApp.Workbooks.Open(fName)
            xlSheet = xlBook.Sheets(1)
    
            xlSheet.Rows(1).Insert()
            xlSheet.Cells(1, 1).value = "Report"
            xlSheet.Cells(1, 2).value = "Report Line"
            xlSheet.Cells(1, 3).value = "Program"
            xlSheet.Cells(1, 4).value = "Service"
    
            xlBook.Save()
            xlBook.Close()
            xlApp.Quit()
    
        End Sub
    
    End Module
    

    Effect of code above:

    Hope it helps.

    Best regards,
    Quist


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us

    • Proposed as answer by Quist ZhangModerator Tuesday, August 28, 2012 8:52 AM
    • Marked as answer by Donsls Tuesday, August 28, 2012 12:46 PM
    Tuesday, August 28, 2012 8:51 AM
    Moderator
  • Thanks for all the replies. It works great. Much appreciated.

    donsls

    Tuesday, August 28, 2012 12:46 PM