Photo Gallery Member List Search Calendars FAQ Ticket List Log Out


Converting sort macro to VBS usable code ?

  		Logged in as: Guest
arrSession:exec spGetSession 2,2,36317
Active Users: There are 0 members and 0 guests.
Users viewing this topic: none
 

 

  
 
  Printable Version
All Forums >> [Scripting] >> WSH & Client Side VBScript >> Converting sort macro to VBS usable code ?
  Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks! 		Page: [1]
Login
Message << Older Topic   Newer Topic >>
Converting sort macro to VBS usable code ? - 7/19/2006 4:56:54 PM   
  takeda kozo

 

Posts: 83
Score: 0
Joined: 11/9/2005
Status: offline 		OK, i've added in some bits and peices, but I've now come across some code from my macro recorder which I'm having trouble using in my script

Any ideas ?


+++++++++++++++++++++++++

Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
oExcel.DisplayAlerts = False
Set oWorkbook = oExcel.Workbooks.Add
Set oWorksheet = oWorkbook.Worksheets(1)

oWorksheet.Cells(1, 1).Value = "HOSTNAMES"

oExcel.ActiveWindow.Zoom = 75

oExcel.Cells.Select

' +++++++++++++++++++++++++++++++++++++++++++++++

' THIS IS THE PROBLEM SECTION

Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
       OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
       DataOption1:=xlSortNormal

' +++++++++++++++++++++++++++++++++++++++++++++++

oWorkbook.SaveAs("c:\temp\report.xls")
oExcel.Quit

+++++++++++++++++++++++++
 
 
Post #: 1
 
RE: Converting sort macro to VBS usable code ? - 7/19/2006 5:41:42 PM   
  ginolard


Posts: 1082
Score: 21
Joined: 8/10/2005
Status: offline 		Wow.  What a coincidence.  I was just facing this exact same problem yesterday.

Replace the VBA code with this

Set objRange = objWorksheetMovies.UsedRange
Set objRange2 = objExcel.Range("B2")
objRange.Sort objRange2,1,,,1,,1,1,1,False,1

_____________________________

Author of ManagePC - http://managepc.net
AD Query Template - http://www.visualbasicscript.com/m_40609/tm.htm
Consolidated Scripting Framework - http://www.visualbasicscript.com/m_59109/tm.htm

(in reply to takeda kozo)
 
 
Post #: 2
 
RE: Converting sort macro to VBS usable code ? - 7/19/2006 6:09:38 PM   
  takeda kozo

 

Posts: 83
Score: 0
Joined: 11/9/2005
Status: offline 		Any idea how to change the sort order on columb B ?

From ascending to descending .. or vice versa

(The code below works, ie: sorts the data, but i just want to change the direction)

Do you simply delete a "1" to change the sort order ?

Or insert a "0" ?

Or what ?


+++++++++++++++

oExcel.Cells.Select
oExcel.Selection.Sort oExcel.Range("B2"),1,,,1,,1,1,1,False,1

+++++++++++++++

(in reply to ginolard)
 
 
Post #: 3
 
RE: Converting sort macro to VBS usable code ? - 7/19/2006 8:10:06 PM   
  ginolard


Posts: 1082
Score: 21
Joined: 8/10/2005
Status: offline 		In short, no ;)  I would imagine that 0 is descending though

_____________________________

Author of ManagePC - http://managepc.net
AD Query Template - http://www.visualbasicscript.com/m_40609/tm.htm
Consolidated Scripting Framework - http://www.visualbasicscript.com/m_59109/tm.htm

(in reply to takeda kozo)
 
 
Post #: 4
 
RE: Converting sort macro to VBS usable code ? - 7/19/2006 9:46:00 PM   
  ehvbs

 

Posts: 2222
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		If I'd use the Excel Docs and the "immediate window" (or the Object Catalog)
instead of imagination, I'd come up with:

  xlAscendig = 1
  xlDescending = 2

  oExcel.Selection.Sort oExcel.Range("B2"),xlDescending,,,1,,1,1,1,False,1

(in reply to ginolard)
 
 
Post #: 5
 
RE: Converting sort macro to VBS usable code ? - 7/19/2006 10:41:25 PM   
  ginolard


Posts: 1082
Score: 21
Joined: 8/10/2005
Status: offline 		Well, it was either going to be 0 or 2.  Figures I'd get my 50-50 guess wrong...

_____________________________

Author of ManagePC - http://managepc.net
AD Query Template - http://www.visualbasicscript.com/m_40609/tm.htm
Consolidated Scripting Framework - http://www.visualbasicscript.com/m_59109/tm.htm

