Jay Williams
asked on
How do I vba copy from 2 Access recordsets and paste on 2 different Excel sheets in the same open workbook?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER