Solved: Export to Excel code needed

[CFDM]

I need some code to send data into an Excel template. Can anyone help me with this?:banghead:

[Ken Puls]

Depends...

If you want to open the Excel template and pull the data into it from your database, then you can check out the article at my site:
Retrieve Data From A Database To Excel Using SQL (http://www.excelguru.ca/node/23)

If you actually want to push it from Access into a template (have Access launch Excel, open the template and push the data in), you'll need to do the following:

-Bind to or create a new instance of Excel
-Open the template
-Stuff your data in

I also have an article on
Connecting To/Creating A New Application Instance Via Late Binding (http://www.excelguru.ca/node/11)

HTH,

[CFDM]

This is what I used to get it to work

Private Sub cmdExport_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMTFlaggedToJournal"
DoCmd.SetWarnings True

On Error GoTo Export_Trap
Dim strFileName As String
Dim intCounter As Integer
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim intResponse As Integer

Set rst = New ADODB.Recordset
strSQL = " SELECT Company, RRDD, Center, OptType, Account, Amount, LineDes, CapturedToDFF " _
& "FROM tblFlaggedToJournal"
rst.Open strSQL, cnn1, adOpenKeyset, adLockOptimistic

Dim Row As Integer
Dim oApp As Excel.Application
Dim strFile As String

Set oApp = New Excel.Application

strFile = "U:\TXCDO\UPSDBS\CASHBOOK\JournalTemplate.xls"

oApp.Workbooks.Open strFile
oApp.Visible = True
Set workSheet = oApp.Worksheets("Sheet1")
Row = 17

' Loop through record set and populate cells with data
rst.MoveFirst
Do Until rst.EOF
If Row = 1719 Then
workSheet.Rows(Row).Insert
Else
Row = Row + 1
End If

workSheet.Cells(Row, 3).Value = rst("Company")
workSheet.Cells(Row, 4).Value = rst("RRDD")
workSheet.Cells(Row, 5).Value = rst("Center")
workSheet.Cells(Row, 6).Value = rst("OptType")
workSheet.Cells(Row, 7).Value = "000"
workSheet.Cells(Row, 8).Value = rst("Account")
workSheet.Cells(Row, 10).Value = Format(rst("amount"), "#,###,##0.00")
workSheet.Cells(Row, 11).Value = rst("LineDes")
workSheet.Cells(Row, 13).Value = rst("CapturedToDFF")
rst.MoveNext
Loop

intResponse = MsgBox("Was the journal created successfully?", vbYesNo, "Journal Creation Confirmation")
If intResponse = vbYes Then
Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
strSQL = "UPDATE tblCheckDepositDetail " _
& "SET Journal = -1 " _
& "WHERE Flag = -1 and Journal = 0 " _

cmd1.ActiveConnection = cnn1
cmd1.CommandText = strSQL
cmd1.Execute

Else

End If
Exit Sub

[Ken Puls]

Glad you got it sorted out. :)

FYI, I've edited your post to use our VBA tags to make it a bit more readable. ;)