Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    4

    Question Unanswered: Access Query to Excel File

    I am trying to move the results from an Access query to Excel. I have no problem doing this with table's but am running into some brick walls trying to do the same thing with a query instead of a table. Below is the code I am use to move the data in my table to Excel, not sure how much of it I would need to change to move my query data over instead. Once working correctly I also need to be able to change the query I am moveing based upon a selection from the user form.

    Private Sub cmdRptExcel_Click()
    On Error GoTo Err_cmdReport_Click
    Dim txtReportType As String

    Dim rst As ADODB.Recordset
    Dim qdf As QueryDef
    Dim XlApp As Excel.Application
    Dim XlBook As Excel.Workbook
    Dim XlSheet As Excel.Worksheet
    Dim TabName As String
    Dim DateRange As String
    Dim dbDataSet As String
    Dim I As Integer


    Set XlApp = New Excel.Application
    Set XlBook = XlApp.Workbooks.Add

    strReportID = Me.lstReports
    If IsNull(strReportID) Then
    MsgBox "Please select a report"
    GoTo Exit_cmdReport_Click
    End If

    dbDataSet = Me.cmbReportOutput.Column(0)
    txtReportType = Me.cmbReportOutput.Column(2)

    DateRange = Me.txtMonthS & Right(Me.txtYearS, 2) & "-" & Me.txtMonth & Right(Me.txtYear, 2)
    TabName = Mid(Me.cmbReportOutput.Column(0), 4, Len(Me.cmbReportOutput.Column(0)) - 12)

    Set XlSheet = XlBook.ActiveSheet

    XlSheet.Name = TabName & DateRange

    Set rst = New ADODB.Recordset
    rst.Open _
    Source:="tlkTableQuery", _
    ActiveConnection:=CurrentProject.Connection

    MsgBox rst.Fields.Count

    For I = 0 To rst.Fields.Count - 1
    XlSheet.Cells(1, I + 1).Value = rst.Fields(I).Name
    XlSheet.Cells(1, I + 1).Font.Bold = True
    Next I

    With XlSheet
    .Range("A2").CopyFromRecordset rst
    .Columns.AutoFit
    End With

    XlApp.Visible = True

    Exit_cmdReport_Click:
    rst.Close
    Set rst = Nothing
    Set XlSheet = Nothing
    Set XlBook = Nothing
    Set XlApp = Nothing
    Exit Sub

    Err_cmdReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdReport_Click
    End Sub

  2. #2
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Why don't you try docmd.exportTo option.?

  3. #3
    Join Date
    Jul 2004
    Posts
    4
    I was doing it that way, export with docmd and then run an excel macro to format the data and save it to a shared network drive. I want to be able to do all of that from Access though now, hit one button export the query to excel call my formatting macro from excel and then save it to my shared drive. I already do this with a table and have no problems, it is the moving of the query into Excel from Access with out the DoCmd I have problems with.

  4. #4
    Join Date
    Jun 2004
    Location
    Saratoga Springs
    Posts
    24
    If you already have success doing what you want with tables, you could materialize your query into a table using a make table query. Then, once the export is complete, drop the table from your database.
    Jon Jaussi, OCP DBA
    D4L Data Managent Solutions
    D4L_solutions@comcast.net

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •