Append to excel worksheet

  • Thread starter Thread starter cunger28
  • Start date Start date
C

cunger28

Is there a means to append access records to an excel worksheet? I've seen
work arounds to where people suggest importing the destination excel file
into a temp table in access, append the necessary detail, then turn around
and re-export that file. I would like to avoid that if necessary.

Thanks
 
Chris,

Here's some code that will write data directly to a spreadsheet. This code
assumes that an Excel worksheet is already open, and it creates a new tab.
You should be able to modify it to read down to the bottom of your worksheet
and add data at that point.

Hope this helps




Dim objXL As Object
Dim objActiveWkb As Object

Dim db As Database
Dim rs As Recordset

Dim c As Integer 'Numeric value of column (A=1)
Dim r As Integer 'Row
Dim fld as Field

Dim lngCount As Long 'For meter

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM MyTable", dbOpenSnapshot)

rs.MoveLast
rs.MoveFirst
SysCmd acSysCmdInitMeter, "Exporting", rs.RecordCount

Set objXL = GetObject(, "Excel.Application")
Set objActiveWkb = objXL.Application.ActiveWorkbook

With objActiveWkb

'Create a new sheet in workbook
.Worksheets.Add.Move after:=.Worksheets(.Worksheets.Count)

'Position cursor at cell A1
r = 1
c = 1

Do While Not rs.EOF

'Write record to spreadsheet
With .ActiveSheet
For Each fld In rs.Fields
.Cells(r, c) = rs(c - 1) 'Fields collection is zero based
c = c + 1
Next fld
End With

r = r + 1

lngCount = lngCount + 1
SysCmd acSysCmdUpdateMeter, lngCount
rs.MoveNext
Loop

End With

rs.Close
Set rs = Nothing

Db.Close
Set db = Nothing

Set objActiveWkb = Nothing
Set objXL = Nothing
SysCmd acSysCmdRemoveMeter
 
Back
Top