With the following code, is it possible to apply a sort order ASC or DESC based on the JobID column ( Header name is JobID ) ?:
Code: Select all
Sub RunCycleQuery()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
'ProgressBar.Show
'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
(ActiveWorkbook.Path & "\Autoflow-Dash.mdb")
Set MyQueryDef = MyDatabase.QueryDefs("qryK2K")
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
'Step 5: Clear previous contents
Range("Cycle!A1:CC10000").ClearContents
'Step 6: Copy the recordset to Excel
Range("Cycle!A2").CopyFromRecordset MyRecordset
'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
Sheets("Cycle").Cells(, i).Value = MyRecordset.Fields(i - 1).Name
Next i
Sheets("Cycle").ListObjects.Add SourceType:=xlSrcRange, _
Source:=Sheets("Cycle").Range("A1").CurrentRegion, _
XLListObjectHasHeaders:=xlYes
End Sub