Member Avatar for pingpongplaya

I'm creating an Excel spreadsheet from within Microsoft Access.

This is declared at the top:

Public OpenExcel As Object
Public OpenSheet As Object

This is in my sub-routine:

Set OpenExcel = CreateObject("Excel.Application")
OpenExcel.Visible = False
With OpenExcel
   .Application.DisplayAlerts = False
   .Workbooks.Add
   .ActiveWindow.DisplayGridlines = False
   .ActiveWorkbook.SaveAs FileName:="" & "C:\test.xls"
   .Visible = True
   .Windows("test.xls").Activate
   .ActiveWindow.Zoom = 100
   .Sheets("Sheet1").Select
   .Sheets("Sheet1").Name = "testing"

   'BUNCH OF .Range("[insert cell here]").Select/ActiveCell.FormulaR1C1 LINES
   'ETC
   'ETC
   'ETC

My question is, after I have populated my spreadsheet with data, what is the correct line of code to have this spreadsheet sorted?

I tried using the code by recording a macro in excel, but not sure how to get it to work with the "With OpenExcel" format.

From the recorded macro...

Rows("4:137").Select
    ActiveWorkbook.Worksheets("testing").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("testing").Sort.SortFields.Add Key:=Range( _
        "B4:B137"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("testing").Sort
        .SetRange Range("B4:P137")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B4").Select
Edited by pingpongplaya because: n/a
  • 2 Contributors
  • 2 Replies
  • 927 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by pingpongplaya
Member Avatar for AndreRet

HAve a look at THIS sample, might solve your problem.

Member Avatar for pingpongplaya

HAve a look at THIS sample, might solve your problem.

Thanks for the reply. I got my code to work... but it only works every other time. I receive the error:

"run-time error '1004'
method 'range' of object'_global' failed"

Can someone help explain why the sorting works only half the time? I would rather learn what is going wrong than just copying and pasting someone else's code into my project. Thanks.

I changed the code from the recorded macro to the following which produced the above results:

.Rows("4:137").Select
            ActiveWorkbook.Worksheets("testing").Sort.SortFields.Add Key:=Range( _
                "B4:B137"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                xlSortNormal
            .ActiveSheet.Sort.SortFields.Add Key:=Range( _
                "B4:B137"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                xlSortNormal
            With ActiveWorkbook.Worksheets("testing").Sort
                .SetRange Range("B4:P137")
                .Header = xlGuess
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
Edited by pingpongplaya because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.