Inserting rows in Excel worksheet

[DCrake]

I have put this here as more people read the General forum.

This is a rather simple task and I have done it before but cannot get it to work today and I do not have access to my server.

Basically I have a recordset that I am using CopyFromRecordset Rs functionality. However what I want to do is to insert n number of rows between the heading (Row 5) and the totals (Row 6) so that the formatting is presevered and the recordset is copied between the headings and the totals row.

I recorded a macro in Excel which after manipulation to suit my needs resulted in:

           xlSheet.Rows("6:" & tCount + 6).Select
            Selection.Insert Shift:=xlDown

However the second line fails

Object doesn't suppport this property or method

MSKB doesn;t offer any solutions.

David

 

[namliam]

If you are executing this code from within Access this doesnt know the Excel variables (Like xlDown)

Also each line needs to be preceded (I think) by xlSheet object.

 

[DCrake]

Have now managed to come up with a solution

            With xlSheet
                .Rows(strRange).Insert
            End With

Where strRange is for example "6:20"

6 is the first row position to insert
20 is the number of rows to insert

Once this has been completed I use the

xlSheet.range("A6").CopyFromRecordset Rst

to populate the spreadsheet.

David

 

[namliam]

Which is basicaly what I said... You need to use xlSheet. in front

But instead of adding xlSheet on the second line you merged it into 1 line... Basicaly same, but slightly different.