INSERT to excel file

[Annette]

I am using the VBA code(below) to add data from a query to an excel
worksheet. The code works perfect except it starts adding records
beginning at my chosen cell A2 and continues to the end of the excel
file. I have an excel file that has a header row and a trailer row, so
all of the data from the access query needs to start at row 3 and be
INSERTED to the file.

For example -- manually -- I can copy the rows and open another excel
worksheet and INSERT COPIED CELLS and the data would insert in the
space I wanted and move everything else down.

How can I do that in VBA -- keeping in mind that I don't want to
fiddle with row 1 information or row 3 information -- just insert
beginning in row 2 retaining my row 3 trailer information (which will
now be moved to the end of the file -- so if I inserted 10 rows, the
trailer record would move down to be at row 12?


http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

 

[Ken Snell]

Which one of the code examples at my website are you currently using?

 

[Annette]

I am using the "Write Data From a Recordset into an EXCEL Worksheet
using Automation VBA".

 

[Ken Snell]

OK. Yes, that code starts at the cell you designate and goes from there. It
will not insert into the EXCEL file. To do that, you'll need to use a
somewhat different approach.

Two methods come to my mind initially:

1) Use Automation to insert into the EXCEL spreadsheet instead of just
writing on the spreadsheet. This can be done with the code you're using by
adding a line that inserts a new row before each record is written to the
sheet. If you post your code, we should be able to assist with the changes
needed.

2) Read the trailer row from EXCEL into variables before you start writing
on the spreadsheet. After the recordset is done, write these values onto the
next row. If you have formatting that needs to be preserved, you'd also need
to read and store all the properties of that row's cells so that you can put
those properties on the new, last row.

Option 1) is probably an easier approach, both in terms of code complexity
and ease of use.

 

[Annette]

I finally got it to work. Of course I had to set a reference to the
Excel library. The last two lines before the LOOP command inserts a
new record (xlc.entirerow.insert) in the file, retaining the
formatting of the cells, and the next line sets the location of where
to write the data.


If rst.EOF = False And rst.BOF = False Then

rst.MoveFirst

If blnHeaderRow = True Then
For lngColumn = 0 To rst.Fields.Count - 1
xlc.offset(0, lngColumn).value =
rst.Fields(lngColumn).Name
Next lngColumn
Set xlc = xlc.offset(1, 0)
End If

' write data to worksheet
Do While rst.EOF = False
For lngColumn = 0 To rst.Fields.Count - 1
xlc.offset(0, lngColumn).value =
rst.Fields(lngColumn).value
Next lngColumn
rst.MoveNext
xlc.entirerow.Insert
Set xlc = xlc.offset(-1, 0)
Loop

End If