troubleshooting Question

Need VBA syntax to import to a specific sheet a Microsoft Access VBA Recordset?

Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America asked on
Microsoft AccessVBA
4 Comments1 Solution73 ViewsLast Modified:
Need help with exporting recordset to correct worksheet using Access/ Excel VBA:
Please note the following code.

I'm trying to get the recordset on the first worksheet - Sheet1 and it does put the data in there
But I'm trying to skip over to Sheet 3 - and it won't go to the third sheet in the workbook - it instead still - STILL puts in in Sheet 1 over the previous recordset.



Private Sub bSendExcel_Click()

  Dim sFileName As String
  Dim objApp As Excel.Application
  Dim wb As Excel.Workbook
  Dim ws As Excel.Worksheet
  sFileName = txtPath
  
  'CLOSE OUT OF ALL EXCEL APPLICATIONS
  'if open - remind to save and close out

  
  Dim rs As DAO.Recordset
  Set objApp = CreateObject("Excel.Application")
  objApp.Visible = True
  Set wb = objApp.Workbooks.Open(sFileName, True, False)
  Set rs = CurrentDb.OpenRecordset("qMonthlyWorkloadbyAsset_Crosstab")
  Set ws = wb.Sheets(1)
  For Each ws In wb.Worksheets
      With ws
          .Activate
          .Cells(2, 1).CopyFromRecordset rs
      End With
  Next
 
  rs.Close
  Set rs = Nothing

'this is the second recordset... that still puts in Sheet 1 - even though I specify wb.Sheets(3)
  
'  Set rs = CurrentDb.OpenRecordset("_02_Final Hours Worked by Customer")
'  Set ws = wb.Sheets(3)
'  For Each ws In wb.Worksheets
'      With ws
'          .Activate
'          .Cells(2, 1).CopyFromRecordset rs
'      End With
'  Next
'
  wb.Save
  Set wb = Nothing
  objApp.Quit
  Set objApp = Nothing


End Sub
ASKER CERTIFIED SOLUTION
stephenlecomptejr

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros