Inserting x rows into excel

[Wuelf]

Greetings,
I have just made a query transfer into a preformed excel report.
I was hoping someone could explain how i would copy the first row of the
excel report (which has formulas)and copy and insert the same amount of rows
as records in my query - what i have currently is about 1200 rows in excel
and then manual trim that to suit - I would like to automate this procedure
from my access code.
Thanks in advance

 

[John Nurick]

Hi Wuelf,

Please can you explain in more detail?

1) I'm not sure what you mean by "transfer into a preformed Excel
report". Are you exporting an Access report to Excel, or exporting a
query to an existing Excel worksheet, or using CopyFromRecordset?

2) It sounds as if you want to insert rows into the worksheet above your
data and fill them with formulas. Is that correct?

 

[Wuelf]

Hi John, thanks for the response
1) I'm using VBA code (recordset) to transfer data from a query into an
excel spreadsheet that is already formatted as required - the end columns of
the spreadsheet have formulas (also there is total formulas at the bottom of
the spreadsheet)
2) what I would like to do in the same code is copy the first row of the
spreadsheet (blank data and including formulas) and paste-insert as many rows
into the spreadsheet as records in the query. (currently I have 1200+ rows in
the spreadsheet so that when I transfer the data it doesn't wipe out the
bottom formulas, I then delete the empty rows) the data from the query usely
runs from about 10 records to 1200+)

I hope that makes sense

Thanks

 

[John Nurick]

Assuming that you've got an Excel.Worksheet object variable (which I'll
call oSheet, you can probably just do something like this

Dim lngRows As Long
Dim raDestination As Excel.Range

lngRows = rsMyRecordsetRecordCount
With oSheet
Set raDestination = .Range(.Rows(2), .Rows(lngRows + 1))
.Rows(1).Copy raDestination
End With

 

[Wuelf]

Hi John,
Thanks for that it works fine except for one small item is it possible to
insert rows instead of copy(so the bottom formulas don't get wiped)?
Thanks again and sorry for being a pain

 

[John Nurick]

Something like this should insert the rows after row 1 before you copy
the data as before.

With oSheet
.Range(.Rows(2), .Rows(lngRows).Insert
...