Solved Export and Format Excel

[MattBaldry]

Hi all,

Does anyone know the code to sort by a column in excel after exporting? At the moment I have the below code, which works perfect and formats it how I need, but I cannot get it to sort by Column G, asc.

    Dim xlApp As Object
    Dim xlSheet As Object

    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
    
    With xlApp
            .Application.Sheets("Open_Works_Order_Report").Select
            .Application.Cells.Select
            .Application.Selection.ClearFormats
            .Application.Rows("1:1").Select
            .Application.Selection.Font.Bold = True
            .Application.Cells.Select
            .Application.Selection.RowHeight = 17.5
            .Application.Selection.VerticalAlignment = xlCenter
            .Application.Range("A2").Select
            .Application.ActiveWindow.FreezePanes = True
            .Application.Range("A1:H1").Select
            .Application.Selection.Interior.ColorIndex = 15
            .Application.Range("A:A").Select
            .Application.Selection.ColumnWidth = 25
            .Application.Range("B:B").Select
            .Application.Selection.ColumnWidth = 13
            .Application.Range("C:C").Select
            .Application.Selection.ColumnWidth = 40
            .Application.Range("D:D").Select
            .Application.Selection.ColumnWidth = 60
            .Application.Range("E:F").Select
            .Application.Selection.ColumnWidth = 13
            .Application.Range("G:G").Select
            .Application.Selection.ColumnWidth = 15
            .Application.Selection.NumberFormat = "mm/dd/yyyy"
            .Application.Range("H:H").Select
            .Application.Selection.ColumnWidth = 80
            .Application.Range("E:G").Select
            .Application.Selection.HorizontalAlignment = xlRight
            .Application.Range("A:G").AutoFilter
            .Application.Cells.Select
            .Application.Range("A:H").Sort Key1:=.Application.Range("G2"), Order1:=1, Header:=1
            .Application.Range("A1").Select
            .Application.Activeworkbook.Save
            .Application.Activeworkbook.Close
            MsgBox "Your file is ready for you on your desktop.", vbInformation, "File Ready"
            .Quit
    End With

    Set xlApp = Nothing
    Set xlSheet = Nothing

Everything works apart from my sort line. Has anyone got a working code they can share?

~Matt

 

[Gasman]

I record the VBA with Excel Record Macro. Then amend to suit.

That resulted in code like below.

    ActiveWorkbook.Worksheets("Passengers").Sort.SortFields.Clear
    With ActiveWorkbook.Worksheets("Passengers").Sort
        '.SortFields.Add Key:=Range("L1"), Order:=xlDescending
        .SortFields.Add Key:=Range("I1"), Order:=xlAscending
        .SortFields.Add Key:=Range("J1"), Order:=xlAscending
        .SetRange Range("A1:L" & iLastRow) 'Skip the columns for old passengers.
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[MattBaldry]

I would sort the query before exporting.

Although, I do the same thing as Gasman when I need to automate Word or Excel.

I did this in the end. The issue I had was the Date field imported from Sage50 was not sorting by date, rather by number.

It meant 01/08/23 was followed by 01/09/23 and so on. I had to break the date down into the parts of the field then sort (but not show) those on the query before exporting to Excel. Was a pain, but it worked.

Quite why Sage50 does things the way it does I will never know.

~Matt

 

[Gasman]

Use CDate() ?

 

[MattBaldry]

Sage, along with similar products claims to be all things to all people. That means, it is terrible at pretty much everything, but the marketing material sells "management" because they don't have a clue.

The "date" wasn't sorting like a date because it was a string. If you think 1/9/23 following 1/8/23 is wrong, it must be because you think the string is dd/mm/yyyy. If cDate() doesn't solve the problem, then you're stuck with using Format() to reconstruct the fields.

It is best to solve the problem when you import the data. Once it is imported into an actual Date data type column, it will act like a date instead of a string.

cDate() worked perfectly. I wish I had known this before.

I had thought about doing and import to a new table and setting the field data type, but the query runs in a fraction of a second linking direct to the Sage data, so I didn't see the value in doing an import to local, then running the query each time the data needed to be sent to Excel.

Thanks for your help Gasman and Pat, cDate() is my new best friend on Sage "date" fields (but I still hate Sage)

~Matt

 

[Gasman]

cDate() worked perfectly. I wish I had known this before.

I had thought about doing and import to a new table and setting the field data type, but the query runs in a fraction of a second linking direct to the Sage data, so I didn't see the value in doing an import to local, then running the query each time the data needed to be sent to Excel.

Thanks for your help Gasman and Pat, cDate() is my new best friend on Sage "date" fields (but I still hate Sage)

~Matt

Don't bother looking at Xero then.