(in reply to ehvbs)
 
 
Post #: 6
 
RE: Converting sort macro to VBS usable code ? - 7/20/2006 11:51:27 AM   
  takeda kozo

 

Posts: 83
Score: 0
Joined: 11/9/2005
Status: offline 		Hi Ehvbs, thanks for your post, it worked perfectly.

Can you please explain to me how I use the following, so that I can do this myself next time ?

---> Excel Docs and the "immediate window" 
---> the Object Catalog

Thanks again for your help.


p.s. Ginolard, even though it was just your "imagination" (haha) ... I still appreciate your response. Thanks mate :)



(in reply to ginolard)
 
 
Post #: 7
 
RE: Converting sort macro to VBS usable code ? - 7/20/2006 4:07:26 PM   
  ehvbs

 

Posts: 2222
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		Hi takeda kozo,

    (1) Start Excel

    (2) Open VBA editor (ALT+F11, Extras|Macros|Visual Basic Editor)

    (3) Open Object Catalog (F2, View|Object Catalog)

    (4) Open Immediate Window (CTRL+G, View|Immediate Window)

    (5) Enter "xx.Sort" in IW, select "Sort", hit F1 -> Excel/VBA Docs

    (6) read (sorry, this is German, but you'll recognize the xl Constants)

         Order1   Variant optional. Zulässig ist eine der folgenden XlSortOrder-Konstanten: xlAscending
         oder xlDescending. Mit xlAscending wird Key1 in aufsteigender Reihenfolge sortiert. Mit
         xlDescending wird Key1 in absteigender Reihenfolge sortiert. Die Standardkonstante ist xlAscending.


   (7) In IW: delete "xx.Sort", enter "? xlDescending"


   (8) In OC: enter xlAscending in the Search Entry Field


I hope this is understandable despite the language differences.

(in reply to takeda kozo)
 
 
Post #: 8
 
RE: Converting sort macro to VBS usable code ? - 7/24/2006 12:47:10 PM   
  takeda kozo

 

Posts: 83
Score: 0
Joined: 11/9/2005
Status: offline 		Yes, I was able to decipher your message :) thank you

I am still having a problem with the sorting though. If I try and sort by just the one column, then it works fine....but....i would like to be able to use the 3 different sort orders

For example: Sort by Col A - Ascending Then Sort by Col B Descending then sort by Col C Ascending

This line is ok:  oExcel.Selection.Sort oExcel.Range("B2"),2,,,1,,1,1,1,False,1  '(sorts by col B - Descending)
This line is ok:  oExcel.Selection.Sort oExcel.Range("R2"),1,,,1,,1,1,1,False,1  '(sorts by col R - Ascending)

This line FAILS: oExcel.Selection.Sort oExcel.Range("R2"),1,("B2"),2,("F2"),1,,,1,,1,1,1,False,1

On the line that fails, I would like sort first by: Col R (Ascending) THEN by Col B (Descending) THEN by Col F (Ascending)

Thank you for your help so far

- TK


(in reply to ehvbs)
 
 
Post #: 9
 
RE: Converting sort macro to VBS usable code ? - 7/25/2006 12:08:31 AM   
  ehvbs

 

Posts: 2222
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		Hi takeda kozo,

you know that you need 'oExcel.Range("R2")' to specify the range R2; why do you
expect that simple '("B2")' will pass a range to the .Sort method? Why didn't you
use the macro recorder to get:

    Range("A1:C27").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
        , Order2:=xlDescending, Key3:=Range("C1"), Order3:=xlAscending, Header _
        :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

and work from that? Perhaps it will help to code in a more lucid (german:
übersichtlich) way:

  Const xlAscending = 1
  ...
  Dim rngR2 : Set rngR2 = oExcel.Range("R2")
  Dim rngB2 : Set rngB2 = oExcel.Range("B2")
  Dim rngF2 : Set rngF2 = oExcel.Range("F2")
  oExcel.Selection.Sort   rngR2, xlAscending _
                        , rngB2, xlDescendig _
                        , rngF2, xlAscending _
                        ,,,1,,1,1,1,False,1

(Not tested, I didn't even count the ","). By the way: please include the
error message in your posting.

(in reply to takeda kozo)
 
 
Post #: 10
 
 
 
 

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.
All Forums >> [Scripting] >> WSH & Client Side VBScript >> Converting sort macro to VBS usable code ? Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts




Forum Software © ASPPlayground.NET Advanced Edition 2.5.5 ANSI