Record to excel from access

[firefry]

I am having trouble copying a record from access to excel in vba. I need the code to copy one record [that is entered on a form] at a time, instead of the entire recordset. I am using copyfromrecordset. Is there a better way of doing it? The table is not indexed, so i don't know how to specify the record entered when copying. Please help!
This is what I have:

Private Sub cmdXl_Click()
On Error GoTo Err_cmdXl_Click

Dim strPath As String
Dim xlApp As Object
Dim xlSheet As Object
Dim db As Database
Dim rec As DAO.Recordset

Set db = CurrentDb
Set rec = db.OpenRecordset("Addxl", dbOpenTable) 'dbOpenSnapshot)

DoCmd.GoToRecord , , acNewRec

Screen.MousePointer = vbHourglass
strRootDir = "U:\CWINS\CWINS Maintenance\Secured Folder\Access Databases\Quality\"
strTargetFolder1 = Year(txtDate.Value) & "\"
strTargetFolder2 = Format(txtDate.Value, "mmmm") & "\"
strTargetFile = Format(txtDate.Value, "dd") & ".xls"
strEntirePath = strRootDir & strTargetFolder1 & strTargetFolder2 & strTargetFile
strPath = strEntirePath

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(strPath).Sheets(1)

With xlApp
.Application.Workbooks(1).Sheets(1).Select
.Application.Range("A2").Select
.Application.Selection.CopyFromRecordset (rec)
.Application.Selection.Offset(1, 0).Select
.Application.ActiveWorkbook.Save
.Application.ActiveWorkbook.Close
.Quit
End With

 

[HitechUser]

firefry,

Use ADO..

Here is an example...

Microsoft

Hope this helps...

 

[jbpez]

Try:

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(strPath)

With xlApp
xlApp.Visible = False
Set xlSheet = xlBook.Sheets(1)
xlSheet.Range(xlSheet.Cells(2, 1), xlSheet.Cells(2, 1)).CopyFromRecordset rec
End With
xlBook.Close savechanges:=True
Set xlSheet = Nothing
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

I hope it helps.

John Borges

 

[jbpez]

Also,

Don't open the entire table.
Set rec = db.OpenRecordset("Addxl", dbOpenTable) 'dbOpenSnapshot)

Be specific with a SQL statement like:

strSQL = "SELECT * FROM Addxl WHERE somethin=something"
Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)

John Borges