Appending to Existing Excel File from Recordset (1 Viewer)

[wilderfan]

I'm spinning my wheels on this. Trying to copy a recordset into an existing excel file. Here's the code I have so far. Any suggestion?

Private Sub cmdExport_Click()

Dim conn As ADODB.Connection
Dim conn2 As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strConnection2 As String
Dim wks As Excel.Worksheet

Dim intMaxCol As Integer
Dim intMaxRow As Integer

Set conn = CurrentProject.Connection

strSQL = "SELECT Title, YearPublished FROM tblTitles"

rst.Open strSQL, conn, adOpenKeyset, adLockOptimistic


' Open connection2 with Excel file

Set conn2 = New ADODB.Connection

strConnection2 = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & CurrentProject.Path & "\Books.xlsx;" _
& "Extended Properties=""Excel 12.0; HDR=Yes"""

conn2.Open strConnection2

intMaxCol = rst.Fields.Count
' MoveLast/First to get an accurate RecordCount
rst.MoveLast
rst.MoveFirst

If rst.RecordCount > 0 Then

intMaxRow = rst.RecordCount

Set wks = Books.xlsx.[Worksheets("Sheet1")]

With wks
.Range(.Cells(2, 1), .Cells(intMaxRow + 1, intMaxCol)).CopyFromRecordset rst
End With

End If

conn.Close
Set conn = Nothing

conn2.Close
Set conn2 = Nothing

End Sub

 

[wilderfan]

I see that my code didn't get "blocked off".

FYI - I did apply the pound sign. Don't know why it didn't work.

 

[namliam]

dont have time to go into your post on details.

Your # button adds two tags into your post [CADE] and [/CADE]
The A obviously replaced by O obviously.
Your code should be inbetween those two tags.