Sorting Columns in Excel

[aziz rasul]

Does anyone have any VBA code using CreateObject to sort 3 columns in an existing Excel file for the worksheet called "Data"? The VBA code will be written in a MS Access module.

 

[boblarson]

Just a hint on this - Go into Excel and start the creation of a macro and then do exactly what you want to do. Then stop the macro and go into the VBA window of Excel to the module that the code went in and there you go - the code to do what you are asking (although you may have to tweak it slightly to fit in Access).

Give that a try first (that's how I have learned to do a lot of my Access to Excel programming).

 

[aziz rasul]

I actually tried that and got it to work i.e. referencing the Excel object library. However since I'm dealing with a shared db and each user has a slightly different path to the object library reference (Excel.exe) which gives an error to some of the user's as the code can't find the object library reference. Here's the working code: -

Public Sub SortColumn(strExcelFile As String, strWorksheet As String)

    Dim objExcelApp As Excel.Application
    Dim ws As Excel.Worksheet

    Set objExcelApp = New Excel.Application
    
    With objExcelApp
        .Workbooks.Open FileName:=strExcelFile
        .Visible = False
        For Each ws In .Worksheets
            If ws.Name = strWorksheet Then
                .Range("A1:E" & .ActiveSheet.UsedRange.Rows.Count).Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range( _
                "B2"), Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, _
                Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
                xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
                .Range("A1").Select
            End If
        Next ws
        .DisplayAlerts = False
        .ActiveWorkbook.SaveAs strExcelFile
        .Quit
        .DisplayAlerts = True
    End With
    
    Set objExcelApp = Nothing

End Sub

To resolve the issue, I've removed the reference to the object library reference and using CreateObject to do the same thing. Here's the unworking code: -

Public Sub SortColumn(strExcelFile As String, strWorksheet As String)

    Dim objExcelApp As Object
    Dim ws As Object

    Set objExcelApp = CreateObject("Excel.Application")
    objExcelApp.Workbooks.Open strExcelFile
    objExcelApp.Visible = True
    
    With objExcelApp
        For Each ws In .Worksheets
            If ws.Name = strWorksheet Then
[COLOR="Red"]                .Range("A1:E" & .ActiveSheet.UsedRange.Rows.Count).Sort Key1:=ActiveSheet.Range("A2"), Order1:=xlAscending, Key2:=ActiveSheet.Range( _
                "B2"), Order2:=xlAscending, Key3:=ActiveSheet.Range("D2"), Order3:=xlAscending, _
                Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
                xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
                .Range("A1").Select[/COLOR]            
            End If
        Next ws
        .DisplayAlerts = False
        .ActiveWorkbook.SaveAs strExcelFile
        .Quit
        .DisplayAlerts = True
    End With
    
    Set objExcelApp = Nothing

End Sub

However I get run time error 424 (Object required) within the If ... End If section. I've spent a lot of time yesterday, but can't figure it out.

 

[Rabbie]

Have a look at using late binding rather than early binding if users have different paths to Excel.

 

[aziz rasul]

Can you expand?

 

[Rabbie]

Search this forim for "late binding". Search Access Help for "Late Binding". Do a google search.

 

[aziz rasul]

I think I'm using late binding already. I amended the code, as below with the object library reference in and it works. But as soon as I remove the reference, it doesn't like it. Comes up with the error 'Sort method of Range class failed'. I think I have to change some of the constants within the If ... End IF statement to numerical values. I have tried 0, -1, 1, for xlAscending but no luck. Any help would be appreciated as I'm getting desparate.

Public Sub SortColumn(strExcelFile As String, strWorksheet As String)

    Dim objExcelApp As Object
    Dim objWorkBook As Object
    Dim objWorkSheet As Object

    Set objExcelApp = CreateObject("Excel.Application")
    Set objWorkBook = objExcelApp.Workbooks.Open(strExcelFile)
    objExcelApp.Visible = True
   
    For Each objWorkSheet In objWorkBook.Worksheets
        If objWorkSheet.Name = strWorksheet Then
            objWorkSheet.Range("A1:E" & objWorkSheet.UsedRange.Rows.Count).Sort Key1:=objWorkSheet.Range("A2"), Order1:=xlAscending, Key2:=objWorkSheet.Range( _
            "B2"), Order2:=xlAscending, Key3:=objWorkSheet.Range("D2"), Order3:=xlAscending, _
            Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
            xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
            objWorkSheet.Range("A1").Select
        End If
    Next objWorkSheet
    
    With objExcelApp
        .DisplayAlerts = False
        .ActiveWorkbook.SaveAs strExcelFile
        .Quit
        .DisplayAlerts = True
    End With
    
    Set objWorkBook = Nothing
    Set objExcelApp = Nothing

End Sub

 

[aziz rasul]

If I use

objWorkSheet.Range("A1:E" & objWorkSheet.UsedRange.Rows.Count).Sort Key1:=objWorkSheet.Range("A2") ', Order1:=xlAscending
If I include Order1:=xlAscending in the code, I get run time error 1004 'Sort method of Range class failed'.

How do I rectify the code so I can sort in Ascending or Descending order?

 

[aziz rasul]

I managed to get it working. Here's the code.

Public Sub SortColumn(strExcelFile As String, strWorksheet As String)

Dim objExcelApp As Object
Dim objWorkBook As Object
Dim objWorkSheet As Object

Set objExcelApp = CreateObject("Excel.Application")
Set objWorkBook = objExcelApp.Workbooks.Open(strExcelFile)
objExcelApp.Visible = True

For Each objWorkSheet In objWorkBook.Worksheets
If objWorkSheet.Name = strWorksheet Then
objWorkSheet.Range("A2:E" & objWorkSheet.UsedRange.Rows.Count).Sort _
Key1:=objWorkSheet.Range("A2"), _
Key2:=objWorkSheet.Range("B2"), _
Key3:=objWorkSheet.Range("D2"), _
Order1:=1, Order2:=1, Order3:=1 'For descending, use 2.
objWorkSheet.Range("A1").Select
End If
Next objWorkSheet

With objExcelApp
.DisplayAlerts = False
.ActiveWorkbook.SaveAs strExcelFile
.Quit
.DisplayAlerts = True
End With

Set objWorkBook = Nothing
Set objExcelApp = Nothing

End Sub

 

[boblarson]

Glad to hear you got it working. I have used that method of getting code for Excel (and Word) many, many times before and then I work with it to get rid of the extra stuff that the macro recorder includes that isn't necessary. But, it's a great way to get some syntax when you just aren't sure.

 

[aziz rasul]

In this case I got the code from the macro recorder, but unfortunatley the resulting code is normally more amenable when you have a reference to the Excel object library in the MS Access module.

In this case I didn't want the reference to be there as it was a shared db, hence I had to use late binding i.e. use CreateObject. The code for sorting columns is different to that used in the macro generated VBA code. That essentially was what was causing the problem e.g.

Order1:=1, Order2:=1, Order3:=1

was the bit of code I couldn't work out. Hope this helps others in the future.

 

[boblarson]

Yeah, sometimes it takes a LOT of tweaking to get there but I've found that usually, if I get the code out of Excel, I can usually figure out how to modify it to work in Access, but I wouldn't have been on the right track without the initial assistance from the Excel code.