Inserting rows in Excel from Access

[evenlater]

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("E xcel.Applicatio n")
objXLApp.Workbo oks.Open (strTemplatePat h)
objXLApp.Active Workbook.SaveAs (strTargetPath)
Set objXLws = objXLApp.Active Workbook.Worksh eets(strWorkshe etName)
objXLws.Activat e

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.

[The Frog]

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