asked on

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

Badotz

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.

justastef

ASKER

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?

patrickab

Where are you running the VBScript from?

justastef

ASKER

vbscript is stored on a network drive ... all other code of my script works fine. just don't know how to sort.

Badotz

How can that not be VBScript?

justastef

ASKER

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

patrickab

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

justastef

ASKER

do not want to run it as a vba macro

patrickab

>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.

patrickab

Why not get the VBScript to call a VBA Excel macro as a sub-routine?

justastef

ASKER

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

ASKER CERTIFIED SOLUTION

justastef

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

VivaChile

TRY THIS:
Sheets("ProjectSynch").Activate
ActiveSheet.UsedRange.Select
Range2 = "d1"
Selection.Sort Key1:=Range(Range2) ....