justastef
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("Projec tSynch")
Set objRange = objWorksheet2.UsedRange
Set objRange2 = objExcel2nd.Range("D1")
objRange.Sort objRange2, xlAscending, , , , , , xlYes
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("Projec
Set objRange = objWorksheet2.UsedRange
Set objRange2 = objExcel2nd.Range("D1")
objRange.Sort objRange2, xlAscending, , , , , , xlYes
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?
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?
Where are you running the VBScript from?
ASKER
vbscript is stored on a network drive ... all other code of my script works fine. just don't know how to sort.
How can that not be VBScript?
ASKER
Set objExcel2nd = CreateObject("Excel.Applic ation")
objExcel2nd.DisplayAlerts = False
Set secondwkbook = objExcel2nd.Workbooks.open ("\\Midp-s fs-005\qaa im\QES Operations Team\Audit Effort\FileFeeds\ProjectSy nch.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
objExcel2nd.DisplayAlerts = False
Set secondwkbook = objExcel2nd.Workbooks.open
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
If you want to run it as a VBA macro change it to this:
Sub sorter()
Sheets("Sheet1").Activate
Sheets("Sheet1").Columns(" D1:D20").S ort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom , _
DataOption1:=xlSortNormal
End Sub
Sub sorter()
Sheets("Sheet1").Activate
Sheets("Sheet1").Columns("
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
DataOption1:=xlSortNormal
End Sub
ASKER
do not want to run it as a vba macro
>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.
I'm not acquainted with VBScript but perhaps you can adapt the VBA to suit.
Why not get the VBScript to call a VBA Excel macro as a sub-routine?
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("Projec tSynch")
Set objRange = objWorksheet2.UsedRange
Set objRange2 = objExcel2nd.Range("D1")
objRange.Sort objRange2, xlAscending, , , , , , xlYes
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("Projec
Set objRange = objWorksheet2.UsedRange
Set objRange2 = objExcel2nd.Range("D1")
objRange.Sort objRange2, xlAscending, , , , , , xlYes
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
TRY THIS:
Sheets("ProjectSynch").Act ivate
ActiveSheet.UsedRange.Sele ct
Range2 = "d1"
Selection.Sort Key1:=Range(Range2) ....
Sheets("ProjectSynch").Act
ActiveSheet.UsedRange.Sele
Range2 = "d1"
Selection.Sort Key1:=Range(Range2) ....
Tools->Macro->Record new macro
Then sort the columns manually
Tools->Macro->Stop recording
Open the VBA IDE (ALT-F11) and view the code.