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
Code:
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
Code: