How to select and "SORT" Excel column with keystro

[bstafford]

Marcus, I appreciate your help, seems more study is required of me when it comes to this, my first time with this vbscript, I have pasted, highlighted and then pressed code, hope your can see the entire script I have done.

Thanks again for the help.

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

VBRun>OpenBook,C:\MO\Excel Data\MO Data.xls
Wait>2
VBRun>SortRange,Export_MO_Data,A1:CI9999,G



End

[Marcus Tettmar]

Here you go:

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

Sub Modify_Format(Sheet,Range)
    xlBook.Worksheets(Sheet).Select
    xlBook.Worksheets(Sheet).Columns(Range).Select
    xlApp.Selection.HorizontalAlignment = -4108
    xlApp.Selection.VerticalAlignment = -4107
    xlApp.Selection.WrapText = False
    xlApp.Selection.Orientation = 0
    xlApp.Selection.AddIndent = False
    xlApp.Selection.IndentLevel = 0
    xlApp.Selection.ShrinkToFit = False
    xlApp.Selection.ReadingOrder = -5002
    xlApp.Selection.MergeCells = False
End Sub


VBEND

VBRun>OpenBook,C:\MO\Excel Data\MO Data.xls

VBRun>SortRange,Export_MO_Data,A1:CI9999,G

VBRun>Modify_Format,Export_MO_Data,B:B

I've added the final line which calls the function. You didn't have anything actually calling the function or specifying which sheet it should operate on so I've made a few guesses there, but hopefully you can fill in any blanks.

Note how I've moved the VBScript subroutine (the block that begins "Sub") into the VB block and also modified the code so that the Excel object references xlApp and xlBook created in the first OpenBook subroutine are used. This then gives the objects and properties you were referring to context which are meaningless to VBScript otherwise.

This is all explained here:
http://www.mjtnet.com/blog/2008/04/28/c ... -vbscript/