Thank you! the information you supplied is helpful. I noticed that
forgot to mention that I was trying to do this with vba/vbs.
Below is what I have at the moment. It is sloppy as far as the coding
but it works. The probelm I am having is that the sort function doe
not seem to want to sort... I am not sure if you folks can help, but
thought I would try.
' =========================================
' Code is below
' =========================================
'=========================================================================
' VBScript OpenExcelAndEdit
'
' AUTHOR: J Morton
' COMPANY:
' DATE : 4/12/2004
' COMMENT:
'=========================================================================
'=========================================================================
' The following will hopefully be passed in by calling program
Dim PackageName : PackageName = "SomeString"
'=========================================================================
'
' Define the Spreadsheet Columns
'
' Here is the ful path to the Spreadsheet
Const sWorkbook = "\\Server\Share\Folder\Folder\Spreadsheet.XLS"
' Here is the tab we want to work with
Const sWorksheet = "TabName"
' If the format of the sheet changes, then the following info will hav
to change too.
' Last Column, needed for selection and sorting
Const LastCol = "O"
' Column Definitions
Const AppNameCol = "A"
Const scripterNameCol = "B"
Const DTAssignCol = "C"
Const DTReadyToScriptCol = "D"
Const DTPassedToQACol = "E"
Const DTClosedQACol = "F"
Const StatusCol = "G"
Const OrigScripterCol = "H"
Const SpecialNotesCol = "I"
Const ScriptNotesCol = "J"
Const TestMachCol = "K"
Const DocUpdateCol = "L"
Const DTCompleteCol = "M"
Const QACheckCompleteCol = "N"
Const RemarksCol = "O"
'=========================================================================
'
' Constants used for scripting in Excel
'
' Excel Applicaition window style
Const vbNormal = 1
' Movement in Excel worksheet
Const xlDown = -4121
Const xlToLeft = -4159
Const xlToRight = -4161
Const xlUp = -4162
' Excel Sorting
Const xlAscending = 1
Const xlDescending = 2
Const xlGuess = 0
Const xlNo = 2
Const xlYes = 1
Const xlSortRows = 2
Const xlSortColumns = 1
Const xlPinYin = 1
Const xlStroke = 2
Const xlSortNormal = 0
Const xlSortTextAsNumbers = 1
'
' My constants used while debugging
'
' Show the Excel Spreadsheet (True or False)
Const showXL = True
' Show variable info by echoing out
Const ShowDebug = True
'=========================================================================
'
' Define the Excel object so we can work with it
Dim oXL : Set oXL = CreateObject("Excel.Application")
'
' If we want to actually display the Excel window
' We will define it first
'
If showXL = True Then
' set the Excel window properties (not absolutely necessary)
oXL.WindowState = vbNormal ' Normal
oXL.Height = 300 ' height
oXL.Width = 400 ' width
oXL.Left = 40 ' X-Position
oXL.Top = 20 ' Y-Position
oXL.Visible = true ' show window
End If
'
' Open the Excel workbook and the tab we need
' to work with.
'
' Set object to file
Set oXLwb = oXL.Workbooks.Open(sWorkbook)
' Set object to tab in workbook
Set oXLwb = oXL.ActiveWorkbook.Worksheets(sWorksheet)
' Set it as active
oXLwb.Activate
' find the row with the package name
' Errors if the value is not present
err.Clear
On Error Resume Next
sMatch
oXL.WorksheetFunction.match(PackageName,oxl.Range("a1","a4000"),0)
If ShowDebug = True Then wscript.Echo "Error = " & err
If err = 1004 Then
If ShowDebug = True Then wscript.Echo "Value Not found"
' since it's not there, then let's ad
it in
EnterNewData
' since it's new, sort the sheet
SortData
ElseIf err = 0 Then
If ShowDebug = True Then wscript.echo sMatch
oxl.range("a"&sMatch).Select()
End If
On Error GoTo 0
CloseWb
'=========================================================================
'
' Subroutines and Functions
'
'=========================================================================
Sub EnterNewData
On Error GoTo 0
'
' find the last cell with data and move to the first empty
'
' Go to the 1st cell in the sheet
oxl.range("a1").Select()
' Find the last cell in the A column with data
oxl.Selection.End(xlDown).Select
If ShowDebug = True Then wscript.Echo "Last cell = " &
oxl.ActiveCell.Row
oxl.Range("a" & (oxl.ActiveCell.Row+1)).select
If ShowDebug = True Then
wscript.Echo "first Empty cell = " & oxl.ActiveCell.Row
wscript.Echo oxl.ActiveCell.Address
End If
wscript.Echo PackageName
oxl.ActiveCell.Value = PackageName
End Sub
'=========================================================================
Sub SortData
On Error GoTo 0
Dim sLastRow : sLastRow = ""
'
' The following are what VBA gives me
'
' Range("A1").Select
' Range("A1:O502").Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range _
' ("H2"), Order2:=xlAscending, Key3:=Range("G2"),
Order3:=xlAscending, _
' Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:= _
' xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, _
' DataOption3:=xlSortNormal
' Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
' DataOption1:=xlSortNormal
'
' This works as far as selecting the right range
'
' Go to the 1st cell in the sheet
oxl.range("a1").Select()
' Find the last cell in the A column with data
oxl.Selection.End(xlDown).Select
sLastRow = oxl.ActiveCell.Row
oxl.Range("a1", LastCol & sLastRow).select()
'
' Stuff I have tried...
'
' oxl.Range("a1", LastCol & sLastRow).Sort oxl.Range("A2"),xlAscending,
_
' oxl.Range("H2"),xlAscending, _
' oxl.Range("G2"),xlAscending, _
' xlGuess, _
' 1, _
' False, _
' xlTopToBottom, _
' xlSortNormal, _
' xlSortNormal, _
' xlSortNormal
oxl.Selection.Sort oxl.Range("A2"), xlAscending, xlYes, 1, False,
xlTopToBottom, xlSortNormal
End Sub
'=========================================================================
Sub CloseWb
'
' save and close the work book
'
oxl.ActiveWorkbook.Close True, sWorkbook
End sub
