Results 1 to 8 of 8
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    Object Required - Xferring to Specific Excel Sheet


    Good Morning. I am trying to transfer a query to a specific worksheet in Excel. After surfing the forums, I have found this sub (modified for my purpose).

    Code:
    Private Sub cmdGo1_Click() ' Contract Report - Invoicing
    Dim CountProc As Integer
    Dim strRptSQL1 As String
    Dim db As DAO.Database
    Dim rs As Recordset
    Set db = CurrentDb
    dt1 = Me.tbxDate1
    dt2 = Me.tbxDate2
    
    
    strRptSQL1 = "SELECT * FROM WOTracking WHERE ContractCo = '" & Me.cbxContractCo & "' AND  Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "# GROUP BY OrderNo;"
    
    
    Set rs = dbs.OpenRecordset(strRptSQL1)
    
    
    Set excelApp = CreateObject("Excel.application", "")
    excelApp.Visible = True
    Set targetWorkbook = excelApp.workbooks.Open("E:\Databases-DO NOT MOVE\LE Tracking System\InvoicingReport.xlsx")
    targetWorkbook.Worksheets("InvoiceRaw").Range("A1").CopyFromRecordset rsQuery
    
    
    End Sub
    I think I may not have defined something, as I am getting an Object Required error on the highlighted line.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You set db, not dbs.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Opps. That's what I get for Copy and Paste. Continuing on this sub, here is an new one. It initially said that I could not GROUP BY when using a SELECT *. So I changed to individual fields.

    Code:
    strRptSQL1 = "SELECT OrderNo, SKU, Date_Time, SKUQty, Process, ContractCo FROM WOTracking WHERE ContractCo = '" & Me.cbxContractCo & "' AND  Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "# GROUP BY OrderNo;"
    
    
    Set rs = db.OpenRecordset(strRptSQL1)
    Now I get: The query does not include the specified expression 'SKU' as part of an aggregate function.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Why are you grouping? Basically, every field in the SELECT clause must either be in the GROUP BY clause or have an aggregate function applied to it (Sum, Max, etc).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Multi Item orders are logged as each item is completed. I simply wanted to put the each item on an order together. Example: Item 1 uses Process A, Item 2 uses Process B, Item 3 uses Process D. Each is logged in the system individually, with other orders in between them. Maybe I should be using ORDER BY, so that the same order numbers are listed together?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd start with that, changing GROUP BY to ORDER BY. If that isn't is, perhaps some sample data and your desired result. Typically you only use GROUP BY when you're using aggregate functions. Like you want the total amount per order.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    That did it. As always, thanks for the insight.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 03-11-2015, 08:07 AM
  2. Replies: 2
    Last Post: 12-12-2014, 04:42 PM
  3. Cant Export data to specific excel sheet by ADODB
    By rangga_osh in forum Import/Export Data
    Replies: 4
    Last Post: 03-14-2013, 04:20 AM
  4. Export Query to specific excel sheet
    By Perceptus in forum Queries
    Replies: 2
    Last Post: 12-20-2012, 11:50 AM
  5. Exporting to Specific Excel Sheet
    By unrealtb in forum Access
    Replies: 2
    Last Post: 01-24-2012, 10:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums