Inserting rows in Excel from Access

[Database Administrator]

I have an Excel workbook that I'm programming from Access. The rows in
the workbook vary in height. So let's say row 8 is taller than the
rows above it. If I *manually* insert 2 new rows at the top, row 8
becomes row 10 and row 10 is now the taller row. That’s the way I want
it to be.

But when I insert lines using VBA code, row 8 remains taller and row
10 is shorter. That messes up the way my data is displayed.

Is there a way to get the spreadsheet to work the same way when
inserting rows with VBA as it does when the rows are inserted
manually?

Here’s the relevant section of my code:

‘Create a new Excel workbook based on a template workbook and
‘then activate the first worksheet so we can edit it.
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Workbooks.Open (strTemplatePath)
objXLApp.ActiveWorkbook.SaveAs (strTargetPath)
Set objXLws = objXLApp.ActiveWorkbook.Worksheets(strWorksheetNam e)
objXLws.Activate

With objXLws
‘Insert as many new rows as there are records to insert.
For lngRow = 1 To lngRecordCount
.Range("A4:E4").Insert -4121
Next
End With

After that, I populate the newly inserted rows using data in an Access
recordset.

[Database Administrator]

Apply the layout code after the data and row alterations. Make a sub
that simply does the layout formatting you want, and call it when
appropriate. I know this isnt ideal, but it works for me :-)

Cheers

The Frog