We help IT Professionals succeed at work.

Excel Automation Insert Row

SZachmann
SZachmann asked
on
1,871 Views
Last Modified: 2012-05-05
Experts

I have an existing excel document that has a header with formulas and footer with formulas, mostly summing the information above.  

How do I use automation to insert data into the middle section without knowing how many rows there will be?  For example I have one customer with only 5 records and another with 100.  I could not figure out how to insert rows through automation.  In addition when I insert a row how do I keep the formatting of the source row?

Thanks in advance
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
Hi

minor correction

use
oBook = oExcel.Workbooks.Open("C:\test.xls")

instead of

oBook = oExcel.Workbooks.Add("C:\test.xls")

and if you dont want to display the save message "do you want to save changes i mysheet"

then apply :oExcel.Application.DisplayAlerts = False

            oExcel.Application.DisplayAlerts = False
            'Save the Workbook and quit Excel.
            oBook.SaveAs("C:\test.xls")


vbturbo

Author

Commented:
vbTurbo

Thank you for the quick response, I changed your code and got it to work I think!  I am going to try a couple of things then if everything is ok accpet your answer.  Thank you!!

Commented:
ok that's just fine

hope you get it ,so it suits you

vbturbo

Author

Commented:
vbTurbo

Thank you again.  Here is how I changed your code, not much but for anyone else looking at this post.  

Add a referance to Excel
Imports Microsoft.Office.Interop

 'Start a new workbook in Excel.
        Dim oExcel As New Excel.Application
        Dim oBook As Excel.Workbook = oExcel.Workbooks.Open("C:\template.xls")
        Dim oSheet As Excel.Worksheet = oBook.Worksheets("sheet1")

        ' Change Header Values
        oSheet.Range("F1").Value = "Name" ' Name
        oSheet.Range("D3").Value = 0.1 ' %
        oSheet.Range("D4").Value = 0.05 ' %

        For i As Integer = 0 To 3
            Dim range As Excel.Range = oSheet.Range("A8:L8")
            range.Insert(Excel.XlInsertShiftDirection.xlShiftDown)
            oSheet.Range("B7").Value = "Name" ' Name
            oSheet.Range("C7").Value = "Year" ' 2006
            oSheet.Range("D7").Value = "75000" ' Salary
        Next
        'Save the Workbook and quit Excel.
        oBook.SaveAs("C:\test.xls")

        oSheet = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()
        MsgBox("Rows has now been created", MsgBoxStyle.Information, "Info")

Commented:
SZachmann

tip.

        For i As Integer = 0 To 3
            Dim range As Excel.Range = oSheet.Range("A8:L8")
            range.Insert(Excel.XlInsertShiftDirection.xlShiftDown)
            oSheet.Range("B7").Value = TextBox1.Text
            oSheet.Range("C7").Value = ds.Tables("tbl").Rows(index).Item(0)
            oSheet.Range("D7").Value = DatagridView.Cells(5,1)
        Next

Author

Commented:
vbTurbo

I thought I looked at everything right, but when I insert the range it does not copy the formulas and borders, do you know how to fix that?

Thanks,

Commented:
some border setting

Range("A1).Borders(xlEdgeLeft).LineStyle = xlDouble
.Range("A1).Borders(xlEdgeLeft).Weight = xlThick
.Range("A1).Borders(xlEdgeLeft).ColorIndex = xlAutomatic

.Range("A1).Borders(xlEdgeRight).LineStyle = xlDouble
.Range("A1).Borders(xlEdgeRight).Weight = xlThick
.Range("A1).Borders(xlEdgeRight).ColorIndex = xlAutomatic

.Range("A1).Borders(xlEdgeTop).LineStyle = xlDouble
.Range("A1).Borders(xlEdgeTop).Weight = xlThick
.Range("A1).Borders(xlEdgeTop).ColorIndex = xlAutomatic

.Range("A1).Borders(xlEdgeBottom).LineStyle = xlDouble
.Range("A1).Borders(xlEdgeBottom).Weight = xlThick
.Range("A1).Borders(xlEdgeBottom).ColorIndex = xlAutomatic

Author

Commented:
Thanks for all your help.  Here is the code I ended up using:

For i As Integer = 8 To 10
            Dim range As Excel.Range = oSheet.Range("A" & i & ":L" & i)
            range.Select()
            range.Copy()
            range.Insert(Excel.XlInsertShiftDirection.xlShiftDown)
            oSheet.Range("B" & i).Value = ""
            oSheet.Range("C" & i).Value =""
            oSheet.Range("D" & i).Value = ""
Next

Using that .Copy copyed the formulas and the format.  
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.