Formatting Access Export to Excel

[derekben]

Hi,

I am looking to export a table to excel from access. I would like to order the transaction category column in a specific order(round trip air far, parking, lodging etc),. I have a button that runs a make query table and exports it to excel. I would like the rows to be in the order of transactions category. What code would I need in the button to make this order correct? If you need any extra information, PLEASE ASK!! ANY HELP WOULD BE AMAZING! THANK YOU.

I have attached some code below. Let me know if you need any extra explanations. THANKS!

-Derek


Private Sub ExportDebitsButton_Click()
Dim oApp As Excel.Application
Dim oWB As Excel.Workbook
Dim i As Integer
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String

DoCmd.OpenQuery "Export Debits to Excel Query", acViewNormal, acEdit

With DoCmd
.SetWarnings False
.OpenQuery "Export Debits to Excel Query"
.SetWarnings True
End With



'Create an instance of Excel and add a new blank workbook
sSQL = "SELECT * FROM [Export Debits to Excel Table]"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)


Set oApp = New Excel.Application
oApp.Visible = False
Set oWB = oApp.Workbooks.Add


'Add the field names as column headers (optional)
For i = 0 To rst.Fields.Count - 1
oWB.Sheets(1).Cells(1, i + 1).Value = rst.Fields(i).Name
Next


oWB.Sheets(1).Range("1:1").Font.Bold = True
oWB.Sheets(1).Cells(2, 1).CopyFromRecordset rst
oWB.Sheets(1).Columns.AutoFit


oWB.Sheets(1).Range("H1").Value = "RefNumber"


Dim x As Integer
x = oWB.Sheets(1).UsedRange.Rows.Count - 1




For i = 1 To x
oWB.Sheets(1).Range("H" & i + 1) = oWB.Sheets(1).Range("H2") + i
Next

'Clean up ADO Objects
rst.Close
Set rst = Nothing


'Create a folder if not exist
Dim strFilePath As String
Dim strFolder As String
strFolder = "C:\My Documents"
strFilePath = strFolder & "\AMEX_Debits_" & Format(Now(), "mm-dd-yyyy") & ".xlsx"


Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(strFolder) Then
'Create the file
FileSystem.MkDir (strFolder)
End If


'Clean up Excel Objects
oWB.Close SaveChanges:=True, FileName:=strFilePath
Set oWB = Nothing
oApp.Quit
Set oApp = Nothing


'Open the file after export to excel
Shell "EXCEL.EXE """ & strFilePath & "", vbNormalFocus




End Sub

[June7]

Why do you make table for the export?

Export a query that has an ORDER BY clause.