Tuesday, January 24, 2012

More Excel Code: Inserting and Copying Rows

I was working on some code to export data to a pre-formatted Excel file and, as part of that, needed to be able to insert rows into a range so that the data I added would be included in formulas below it (they were column totals and data summaries). As I find typical when trying to control Excel from LotusScript, I couldn't locate examples of the code I needed. That doesn't mean they aren't out there, just that I couldn't find them.

After some experimentation and some dumb luck stumbling on information that referred to methods I wasn't aware of in the COM interface, I came up with the code below.

Sub CopyFormulas(xlSheet As Variant, rowNum As Integer, firstCol As Integer, lastCol As Integer)
Dim x As Integer
Dim cellVal As String
xlSheet.Activate
xlSheet.Cells(rowNum-1,1).EntireRow.Copy
xlSheet.Cells(rowNum,1).EntireRow.Select
xlSheet.PasteSpecial(7)
xlSheet.Application.CutCopyMode = False
For x = firstCol To lastCol
cellVal = xlSheet.Cells(rowNum,x).Formula
If Left(cellVal,1) <> "=" Then
xlSheet.Cells(rowNum,x).ClearContents
End If
Next
End Sub



This LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at nsftools.com.


This takes the Worksheet object you pass it, copies the content and format of the row above your current row into your current row and then removes the contents of a defined set of columns if they are not formulas. The problems I had were that the copy was not done in the proper place and I wasn't maintaining the formulas and formatting of the row I was copying.

The copy location was solved by adding the xlSheet.Activate line. My code was writing data to three different worksheets (one each for month-to-date, quarter-to-date and year-to-date) so I found that adding that line made sure the proper sheet was active.

The formatting issue was resolved by a combination of two methods. One is .PasteSpecial(7) , where the 7 represents the xlPasteAllExceptBorders paste option. The other is the .ClearContents method. I was using the .Paste and .Clear methods initially. I suspect I could use the .Paste and .ClearContents methods, since I think the .Clear method was the culprit that was deleting the formats, but I am going to leave it the way it is since a) I know it works, and b) the .PasteSpecial method indicates what I am really trying to do.

By the way, the code to insert a row is xlSheet.Cells(row,col).EntireRow.Insert.

Enjoy! Now, how many times do you think I'll find this blog post when I search for this in the future? :)