Transfer data from a sub form and main form to excel spreadsheets

[chuch0117291]

Hi, i have a form with the main details of a vendor and and contract description and a budget reference(PK). Within this form, i have a sub form which includes cost details for that specific vendor but also has the same budget reference hence the link. I would like to create a command button to be able to export the data from the main form to an excel spreadsheet and the data from the sub form to excel spreadsheet tab2 from the click of a button. Please help some genius out there that understands this problem. Here is what i have come up with so far: My query filters the main form and extracts the vendor details filtered by [Bud Ref].
This code does not fully work as i get an error message and it just opens the template i have designed but blank. Also it has a problem with my sSQL statement.

Private Function ExportQuery() As String
On Error GoTo err_Handler
 
 
 
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim wks2 As Excel.Worksheet
 
Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String
 
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer
 
Const cTabOne As Byte = 1
Const cTabTwo As Byte = 2
Const cStartRow As Byte = 3
Const cStartColumn As Byte = 1
 
DoCmd.Hourglass True
Excel.Application.SetOption "Error Tapping", 0
 
    sTemplate = "J:\2007\Databases\Contracts\ContractsForm.xls"
    sOutput = "J:\2007\Databases\Contracts\ContractsFormTest.xls"
 
If Dir(sOutput) <> "" Then Kill sOutput
 
FileCopy sTemplate, sOutput
 
Set appExcel = New Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Open(sOutput)
 
'sSQL = "SELECT tblContracts.[Bud Ref], tblContracts.Vendor, tblContracts.[Contract Description], tblContracts.ApplicationSystemService, tblContracts.[Contract Type], tblContracts.[Cost Centre], tblContracts.HNC FROM tblContracts WHERE (((tblContracts.[Bud Ref])=[Forms]![frmContracts]![Bud Ref]))"
sSQL = "SELECT * FROM tblDashboard"
 
'DoCmd.OpenQuery "qryContracts", acViewDesign, acEdit
'DoCmd.OutputTo acOutputQuery, "", "MicrosoftExcel(.xls)", "ContractsForm", True
'DoCmd.SetWarnings False
'DoCmd.Close acQuery, "qryContracts", acSaveYes
 
 
 
 
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
 
iCol = cStartColumn
iRow = cStartRow
 
 
    If Not rst.BOF Then
        rst.MoveFirst
            Do Until rst.EOF
                iFld = 0
                lRecords = lRecords + 1
                Me.Label16.Caption = "Exporting record #" & lRecords & " to ContractsFormTest.xls"
                Me.Repaint
 
 
            rst.MoveNext
        Loop
 
 
 
    ExportQuery = "Total of " & lRecords & "rows processed."
 
 
exit_Here:
 
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function
 
err_Handler:
ExportQuery = Err.Description
Resume exit_Here
End If
 
End Function
 

[Rabbit]

Please use code tags.

You have not told us:
1) What the error message was.
2) What the SQL error message was.

You have:
1) Dimmed worksheets yet did not use them.
2) Open workbooks and yet used the automated export function.