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? :)