How to Sort a Column VB Script?

Record ID#      Release Date      Related Project/SR      Concatenate
321      39521       P05-5649b                                 39521P05-5649b
322      39521      P06-6139c                          39521P06-6139c
323      39528      P06-6145a                       39528P06-6145a
325      39521      P07-7102a                     39521P07-7102a
326      39514      P07-7111a                      39514P07-7111a
327      39514      P07-7120b                  39514P07-7120b
328      39521      P07-7123b                         39521P07-7123b

Need VBScript to sort by Column D ascending?

Tried something like this got a subscript out of range error
Set objWorksheet2 = objExcel2nd.Sheets("ProjectSynch")

Set objRange = objWorksheet2.UsedRange
Set objRange2 = objExcel2nd.Range("D1")
objRange.Sort objRange2, xlAscending, , , , , , xlYes
justastefAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BadotzCommented:
Do this:

Tools->Macro->Record new macro

Then sort the columns manually

Tools->Macro->Stop recording

Open the VBA IDE (ALT-F11) and view the code.
0
justastefAuthor Commented:
  Columns("A:D").Select
    Range("D1").Activate
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

That is what the macro produces and since its not VBScript language its not working ... I don't need it in macro/excel language ... i need it in VBScript language.
Can anyone advise?
0
patrickabCommented:
Where are you running the VBScript from?
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

justastefAuthor Commented:
vbscript is stored on a network drive ... all other code of my script works fine.  just don't know how to sort.
0
BadotzCommented:
How can that not be VBScript?
0
justastefAuthor Commented:
Set objExcel2nd =  CreateObject("Excel.Application")
objExcel2nd.DisplayAlerts = False
Set secondwkbook = objExcel2nd.Workbooks.open("\\Midp-sfs-005\qaaim\QES Operations Team\Audit Effort\FileFeeds\ProjectSynch.csv")
objExcel2nd.Visible = true

thats the type of code I'm using

i am not directly opening the file myself or coding in a macro ... i code into a text file which is saved as  .vbs extention.  the script creates the instance of excel itself and does all of the functions on its own.  therefore want to tell the script after its opened that file to sort for column
0
patrickabCommented:
If you want to run it as a VBA macro change it to this:

Sub sorter()
    Sheets("Sheet1").Activate
    Sheets("Sheet1").Columns("D1:D20").Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub
0
justastefAuthor Commented:
do not want to run it as a vba macro

0
patrickabCommented:
>i am not directly opening the file myself or coding in a macro...

I'm not acquainted with VBScript but perhaps you can adapt the VBA to suit.
0
patrickabCommented:
Why not get the VBScript to call a VBA Excel macro as a sub-routine?
0
justastefAuthor Commented:
thanks anyway i need it in VBS ... cannot call a macro ... i would just code in the vba macro if I could but
i am working with a file someone else already created and it is kicked off by an autoscheduler task to run all by itself and import schema into an online system database which can only be accessed via vbscript login

that is why Its a requirement i have this coded in VBScript.  Thanks for trying anyway...

For those that know how to code VBScript anyone know why this is bombing out in the code?

Set objWorksheet2 = objExcel2nd.Sheets("ProjectSynch")

Set objRange = objWorksheet2.UsedRange
Set objRange2 = objExcel2nd.Range("D1")
objRange.Sort objRange2, xlAscending, , , , , , xlYes
0
justastefAuthor Commented:
Got it


Const xlAscending = 1
Const xlYes = 1

constants were not set to appropriate numbers
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VivaChileCommented:
TRY THIS:
Sheets("ProjectSynch").Activate
ActiveSheet.UsedRange.Select
Range2 = "d1"
Selection.Sort Key1:=Range(Range2) ....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.