Recordset with paramter query and export it to excel

[jj1]

Hi



I am trying to run this recordset with parameter query and export to excel. Problem I am getting is it is sending the ID of employee name and exporting row wise instead of column wise.

  Dim dbMyDatabase As DAO.Database
   Dim rsMyRecords1 As DAO.Recordset
   Dim qdMyParameters1 As DAO.QueryDef
   Dim iIndex As Integer
   Dim iFound As Integer
   Dim oExcel As New Excel.Application
   Dim oWb As Excel.Workbook
   Dim oSheet As Excel.Worksheet
   If IsNull(Me.cbozoneteamshift) Then
      MsgBox ("No valid Zone/Team/Shift selected")
      'GoTo 500
   End If
   oExcel.Workbooks.Open (CurrentProject.Path & "\" & "JobMatrix.xlsx")
   oExcel.Visible = True
   Set oWb = oExcel.Workbooks(1)
   Set oSheet = oExcel.ActiveWorkbook.Worksheets("ROTC")
   oSheet.Unprotect ("qwerty")
   Set dbMyDatabase = CurrentDb()
   oSheet.Cells(3, 3) = Forms!JobMatrix!cbozoneteamshift.Column(1)
   oSheet.Cells(4, 3) = Date
   Set rsMyRecords1 = dbMyDatabase.OpenRecordset("tblZone/EmployeeJunction")
   Set qdMyParameters1 = dbMyDatabase.QueryDefs("EmployeesByZone")
   qdMyParameters1.Parameters(0) = _
       Forms!JobMatrix!cbozoneteamshift
   Set rsMyRecords1 = qdMyParameters1.OpenRecordset
 

   iIndex = 0
   Do
      With oSheet
         .Cells(8, 3 + iIndex) = rsMyRecords1("EmployeeID")
      End With

      iIndex = iIndex + 1
      rsMyRecords1.MoveNext
   Loop Until rsMyRecords1.EOF = True
   iIndex = 0
   iFound = 0
   iMax = rsMyRecords1.RecordCount
        oSheet.Protect ("qwerty")
        Set oSheet = Nothing
        Set oWb = Nothing
        Set oExcel = Nothing
      rsMyRecords1.Close
      qdMyParameters1.Close
      dbMyDatabase.Close
End Sub

[June7]

That is odd because the Cells method is incrementing the column index.

[jj1]

and also when name values are populated they are populating as ID instead of name.

[June7]

Not seeing where code populates name, just EmployeeID.