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]

You can't export a formatted and grouped report to Excel, not if you want to keep the formatting.
You can either print a PDF for emailing, or pull the data into Excel and create the formatted layout there. That could be tricky, depending on what you need the report to do.

Denis

 

[santhisrinivas]

Thankyou very much for your reply..

any way i want any solution for that. in excel i have desined that format. through MS-excel can you please tell the code, that particular access data come in to that excel sheet.


I want another solution...

This is the final sheet, designed in excel like circle wise.

<TABLE style="WIDTH: 458pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=611><COLGROUP><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1645" width=45><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: olive; WIDTH: 47pt; HEIGHT: 11.25pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=15 width=63>Product</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 411pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl68 width=548 colSpan=10>CLCM_RC22 </TD></TR><TR style="HEIGHT: 33.75pt" height=45><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffff; WIDTH: 47pt; HEIGHT: 33.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=45 width=63>Day</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffff; WIDTH: 50pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=67>Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffff; WIDTH: 35pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=47>Platform</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffff; WIDTH: 55pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=73>Total data provided/Base Uploaded</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffff; WIDTH: 42pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=56>Calls made</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffff; WIDTH: 39pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=52>Call answered</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffff; WIDTH: 46pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=61>Call answered / call made %</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffff; WIDTH: 32pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=43>0-5 secs discntn</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffff; WIDTH: 37pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=49>6-15 secs discntn</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffff; WIDTH: 41pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=55>16-30 secs discntn</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffff; WIDTH: 34pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=45>>30 secs discntn</TD></TR></TBODY></TABLE>

Daily data will come in before format.

<TABLE style="WIDTH: 584pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=777><COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 66pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=88>date(start)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 92pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=122>Application name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=86>base received</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> Unique Dialouts </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=97> success </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> single_conv </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>0-5succ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>6-15succ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>16-30succ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>30abovesucc</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>9/29/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>BIRTHDAY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>72.73%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 align=right>9/29/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>TEST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>100.00%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>2</TD></TR></TBODY></TABLE>


Can you give the solution for this data directly go to our final format..if any macro in Excel for this solution. give that code details...or if any code this data will go in our final format like application wise..

 

[SydneyGeek]

Pull the data into another sheet in the Excel workbook using Data > External Data > Access. You can then use pivot tables and / or formulas to summarise the data so that you can use it in the reports.

Denis

 

[HiTechCoach]

The method I like to use is Excel Automation.

See: Export Data To Excel

 

[santhisrinivas]

Thankyou for your reply..

While running this code in Access 2007, this "User-defined type not defined" error came for this code line " Public Function ExportRequest() As String"...How to resolve this problem..

 

[SydneyGeek]

You'll have to post the code before we can help you. Please put it inside CODE tags to make it easier to read.
Put [ CODE ] before the pasted code
Put [ /CODE ] after the pasted code
NOTE: NOT with the spaces -- between the [ and the ] there should be NO SPACES. I have just included them so you can see the layout.

Denis

 

[santhisrinivas]

Thankyou for your reply.


This is our code. while running the code error will come. Please give solution. if this is resoved our reports are easy to do.. daily our members are find that application and copy and paste the values.. my question is to avoid this copy paste activity directly how it will work.. my project is reports automation...

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

 

[SydneyGeek]

Thanks for pasting the code. A comment: The square brackets are also required for the CODE tags to work. I have put them in for you.

You say that you get an error. Where in the code does it happen?
Which line is highlighted when you press the Degub button after an error?

Denis

 

[santhisrinivas]

Thankyou for your reply...

error come in this line 'Public Function ExportRequest() As String'