Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
Keywords: Sort Range Excel
XLFileText = "C:\CBSC\TrAFIc\024\024.txt" XLFileXls = "C:\CBSC\TrAFIc\024\024.xls" xlFileFormat = 43 ; xls FileFormat Office XP = 43; Office 95/97 = 6 xlDown = -4121 xlToRight = -4161 XL = ObjectOpen("Excel.Application") XL.Visible = @True XL.DisplayAlerts = @FALSE WB = XL.Workbooks WB.OpenText(XLFileText,932,1,1,1,@false,@true,@true,@false,@false,@false) WS = XL.Worksheets(1) WS.Activate WS.SaveAs(XLFileXls,xlFileFormat, , , , ,@False) SR = WS.Range ("A6") SR.Select ;This does not work SR.End(xlDown) SR.Select SR.End(xlToRight) SR.Select ; This line returns an OLE Exception probably because ; the above End.Select statements do not work ! SR.Sort("G6", 2, 0, 1, @False, 1, 0) ;Macro produced by VBA to Sort a variable Range of Rows and Columns ;Range("A6").Select ; Range(Selection, Selection.End(xlDown)).Select ; Range(Selection, Selection.End(xlToRight)).Select ; Selection.Sort Key1:=Range("G6"), Order1:=xlDescending, Header:=xlGuess, _ ; OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ ; DataOption1:=xlSortNormal Row = 1 Range = WS.Range("A6:G9") WS.Select For Row = 1 to 4 Data = "" Cell = Range.Cells(%Row%,7) Data = Cell.Value ;Real code to eventually replace this message message("","Row=%Row%, Data=%Data%") Next message("End of Test","") WB.Close() XL.Quit ObjectClose(XL) Exit
XLFileText = "C:\CBSC\TrAFIc\024\024.txt" XLFileXls = "C:\CBSC\TrAFIc\024\024.xls" xlFileFormat = 43 ; xls FileFormat Office XP = 43; Office 95/97 = 6 xlDown = -4121 xlToRight = -4161 XL = ObjectOpen("Excel.Application") XL.Visible = @True XL.DisplayAlerts = @FALSE WB = XL.Workbooks WB.OpenText(XLFileText,932,1,1,1,@false,@true,@true,@false,@false,@false) WS = XL.Worksheets(1) WS.Activate WS.SaveAs(XLFileXls,xlFileFormat, , , , ,@False) SR = WS.Range ("A6") SR.Select selection = XL.Selection col1 = Selection.End(xlDown) range1 = WS.Range(Selection, col1 ) range1.Select selection = XL.Selection col2 = Selection.End(xlToRight) range2 = WS.Range(Selection, col2) range2.Select selection = XL.Selection xlTopToBottom = 1 xlGuess = 0 xlDescending = 2 rangeobj = WS.Range("G6") Selection.Sort(:: Key1=rangeobj, Order1=%xlDescending%, Header=%xlGuess%, OrderCustom=1, MatchCase=0, Orientation=%xlTopToBottom%) Row = 1 Range = WS.Range("A6:G9") WS.Select For Row = 1 to 4 Data = "" Cell = Range.Cells(%Row%,7) Data = Cell.Value ;Real code to eventually replace this message message("","Row=%Row%, Data=%Data%") Next message("End of Test","") WB.Close() XL.Quit ObjectClose(XL) Exit
Article ID: W15644
File Created: 2003:05:13:11:29:20
Last Updated: 2003:05:13:11:29:20