How to select and "SORT" Excel column with keystro

bstafford · Post by **bstafford** » Sun Jul 17, 2011 10:00 am

Looking for help for working in an excel spreadsheet.

Need to learn how to automate this excel file to modify the layout each time the file is opened, so the file is sorted properly. I am new to all this and just joined recently and now have Macro Sched Pro 12.

If I can get a start on what commands to use to automatically select the column and sort A to Z this will get me started. I have an easy project this 1st time around to just modify the excel spreadsheet to sort my data.

I would like to use keystrokes only if possible, as I have used the recorder but I can only use the mouse to do what I want, but believe I will have problems with this method. I have searched and downloaded the keyboard shortcuts, but can't find the shortcut for "SORT". Sorry to bother you but I did try to post the question.

Thanks for any help you can give me to get me started.

Best Regards

Bill

[/b]

hagchr · Post by **hagchr** » Sun Jul 17, 2011 3:40 pm

Hi,

I am not sure which version of Excel you are using but eg in Excel 2010 you could select a cell in the column you want to sort and then access SORT through the key combinations

ALT+A+SA (ascending)
ALT+A+SD (descending)

(ie you press ALT then can access the menus and submenues through letter combinations. Maybe different combinations in earlier versions of excel but concept the same)

bstafford · Post by **bstafford** » Sun Jul 17, 2011 6:15 pm

Hi hagchr,

Thanks for reply, using Excel 2007 and have now tried that keystroke and it works.

My problem I am still trying to figure out is how to select the column without using the mouse and only keystroke. Do you know how that is done with keystrokes only?

For example need to select Column F and the will use the Alt+A+SA then Enter, that sorting works fine, thanks for that, I did a search but did not see that one for sorting. I must have overlooked that option or read tooo fast.

Let me know if you can tell me how to select a whole column, even row as I would do the same for that if there is a keystroke for it.

hagchr · Post by **hagchr** » Sun Jul 17, 2011 7:40 pm

Hi bstafford,

What you can do is:

PRESS F5 (goto/select range in Excel)
Wait>0.2
SendText>A:A (eg to select A column)
Wait>0.2
PRESS ENTER

bstafford · Post by **bstafford** » Mon Jul 18, 2011 5:25 am

Hi,

Thanks for the reply, I will give this a try today and see how we go.
Will let you know how I do, thanks again.

Regards

Bill

Post by **Marcus Tettmar** » Mon Jul 18, 2011 7:39 am

There's no need to automate anything in Excel by sending keystrokes.

Two options:

1. The simplest is to record a macro inside Excel - using Excel's own macro recorder - to sort the column. Save the macro and then you can have Macro Scheduler run it when it opens the workbook. Look up XLOpen and XLRun functions. No keystrokes needed.

2. If 1 is not possible (maybe you are not in a position to modify the workbook by including a macro in it), you can port the code produced by the VBA macro recorder to VBScript. As an example the following simple macro uses VBScript to open an Excel file (it uses the example.xls file supplied with Macro Scheduler) and then sorts Sheet1 on column A:

Code: Select all

VBSTART
Dim xlApp
Dim xlBook

Sub OpenBook(workbook)
  Set xlApp = CreateObject("Excel.Application")
  Set xlBook = xlApp.Workbooks.Open(workbook)
  xlApp.visible = True
End Sub

Sub SortRange(Sheet,Range,Key)
 xlBook.Worksheets(Sheet).Range(Range).Sort xlBook.Worksheets(Sheet).Columns(Key), , , , , , , 0
End Sub
VBEND

VBRun>OpenBook,%SCRIPT_DIR%\example.xls
Wait>2
VBRun>SortRange,Sheet1,A1:E9999,A

Again, no keystrokes needed.

bstafford · Post by **bstafford** » Mon Jul 18, 2011 8:21 am

Hi Marcus,

Thanks for this code, I did try the keystrokes with the excel file and can do this, but as you mention in the options, I need to refresh this data each time a new report is generated.

This report is exported into a new data worksheet and the will need an external macro to run this each time.

I will try today the methods you are teaching me here and will understand better what I have to do once I remember and learn new command strings.

Also have investigated and compiled the Excel keystroke shortcuts and will review as well.

Thanks again for all this help, much appreciated and will get me to where I need to go.

Best Regards

Bill

bstafford · Post by **bstafford** » Tue Jul 19, 2011 11:25 am

Hi, Marcus & hagchr,

I have now been able to start this project with commands to open excel and sort my column G, use the following:

//Set IGNORESPACES to 1 to force script interpreter to ignore spaces.
//If using IGNORESPACES quote strings in {" ... "}
//Let>IGNORESPACES=1
VBSTART
Dim xlApp
Dim xlBook
Sub OpenBook(workbook)
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(workbook)
xlApp.visible = True
End Sub
Sub SortRange(Sheet,Range,Key)
xlBook.Worksheets(Sheet).Range(Range).Sort xlBook.Worksheets(Sheet).Columns(Key), , , , , , , 0
End Sub
VBEND
VBRun>OpenBook,C:\MO\Excel Data\MO Data.xls
Wait>2
VBRun>SortRange,Export_MO_Data,A1:CI9999,G
End

