• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Access Report or Query to Excel

Hi,

I have a bunch of reports that need to be output to Excel through code.  The formatting is not important.  The issue is that these reports are created by the user with specific data options and are opened with a WHERE clause and some data (mostly formatting) in OPENARGS.  

DoCmd.OutputTo doesn't allow me to use a WHERE clause.  Is the best option to use code to create and save a new query with the user choices and then use DoCmd.TransferSpreadsheet?  Is there something else that would be easier?

Neal Holden
0
nlholden
Asked:
nlholden
  • 4
  • 4
  • 3
1 Solution
 
ref-ITCommented:
Hello nlHolden,

Instead of using code to create and save a new query every time user makes a choice or choices, why not just construct the query to include all the tables in the original form, and then utilise the users choices to filter that query and then perform a docmd.TransferSpreadsheet.

Im not sure but I think you can filter queries using queryDefs in code.

Hope this idea helps.

ref-IT
0
 
ukerandiCommented:
You can use VBA code to do that. using Filesysstem Object you can write txt file or Xls files.
see example code
Or you can create Query and using Macro you can transfer results to excel

Sub ADOImportFromAccessTable(DBFullName As String, _
    TableName As String, TargetRange As Range)
' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _
    "TableName", Range("C1")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
    Set TargetRange = TargetRange.Cells(1, 1)
    ' open the database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
        DBFullName & ";"
    Set rs = New ADODB.Recordset
    With rs
        ' open the recordset
        .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable 
        ' all records
        '.Open "SELECT * FROM " & TableName & _
            " WHERE [FieldName] = 'MyCriteria'", cn, , , adCmdText 
        ' filter records
        
        RS2WS rs, TargetRange ' write data from the recordset to the worksheet
        
'        ' optional approach for Excel 2000 or later (RS2WS is not necessary)
'        For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
'            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
'        Next
'        TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

    End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Open in new window

0
 
ukerandiCommented:
this code is perfect
Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\FolderName\DataBaseName.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable  
    ' all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0 
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
nlholdenAuthor Commented:
ukerandi:

Your VBA looks great, but the users are working in Access when they are making their selections.  It appears that your code works from the Excel side to grab the data from an Access database.  Right now they can make their choices in Access and run an Access report which they can print or save as a PDF.  Many of the reports, there are more than 20, would be more valuable in Excel to analyze. Do you have VBA to run from Access?  I guess I could put on a button to open an excel spreadsheet with similar options to Access and have the users run the code from Excel.  

ref-IT:

I looked at your idea, but I still need to save the query. Here is an the direction I was trying:

1

Public Function strMakeQuery(strReportName As String, strWhere As String) As String
    ' Comments  :
    ' Parameters: strReportName   Report to get the query from
    '                     strWhere             WHERE clause to add to the query
    '                     strMakeQuery     completed query to pass back

    Dim dbCurrent   As DAO.Database
    Dim qryCurrent  As DAO.QueryDef

    Set dbCurrent = CurrentDb
   
'   How can I get the current query from a Report???
'   qryCurrent =

    strMakeQuery = qryCurrent + " " + strWhere
   
End Function
And then DoCmd.TransferSpreadsheet can be used to transfer to Excel. The reports are selected from a menu and then the filters the users want to use. So the name of the report isn't known until the user selects it.  The long way would be to hard code the queries for each of the reports, but if the report ever changed, the coded query would have to change as well. I was hoping to find a more elegent way.
0
 
ukerandiCommented:
What you have to do is go to Form and create new form Add the new form.
steps
01. Add new Text box
02. Add new command button

using text box you can put parameter value
using command button you have to write a code for generate report

for example

see you code below

    strMakeQuery = qryCurrent + " " + strWhere


after you add text box code should be

  strMakeQuery = qryCurrent + " " + textbox1.text

then code should be
select * from [Tablename] where [YourFieldname]=" & textbox1.text



Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\FolderName\DataBaseName.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "select * from [Tablename] where [YourFieldname]=" & textbox1.text , cn, adOpenKeyset, adLockOptimistic, adCmdTable  
    ' all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0 
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Open in new window

0
 
nlholdenAuthor Commented:
ukerandi:

