Sort XL table via VBScript 1

[VulcanJedi]

Help.

I'm trying to report information out to XL via VBscripts.
I'm fairly knowledgable w/ VBA, but I seem to have a heck of a time w/ Range and Selction w/ VBS to XL files.

In particular I'm trying to programatically sort each column in a XL table. I cant hard code refrences like "A1" and need to have variables adjust for differnt table sizes.
I've seen examples but none show how to iterate through each / all columns? Any insights please?

TIA

Tranpkp

 

[Remou]

Excel has a macro recorder. These should help with getting the last cell:

http://www.tek-tips.com/faqs.cfm?fid=2112

http://www.tek-tips.com/faqs.cfm?fid=2115

 

[VulcanJedi]

I'm well aware or the macro recorder. I still seem to have issues. Porting VBA into VBS directly doesn't seem to work.
I have varied number of columns and want to sort each.

Tranpkp

 

[Remou]

Please post some code.

 

[VulcanJedi]

For x =1 To objXL.Sheets.Count
objXL.sheets(x).Select
objXL.Cells.EntireColumn.AutoFit

set objRange = objXL.sheets(x).UsedRange
objRange.Select
set objRange2 = objXL.sheets(x).Range("A1")


If Not x=1 Then

objRange.Sort objRange2, xlAscending, , , , , , xlYes
'
End If
'
Next

Tranpkp

 

[VulcanJedi]

Also tried this?:


For x =1 To objXL.Sheets.Count
objXL.sheets(x).Select
objXL.Cells.EntireColumn.AutoFit



If Not x=1 Then
For y = 1 To objXL.Sheets(x).usedrange.columns.count
'Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)
objXL.Sheets(x).Cells(1,y).Select
Set objRange=objXL.Sheets(x).ActiveCell.EntireColumn
Set objrange2=objXL.Sheets(x).ActiveCell
objrange.Sort objrange2, xlAscending, , , , , , xlYes

Next
'
End If
'
Next

Tranpkp

 

[Remou]

You must use the values of built-in constants instead of the constants.

objrange.Sort objrange2, xlAscending, , , , , , xlYes

Should be:

objrange.Sort objrange2, 1, , , , , , 1

You can get these value by typing, say,

?xlYes

In the immediate window, or by checking the Object Browser.

 

[VulcanJedi]

OMG Thank you that was bugging me all night !
Strange, because I swear I've used xlred and other builtin functions before? Perhaps not.


Tranpkp