Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2017
    Posts
    2

    Question Unanswered: MS Access 2010 Query to Excel Spreadsheet using VBA

    I need to transfer data from a query to an Excel spreadsheet. The code worked until I changed the name of the query.

    All of a sudden, I started to receive an error highlighted at the line:
    Set rs_CBI_eLMS = qdf_CBI_eLMS.OpenRecordset

    The error stated: User Type Not Defined

    I have tried referencing the database using ADODB, DAO, as well as referencing neither, as it was working with neither before the change.

    Referencing the database as DAO, I receive the following error:
    Invalid Operation

    I am hoping this is some type of syntax error, but I am at a loss!

    BTW, the Query works fine when I don't attempt to save the records via VBA. I can call a separate Macro to save the spreadsheet, but I want to use VBA, all in one function.

    Private Sub cmd_Incomplete_Export_old_Click()
    On Error GoTo Err_cmd_Incomplete_Export_old_Click

    'Must have checked Tools-References-MS Excel 14.0 Object Library

    'Variables used for Query
    Dim db As DAO.Database
    Dim qdf_CBI_eLMS As DAO.QueryDef
    Dim rs_CBI_eLMS As DAO.Recordset

    'Set up reference to the query
    Set db = CurrentDb
    Set qdf_CBI_eLMS = db.QueryDefs("qry_CBI_eLMS")

    'Executes the query and saves the recordset to rs_CBI_eLMS
    Set rs_CBI_eLMS = qdf_CBI_eLMS.OpenRecordset

    'Tests to see if Recordset exists in the DB
    If rs_CBI_eLMS.NoMatch Then
    MsgBox "There are no entries for this week."
    Else
    'Do nothing'
    End If

    'Excel Variables
    Dim XL As Object
    Dim wbTarget As Excel.Workbook

    'Converts the new data queried to Excel Spreadsheet
    Set XL = CreateObject("Excel.application")

    'Set reference to the export target Workbook
    Set wbTarget = XL.Workbooks.Open("W:\AirTraffic\Training\eLMS\Inc omplete\Incomplete-CBI.xlsx")

    'Clear worksheet cells of all values
    wbTarget.Worksheets("Incomplete-CBI").Select
    wbTarget.Worksheets("Incomplete-CBI").Cells.ClearContents

    'Copy from recordset to paste in spreadsheet
    wbTarget.Worksheets("Incomplete-CBI").Cells(1, 1).CopyFromRecordset rs_CBI_eLMS

    'Saves then closes the Workbook
    wbTarget.Save
    wbTarget.Close

    'Clear variables
    Set qdf_CBI_eLMS = Nothing
    Set wbTarget = Nothing
    Set XL = Nothing

    'Pop up window confirms data export
    MsgBox "Your data has been exported", vbInformation, "Action Completed"

    Exit_cmd_Incomplete_Export_old_Click:
    Exit Sub

    Err_cmd_Incomplete_Export_old_Click:
    MsgBox Err.Description
    Resume Exit_cmd_Incomplete_Export_old_Click

    End Sub

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    630
    Provided Answers: 34
    If you are in Access,you don't need any DAO code. Access IS the dao.
    Just use the excel code to get the rst.

    If you are in excel. You don't need to create the Excel app,since you are in excel.

    I'd drop the dao.
    Set rst= currentdb.openrecordset.....

  3. #3
    Join Date
    Mar 2017
    Posts
    2

    DAO Not Necessary, Code Still Does Not Work

    I am not in Excel, so I have to call Excel.

    I initially did not call DAO or ADODB. I only tried it because it was a suggestion. I don't understand why the code worked beforehand, and then all of a sudden stopped.
    But, I had a thought that perhaps since the database it bigger, the error may be simply related to the fact that the query is a query make table. So, I simply changed the code to run the query, then save the data from the table to the spreadsheet. Unfortunately, I receive the error: Type Mismatch. I am sure it is a syntax issue, but I have been staring at this database so long, I am sure I am easily missing something that would have normally stood out clearly.

    Private Sub cmd_Incomplete_Export_old_Click()
    On Error GoTo Err_cmd_Incomplete_Export_old_Click

    'Must have checked Tools-References-MS Excel 14.0 Object Library

    'Variables used for Query
    Dim rs_CBI_eLMS As Recordset
    Dim strDocName As String

    'Table created is: tbl_For_CBI_eLMS_Rpt
    Dim intCount As Integer

    strDocName = "qry_CBI_eLMS"
    DoCmd.OpenQuery strDocName, acNormal, acEdit

    MsgBox "You have run the query", vbInformation, "Variables reached"

    'Executes the query and saves the recordset to rs_CBI_eLMS
    Set rs_CBI_eLMS = CurrentDb.OpenRecordset("tbl_For_CBI_eLMS_Rpt", dbOpenDynaset)
    'rs_CBI_eLMS.Open qry_CBI_eLMS, cnString, adOpenStatic, adLockOptimistic

    'Tests to see if Recordset exists in the DB


    If rs_CBI_eLMS.RecordCount = 0 Then
    MsgBox "There are no entries for this week."

    Else
    'Excel Variables
    Dim XL As Object
    Dim wbTarget As Excel.Workbook

    'Converts the new data queried to Excel Spreadsheet
    Set XL = CreateObject("Excel.application")

    'Set reference to the export target Workbook
    Set wbTarget = XL.Workbooks.Open("W:\AirTraffic\Training\eLMS\Inc omplete\Incomplete-CBI.xlsx")
    wbTarget.Visible = True

    'Clear worksheet cells of all values
    wbTarget.Sheets("Incomplete-CBI").Select
    wbTarget.Sheets("Incomplete-CBI").Cells.ClearContents

    'Do While Not rs_CBI_eLMS.EOF
    'Copy from recordset to paste in spreadsheet
    wbTarget.Sheets("Incomplete-CBI").Cells(1, 1).CopyFromRecordset rs_CBI_eLMS

    'wbTarget.Run

    'Loop

    'Saves then closes the Workbook
    wbTarget.Save
    wbTarget.Close

    'Closes the Query
    rs_CBI_eLMS.Close

    'Clear variables
    'Set qdf_CBI_eLMS = Nothing
    Set wbTarget = Nothing
    Set XL = Nothing

    'Pop up window confirms data export
    MsgBox "Your data has been exported", vbInformation, "Action Completed"

    End If

    Exit_cmd_Incomplete_Export_old_Click:
    Exit Sub

    Err_cmd_Incomplete_Export_old_Click:
    MsgBox Err.Description
    Resume Exit_cmd_Incomplete_Export_old_Click

    End Sub
    Last edited by kcaviatrix; 03-14-17 at 09:21. Reason: Forgot to repaste part of the code

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    What I do Is LINK The Query into a Execl File. I use the UNC path SO any one who open the execl file can Read it and I make That Sheet Hidden
    do More Maths If needed on a Differance Sheet
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Tags for this Thread

Posting Permissions

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