Script to sort part numbers

[ntweisen]

I'm trying to run a script from a CAD software which exports property values into an Excel spreadsheet. I need to then sort the spreadsheet by part numbers (which are located in column G) but my script is not working correctly. My data range is A1:G50. Any ideas on why this isn't working?

Const xlCenter = -4108
Const xlAscending = 1
Const xlYes = 1
Const xlSortOnValues = 0
Const xlSortNormal = 0
Const xlTopToBottom = 1
Const xlPinYin = 1

Set objRange = myworksheet.UsedRange
Set objRange2 = Excel.Range("G1")
objRange.Sort objRange2, xlAscending, , , , , , xlYes

http://excelspreadsheetshelp.blogspot.com

 

[MintJulep]

Set objRange2 = Excel.Range("G1")

"G1" is a single cell.

When you sort a single cell nothing useful happens.

 

[BigInch]

Unless you want to sort its DNA.

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

 

[ntweisen]

I figured it out:

Excel.Range("A:G").Select
Excel.Selection.Sort Excel.Range("G1"),1,Excel.Range("A1"),,1,Excel.Range("B1"),1,1,1,False

You first specify the data range then the single G1, A1, B1 tell it which column to sort by.

http://excelspreadsheetshelp.blogspot.com

 

[MortenA]

why cant you just use a pivot table? Usually this type of list consists of a umber of idetical items where you need to count them. A pivot table is excellent for this.

Best regards

Morten

 

[BigInch]

my guess is he wants the code to do it automatically

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

 

[bernoullies123]

Sort on Column G will render the same result.

I use to add macros and scripts to excel files, but when you do they end up getting caught up in security warning etc. Hence I have moved away from including these, and gone back to the old fashion way.

 

[IDS]

I use to add macros and scripts to excel files, but when you do they end up getting caught up in security warning etc. Hence I have moved away from including these, and gone back to the old fashion way.

Is it really so hard to get people to click on the "Enable Content" button? Or better get them to set up a "trusted" folder, and copy spreadsheets with macros there.

To just stop using macros because of the security warnings strikes me as anti-productive.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/