Access Report Query

[santhisrinivas]

Dear All,

I have desined one of the software in MS-Access 2007, I also desined reports, what's my problem is, while exporting the reports into MS-Excel, only table fields are exported, i need total as it is report (Report cantains lables and headings , company logos)for the exporting. Please give the solution.

 

[SydneyGeek]

What is the actual error message?

Denis

 

[santhisrinivas]

Hai Sir,

below are the code details...


CODE

Private Sub cmdExportAutomation_Click()
On Error GoTo err_Handler
MsgBox ExportRequest, vbInformation, "Finished"
Application.FollowHyperlink CurrentProject.Path & "\AIRCEL INVOICE_ JULY_AP.xls"
exit_Here:
Exit Sub
err_Handler:
MsgBox Err.Description, vbCritical, "Error"
Resume exit_Here
End Sub
Public Function ExportRequest() As String
On Error GoTo err_Handler

' Excel object variables
Dim Appexcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks 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 cTabTwo As Byte = 2
Const cStartRow As Byte = 4
Const cStartColumn As Byte = 3

DoCmd.Hourglass True

' set to break on all errors
Application.SetOption "Error Trapping", 0

' start with a clean file built from the template file
sTemplate = CurrentProject.Path & "\AIRCEL INVOICE_ JULY_AP.xls"
sOutput = CurrentProject.Path & "\AIRCEL INVOICE_ JULY_AP1.xls"
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput

' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set Appexcel = Excel.Application
Set wbk = Appexcel.Workbooks.Open(sOutput)
Set wks = Appexcel.Worksheets(cTabTwo)

sSQL = "select * from AIRCEL AP QUERY"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
If Not rst.BOF Then rst.MoveFirst

' For this template, the data must be placed on the 4th row, third column.
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow
Stop
Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
Me.lblMsg.Caption = "Exporting record #" & lRecords & " to AIRCEL INVOICE_ JULY_AP1.xls"
Me.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)

If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If

wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next

wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop

ExportRequest = "Total of " & lRecords & " rows processed."
Me.lblMsg.Caption = "Total of " & lRecords & " rows processed."

exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
Set Appexcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

err_Handler:
ExportRequest = Err.Description
Me.lblMsg.Caption = Err.Description
Resume exit_Here

End Function
/CODE

try to run this code error will come

that error is 'uesr defined type not defined' curser focus in this "Dim Appexcel As Excel.Application" line.

Please give solution for this query..