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