Can you tell me if you see any problems with the code?

The data I checked it looks okay.

Thanks again for all the help.

Bill

Post by **Marcus Tettmar** » Tue Jul 19, 2011 12:38 pm

bstafford wrote:Can you tell me if you see any problems with the code?

Does it work? Does it produce the desired result?

bstafford · Post by **bstafford** » Tue Jul 19, 2011 3:36 pm

Hello,

I have examined the data and is sorting properly, but also getting an error message of the following:

Line 21 - Missing Parameters in Function Call (Min of 2 Expected)

// COMPILE_OPTS|J:\MO\Excel Data\MO Macro.exe|C:\MY HOME\Icons\CLON.ico|CONSOLE=0|INCLUDES=1|
//Set IGNORESPACES to 1 to force script interpreter to ignore spaces.
//If using IGNORESPACES quote strings in {" ... "}
//Let>IGNORESPACES=1
VBSTART
Dim xlApp
Dim xlBook
Sub OpenBook(workbook)
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(workbook)
xlApp.visible = True
End Sub
Sub SortRange(Sheet,Range,Key)
xlBook.Worksheets(Sheet).Range(Range).Sort xlBook.Worksheets(Sheet).Columns(Key), , , , , , , 0
End Sub
VBEND
VBRun>OpenBook,C:\MO\Excel Data\MO Data.xls
Wait>2
VBRun>SortRange,Export_MO_Data,A1:CI9999,G

Line 21 Starts here:

Sub Modify_Format()
'
' Modify_Format Macro
' Modify the Excel format to read and flow better
'

'
Columns("B:B").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

End

Now looking at how to change column width, more reading and learning.

Thanks again for all the help.

Bill

Post by **Marcus Tettmar** » Tue Jul 19, 2011 3:41 pm

Sub Modify_Format() is VBScript syntax. Is it inside a VBSTART ... VBEND block?

bstafford · Post by **bstafford** » Tue Jul 19, 2011 3:45 pm

Hello, No this is running in my Macro scheduler where the Excel file is opened and then the sort function is working, but at line 21 in the Macro Scheduler is where it goes into error.

Post by **Marcus Tettmar** » Tue Jul 19, 2011 3:49 pm

Right, but is that line inside a VBSTART ... VBEND block.

It is VBScript code. But you have not provided the entire script, only excerpts. The error you are getting is a MacroScript error but the code you refer to is VBScript code. That suggests to me that you do not have it inside the VBSTART ... VBEND code as per the code you have quoted before it. Move that Sub .... End Sub to inside your VBSTART ... VBEND block.

bstafford · Post by **bstafford** » Tue Jul 19, 2011 3:57 pm

Yes, this is the entire script:

VBSTART
Dim xlApp
Dim xlBook
Sub OpenBook(workbook)
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(workbook)
xlApp.visible = True
End Sub
Sub SortRange(Sheet,Range,Key)
xlBook.Worksheets(Sheet).Range(Range).Sort xlBook.Worksheets(Sheet).Columns(Key), , , , , , , 0
End Sub
VBEND
VBRun>OpenBook,C:\MO\Excel Data\MO Data.xls
Wait>2
VBRun>SortRange,Export_MO_Data,A1:CI9999,G

Sub Modify_Format()
'
' Modify_Format Macro
' Modify the Excel format to read and flow better
'

'
Columns("B:B").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

End

So I have tried to move that sub to be inside, but it still gives same error.

Apparently you can't mix the 2 types, macroscheduler and vbscript?

Post by **Marcus Tettmar** » Tue Jul 19, 2011 4:04 pm

Ok:

1. I hate repeating myself but the subroutine is vbscript. It therefore needs to be inside your VBSTART .. VBEND block.

2. Yes you can mix both, look at the code above this subroutine, it is part VBScript inside the VBSTART .. VBEND block and then you have native code after that block.

3. Even if you move this subroutine into your VBSTART VBEND block it will produce errors. Columns is not an object known to VBScript. I presume you are referring to the Columns object belonging to an Excel worksheet object. You would have to qualify it thus. You probably want xlBook.Worksheets(Sheetname).Columns ..... where xlBook is a variable created by your OpenBook function which creates the Excel app and workbook instances (the latter of which owns the worksheet child object which in turn owns the Columns object you refer to). Since VBScript and Macro Scheduler sits outside of Excel it cannot possibly know about the objects and properties belonging to it unless they are explicitly declared and references to them are created.

If you can post the code using the CODE button (above the message editing box) so that the code is properly formatted I would be happy to try and fix it for you.

If you prefer to do it yourself take a look at:
http://www.mjtnet.com/blog/2008/04/28/c ... -vbscript/