This still looks like you are in Excel putting information into Access.

I'm trying to take information from an Access report and put it into Excel.  Am I looking at your code in the wrong way.

0
 
ref-ITCommented:
Hey nlHolden,

Sorry for the delay in response. I got a little tied up with other issues.

According to the way you have explained it using code, you want to construct a query based on the selected report and the filters that a user chooses. That is to say, that a user will first select a report from a menu and then will choose a filter and depending on those criteria. The chosen filters in this case will form the WHERE clause however, you want to know the name of the underlying query in the report? Please correct me if I am wrong?

If my understanding is right then this is what you do:

Public Function strMakeQuery(strReportName As String, strWhere As String) As String
    ' Comments  :
    ' Parameters: strReportName   Report to get the query from
    '                     strWhere             WHERE clause to add to the query
    '                     strMakeQuery     completed query to pass back

    Dim dbCurrent   As DAO.Database
    Dim qryCurrent  As DAO.QueryDef

    Set dbCurrent = CurrentDb
   
'   How can I get the current query from a Report???
    qryCurrent = Reports(strReportName).RecordSource
    strMakeQuery = "SELECT * FROM " + qryCurrent + " " + strWhere
   
End Function

Since you have already got the name of the report, you can use Reports(NameOfReport) which in your case is strReportName and then reference the RecordSource Property to obtain the name of the underlying query. In the next statement you can formulate the SELECT Query, save it and send the data to an Excel Spreadsheet using a

DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, NameOfQuery, FullPath

Let me know if this helps.

ref-IT



0
 
ref-ITCommented:
Oh also, please note that in the above approach, the report in question should be opened first. So be sure to put a

DoCmd.OpenReport "rptInvoices", acViewPreview (Plus the filters or you can leave them if you want to).

The main aim to open the report is so that Access can reference that Report Object's record source property to get the name of the underlying query.

You can always close the report in the code using
docmd.Close acReport, ReportName

Hope this helps you.

ref-IT
0
 
nlholdenAuthor Commented:
ref-IT:

This code works.  Based on your suggestions my routine now looks like this:

Public Sub CreateExcelSheet (strReportName As String, strWhere As String) As String
    ' Comments  :
    ' Parameters: strReportName   Report to get the query from
    '                     strWhere             WHERE clause to add to the query

    Dim rpt As Report
    Dim strQuery As String
     
        ' send to Excel
        ' first open the report to view
        DoCmd.OpenReport strReportName, acViewPreview

        ' get the underlying query
        Set rpt = Reports(pstrType)
        strQuery = rpt.RecordSource
        strQuery = CurrentDb.QueryDefs(strQuery).SQL
        
        ' first strip trailing ";" if it exists
        If (InStr(1, strQuery, ";") > 1) Then
            strQuery = Left$(strQuery, InStr(1, strQuery, ";") - 1)
        End If
        strQuery = strQuery + " WHERE " + strWhere + ";"

       '  now create a stored query def
        On Error Resume Next   ' if there is no query to delete, we don't want to stop 

            With CurrentDb              '  it would be better to check to see if the
                                                  '  querydef exists and then delete it
                     .QueryDefs.Delete ("qryTemp")
                                                 '  createquerydef command line follows
                     Set qdfNew = .CreateQueryDef("qryTemp", strQuery)
                    .Close
           End With
        
        ' send everything off to create a new Excel spreadshet
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQuery, strReportName

        DoCmd.Close acReport, strReportName
End Sub

Open in new window


OK, this works and creates a spreadsheet in my default documents directory.  The only thing not working is the report isn't closing but that is a minor issue that I'm sure I can figure out.

Although I figured much of it out myself, you got me put on the right track.  

Thanks

0
 
nlholdenAuthor Commented:
I was put on the right track, but had to supply some of the solution myself.
0
 
ref-ITCommented:
You're welcome ... always glad to help.

Another suggestion Id like to make is that if the formatting of the exported data into Excel wasn't important and also if your users are using an Access version that is 2007 Plus, then simply right clicking on the opened report -> Export -> Excel.

It would automatically export the whole report into Excel.

Anyway, at least the problems been resolved.

Regards,
ref-IT
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now