Solved

How do I vba copy from 2 Access recordsets and paste on 2 different Excel sheets in the same open workbook?

Posted on 2015-02-10
2
189 Views
Last Modified: 2016-02-10
I don't think this is a tough one; probably not declaring the target sheets right.  Here's my code.  It works, generally, but pastes the same rsResults onto the "Tracking" sheet.  I need rsResults to go on one sheet and rsResults2 to go on another.
Public Sub ProcessSheets()

    Dim sPath As String, xlFile As String
    sPath = "C:\Users\jw51640\Desktop\Pick\Refresh\"
    xlFile = Dir(sPath & "*.xlsm")
    Dim XL As Excel.Application, wbTarget As Workbook
    Dim qdfResults As QueryDef
    Dim rsResults As DAO.Recordset
    Dim qdfResults2 As QueryDef
    Dim rsResults2 As DAO.Recordset
    
    Set XL = New Excel.Application
    Set wbTarget = XL.Workbooks.Open("C:\Users\jw51640\Desktop\Pick\PickListTemplate.xlsm")
    Set qdfResults = CurrentDb.QueryDefs("PickListDataQ")
    Set rsResults = qdfResults.OpenRecordset()
    Set qdfResults2 = CurrentDb.QueryDefs("TrackingQ")
    Set rsResults2 = qdfResults.OpenRecordset()
    
    Debug.Print xlFile
'Import and delete the data file
    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ProcessingT", sPath & xlFile, True, "PickList!A2:U5000"
    DoCmd.OpenQuery "DeleteProcessingTBlanksQ", , acReadOnly
    Kill sPath & xlFile
   xlFile = Dir()
'Process
    DoCmd.SetWarnings False
'Updates Archetypes with matching PID
    DoCmd.OpenQuery "UpdatePicksQ", , acReadOnly
    DoCmd.OpenQuery "AppendNewPicksQ", , acReadOnly
    Call Snapshot
    
    XL.Visible = True
    wbTarget.Sheets("PickList").Range("A3").CopyFromRecordSet rsResults
    Set rsResults = Nothing
    XL.Run "SetPickListRowHeight"
    wbTarget.Sheets("Tracking").Range("A1").CopyFromRecordSet rsResults2 'this is where the bug is.
    Set rsResults2 = Nothing
    'XL.Run "SaveSendResults"
    'Set wbTarget = Nothing

'ClearProcessSheets
    DoCmd.RunSQL "DELETE * FROM ProcessingT"
    DoCmd.SetWarnings True
Set db = Nothing

Open in new window

0
Comment
Question by:Jay Williams
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40600508
Hi,

pls correct

    Set rsResults2 = qdfResults.OpenRecordset()

to
    Set rsResults2 = qdfResults2.OpenRecordset()

Regards
0
 

Author Closing Comment

by:Jay Williams
ID: 40600517
That's the ticket!  Thanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question