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.
Code:
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