NetwareX NDS Users Inactive For XX Days program

Use at your own risk. Any problems, damage or loss of business are your responsibility.

Program (see attached) for producing a report of Netware users inactive for xx days. Interactively prompts user for context and xx days (is also written to work in batch/command line mode, but that has not been tested). Outputs a tab delimited text file and also automatically uses Excel 2000 to import and save the results out as an Excel spreadsheet file. Review closely (and adjust defaults as appropriate/necessary to your situation) before using.

The program is a work in progress, written to begin learning/using WinBatch with Netware/NDS, by someone who hasn't programmed in twenty years, and who wasn't that good to start with. It is offered as a contribution to the WinBatch community, which has been very helpful in answering my questions. Hopefully, it or portions of its code will prove of some value to others.

It could obviously stand a lot of improvement. Feedback would be appreciated.

;=============================================================================================
; program: 		NDS Users Inactive For XX Days
;
; language: 	WinBatch v2003F
; extenders:	NetwareX 		WWNWX34I.DLL
;
; author:		Michael Harris
; created:		4/3/2003		last updated: 4/11/2003
;
; CAUTIONS:
;	- Offered as is.  Use at your own risk.  Damage, loss of business, etc. is your problem.
;	- Batch use (run from command line, with arguments) has not been tested and is likely buggy.
;	- This is a work in progress, by someone who hasn't programmed in twenty years,
;		and who is in the process of learning WinBatch.  It is being posted
;		as a way of contributing something back to the WinBatch community.
;	- Thoroughly review (and adjust default values (see below)) before using.
;
; description:
;		Can be run interactively (no command line arguments, will prompt for values)
;		or batch (with command line arguments.... see Command Line/Default Arguments below.
;		CAUTION: Batch (from command line, with arguments) has not been tested.  CAUTION
; 		Queries NDS for all user objects inactive for XX days
;		and outputs them and related info as tab delimited text file
;		and then (using Excel in an automated fashion) also saves it as a spreadsheet file
;		and leaves it up in Excel for the user to view, manipulate, print, etc.
;		for subsequent viewing, manipulation, printing in Excel
;		(Delimited files can be brought into Excel two different ways :
;			- Data | Get External Data | Import Text File - (column widths are set properly)
;			- File | Open - (column widths are not set properly and must be manually adjusted)
;		 In this program, the file is brought in via Data | Get External Data,
;		 as that approach results in column widths being adjusted properly (per data width).)
;
; output file location/name:
;		"NDS Users Inactive For %DaysInactive% Days On %CurrentTimeYmdHmsWithBlanks%.txt" 
;=============================================================================================


;================================ SET DEFAULT VALUES HERE ====================================
; Default Values     YOU WILL NEED TO CHANGE THESE BEFORE YOU CAN USE THE PROGRAM ON YOUR NET 
NDSTreeDefaultValue = '\\ACME_TREE\'
NDSContextDefaultValue = '.ACME'							; command line argument #1 (param1)
DaysInactiveDefaultValue = 90  							; command line argument #2 (param2)
FilePathDefaultValue = '\\R006_server\V006\groups\Isd_techservices\WinBatch\Reports\'
DialogTitle = 'NDS Users Inactive For XX Days'
SpreadsheetDelimiter = @TAB
;=============================================================================================


;================================ SET TOLERANT MODE HERE, IF NEEDED ==========================
; Set tolerant error mode.... 
;  @OFF		will suppress minor errors and keep running
;  @CANCEL	will end program on any error
ErrorMode(@OFF)
;ErrorMode(@CANCEL)
; Note that is turned off to avoid problems if try to find nonexistent object
; or when start extracting user object attribute values,
; as individual user objects can apparently be missing some attributes (ex: Given Name)'
; which results in non-minor error and program termination if set to @CANCEL
; Sometime, could review to see if could reset more selectively (just when doing NDS stuff?)
;=============================================================================================


;---------------------------------------------------------------------------------------------
;Load the NetwareX extender (Netware/NDS API) for use in this WinBatch program
AddExtender('WWNWX34I.DLL')
;---------------------------------------------------------------------------------------------


;------------- DEFINE FUNCTIONS HERE (FOR SUBSEQUENT USE IN MAIN LOGIC BELOW) ----------------
; note: function variables are local, subroutine variables are global
;
;
#DefineFunction PadAndReverseNDSNames(ListOfNDSNames)
; Inserts a blank (space) name segment (' ') between object name segment and remaining path segments
; (so that objects will sort out ahead of subcontainers) (empty segment will be removed later)
; and reverses all segments of each (now padded) NDS name in list
; from ".object..container...org" to ".org...container..object"
; The result is useful for sorting into organizational chart order....
; but must then be unpadded and reversed again before can be used to query NDS objects. 
NumberOfNames = ItemCount(ListOfNDSNames, @TAB)
; Process names, a name at a time
ListofReversedNDSNames = ListofNDSNames
For ListIndex = 1 to NumberOfNames by 1
	NDSName = ItemExtract(ListIndex, ListOfReversedNDSNames, @TAB)
	NDSName = ItemInsert(' ', 2, NDSName, '.')
	ReversedNDSName = NDSName
	NumberOfSegments = ItemCount(NDSName, '.')
	For SegmentIndex = 1 to NumberOfSegments by 1
		Segment = ItemExtract(SegmentIndex, NDSName, '.')
		ReversedNDSName = ItemReplace(Segment, (NumberOfSegments - SegmentIndex + 1), ReversedNDSName, '.')
	Next
	ListOfReversedNDSNames = ItemReplace(ReversedNDSName, ListIndex, ListOfReversedNDSNames, @TAB)
Next
Return (ListOfReversedNDSNames)
#EndFunction
	
	
;
;
#DefineFunction UnpadAndReverseNDSNames(ListOfReversedNDSNames)
; Reverses all segments of each (now padded) reversed NDS name in list
; from ".org...container..object" back to ".object..container...org"  
; and removes the blank (space) name segment (' ') from between object name segment and remaining 
; path segments (was there so that objects would sort out ahead of subcontainers).
; The result is a list of object names, in organizational chart order, but with names 
; in regular ".object..container...org" NDS notation, so can be used to call for object info. 
NumberOfNames = ItemCount(ListOfReversedNDSNames, @TAB)
; Process names, a name at a time
ListofNDSNames = ListOfReversedNDSNames
For ListIndex = 1 to NumberOfNames by 1
	ReversedNDSName = ItemExtract(ListIndex, ListOfNDSNames, @TAB)
	NDSName = ReversedNDSName
	NumberOfSegments = ItemCount(ReversedNDSName, '.')
	For SegmentIndex = 1 to NumberOfSegments by 1
		Segment = ItemExtract(SegmentIndex, ReversedNDSName, '.')
		NDSName = ItemReplace(Segment, (NumberOfSegments - SegmentIndex + 1), NDSName, '.')
		Next
	NDSName = ItemRemove(3, NDSName, '.')
	ListOfNDSNames = ItemReplace(NDSName, ListIndex, ListOfNDSNames, @TAB)
Next
Return (ListOfNDSNames)
#EndFunction
	
	
;---------------------------------------------------------------------------------------------


;-------------------------------- MAIN LOGIC STARTS HERE --------------------------------------

; Note:  Default NDS context and inactive days values were set up in SET DEFAULT VALUES HERE

; Note:  Command line arguments are available as param1, param2, etc.
; 			param0 contains the total number of command line arguments

; param0 will equal 0, if running interactively
; param0 will equal 3, if running batch with required three command line arguments
;If running batch, assure correct number of command line arguments
If ((param0 == 1) || (param0 >=4))
	MessageLine = StrCat('ERROR: Incorrect number of command line arguments.  param0 = ', param0)
	Message(DialogTitle, MessageLine)
	Exit
Endif

; Get/check NDS context
; (may need to add check that begins with '\\ACME_TREE\.')
; Command line arguments supplied?
If (param0 == 0)
	; No command line arguments were supplied (i.e. is being run interactively)
	; Ask user for NDS context (assure isn't empty or a number)
	BaseObjectSpec = ''
	ResultCode = 1
	While ((ResultCode != 0) || (IsNumber(BaseObjectSpec)) || (StrSub(BaseObjectSpec, 1, 1) != '.')) 
		BaseObjectSpec = AskLine(DialogTitle, 'NDS context within %NDSTreeDefaultValue%?', NDSContextDefaultValue)
		nwGetObjInfo(BaseObjectSpec, 2)
		ResultCode = LastError()
		If ((ResultCode != 0) || (IsNumber(BaseObjectSpec)) || (StrSub(BaseObjectSpec, 1, 1) != '.'))
			MessageLine = StrCat('ERROR: NDS context not found, must begin with leading period  You entered = ', BaseObjectSpec)
			Message(DialogTitle, MessageLine)
		Endif
	EndWhile
Else
	; Command line arguments were supplied (i.e. is being run batch)
	BaseObjectSpec = param1
	; Assure is searching ACME_TREE (otherwise, can get a very long delay before erroring)
   If ((BaseObjectSpec != '') && (!(IsNumber(BaseObjectSpec))) && (StrSub(BaseObjectSpec, 1, 1) == '.'))
		nwGetObjInfo(BaseObjectSpec, 2)
		ResultCode = LastError()
	Endif
	If ((BaseObjectSpec == '') || (IsNumber(BaseObjectSpec)) || (ResultCode != 0) || (StrSub(BaseObjectSpec, 1, 1) != '.'))
		MessageLine = StrCat('ERROR: Invalid NDS context, must begin with leading period  Argument supplied = ', BaseObjectSpec)
		Message(DialogTitle, MessageLine)
		; End program
		Exit
	Endif
Endif


; Get/check Days Inactive
; (note that TimeSubtract can use raw days.... don't have to convert to months&days)
; Command Line arguments supplied?
If (param0 == 0)
	; No command line arguments were supplied (i.e. is being run interactively)
	; Ask user for Days Inactive (assure isn't empty, nonnumeric or negative)
	DaysInactive = ''
	While ((DaysInactive == '') || (!(IsNumber(DaysInactive))))
		DaysInactive = AskLine(DialogTitle, 'Number of days inactive?', DaysInactiveDefaultValue)
		If ((!(IsNumber(DaysInactive))) && (DaysInactive <= 1))
			MessageLine = StrCat('ERROR: Must be positive number of days = ', DaysInactive)
			Message(DialogTitle, MessageLine)
		Endif
	EndWhile
Else
	; Command line arguments were supplied (i.e. is being run batch)
	DaysInactive = param2
	If ((DaysInactive == '') || (!(IsNumber(DaysInactive))) || (DaysInactive <= 1))
		MessageLine = StrCat('ERROR: Invalid Days Inactive = ', DaysInactive)
		Message(DialogTitle, MessageLine)
		; End program
		Exit
	Endif  
Endif
; Assure is an integer value
DaysInactive = Int(DaysInactive)

; If running interactively, tell user that search for inactive users is to begin when they click OK
If (param0 == 0)
	;MessageLine = StrCat("Search for inactive users will now start.  Please click OK and wait....")
	;Message(DialogTitle, MessageLine)
	MessageLine = StrCat("Search for inactive users starting.  Please wait....")
	BoxOpen(DialogTitle, MessageLine)
Endif


; get current date/time
CurrentTimeYmdHms = TimeYmdHms()

; Generate output file name and open file (be default, will go where this program is run from)
; replace colons with time with blanks
CurrentTimeYmdHmsWithBlanks = StrReplace(CurrentTimeYmdHms, ':', ' ')
OutputFileName = FilePathDefaultValue
OutputFileName = StrCat(OutputFileName, 'NDS Users Inactive For ', DaysInactive) 
OutputFileName = StrCat(OutputFileName, ' Days On ', CurrentTimeYmdHmsWithBlanks, '.txt') 
; to be safe, delete file by that name if already exists (not likely, but best to be safe
; in case change file naming strategy (though "WRITE" should zero out and overwrite existing file)
If (FileExist(OutputFileName))
	FileDelete(OutputFileName)
Endif
FileHandle = FileOpen(OutputFileName, 'WRITE')
If (FileHandle == 0)
	MessageLine = StrCat('File open/create of ', OutputFileName, ' failed')
	Message(DialogTitle, MessageLine)
	;terminate the program
	Exit
Endif

; generate the full NDS tree/context
BaseObjectSpec = StrCat(NDSTreeDefaultValue, BaseObjectSpec)

; generate the comparison date
; (note that can use raw days.... don't have to convert to months&days)
ComparisonTimeYmdHms = TimeSubtract(CurrentTimeYmdHms, "0000:00:%DaysInactive%:00:00:00")

; Set up a search filter
; (is cumulative... use *FREE_BUFFER* to reset/deactivate)
; (source of attribute names was the NDS Snoop program....
;  see www.novell.com/coolsolutions/tools/1005.html)
; (another source of attribute names is the output displayed by the example
;  WinBatch program supplied in the nwGetObjValue writeup in the NetwareX help)
nwSearchFilter(BaseObjectSpec, 'BASECLS','',0,0,0)
nwSearchFilter(BaseObjectSpec, 'ANAME','User',0,0,0)
nwSearchFilter(BaseObjectSpec, 'AND','',0,0,0)
;nwSearchFilter(BaseObjectSpec, 'ANAME','Login Time',0,0,0)
nwSearchFilter(BaseObjectSpec, 'ANAME','Last Login Time',0,0,0)
nwSearchFilter(BaseObjectSpec, 'LE','',0,0,0)
nwSearchFilter(BaseObjectSpec, 'TIMESTAMP',ComparisonTimeYmdHms,0,0,0)
nwSearchFilter(BaseObjectSpec, 'END','',0,0,0)

; Set up search flags (combine/add various bit flag values)
; value of 2 says search scope is base-object and all its subordinates
Flags = 2

; Tell WinBatch's NetwareX extensions to return typeless names from NDS
nwSetOptions(1,@TRUE)

; Do the search and get the result code
UserListDistinguishedTabbed = nwSearchObjects(BaseObjectSpec, '', '', Flags, '')
ResultCode = LastError()

; Verify that search was ok
If (ResultCode != 0)
	; Diagnostic (if needed) report result code
	MessageLine = StrCat("User objects search failed with result code = ", ResultCode)
	Message(DialogTitle, MessageLine)
	; End the program
	Exit
Endif

; Reset/deactivate the search filter
nwSearchFilter('', '*FREE_BUFFER*', '', 0, 0, 0)

; diagnostic (if needed)
;Message(DialogTitle, UserListDistinguishedTabbed)

; Sort the list (comes from NDS in unsorted sequence) by
; a) flipping the NDS names from ".object.container..org" to ".org.container..object",
; b) sorting the list of flipped names (into organizational chart sequence)
; C) reflipping the names back to ".object.container..org"
UserListDistinguishedTabbed = PadAndReverseNDSNames(UserListDistinguishedTabbed)
; diagnostic (if needed)
; Message(DialogTitle, UserListDistinguishedTabbed)
UserListDistinguishedTabbed = ItemSort(UserListDistinguishedTabbed, @TAB)
UserListDistinguishedTabbed = UnpadAndReverseNDSNames(UserListDistinguishedTabbed)

; diagnostic (if needed)
;Message(DialogTitle, UserListDistinguishedTabbed)

; Determine number of user objects returned
NumberOfUsers = ItemCount(UserListDistinguishedTabbed, @TAB)

; If running interactively, report number of users found in the search scope
If (param0 == 0)
	;MessageLine = StrCat("Number of users inactive for %DaysInactive% days found = ", NumberOfUsers, "  Please click OK and wait....")
	;Message(DialogTitle, MessageLine)
	MessageLine = StrCat("Number of users inactive for %DaysInactive% days found = ", NumberOfUsers, @CR, "Please wait while file is produced....")
	BoxText(MessageLine)
Endif


; Initialize needed variables for use in following loop
; An account with no time restrictions has a Login Allowed Time Map
; consisting of 84 consecutive Fs
NoTimeRestrictions = StrFill("F", 84)

; Write a report header record (with title/info spread across several columns,
; so as to not mess up the autoadjusted width of columns when this
; file is imported into Excel via Data | Get External Data | Import Text File.
; (note that width of title (including trailing spaces) causes Excel to
;  adjust column widths accordingly during data import of this file)
OutputLine = ""
OutputLine = StrCat(OutputLine, "NDS USERS INACTIVE", SpreadsheetDelimiter)
OutputLine = StrCat(OutputLine, "FOR ", DaysInactive, " DAYS", SpreadsheetDelimiter)
OutputLine = StrCat(OutputLine, "              (produced: ", SpreadsheetDelimiter)
OutputLine = StrCat(OutputLine, CurrentTimeYmdHms, ")", SpreadsheetDelimiter)
OutputLine = StrCat(OutputLine, "", SpreadsheetDelimiter)
OutputLine = StrCat(OutputLine, "")
;a CR-LF is appended at the end of the line by the write
FileWrite(FileHandle, OutputLine)

; Write a header record with field descriptions delimited by tabs
; (note that width of title (including trailing spaces) causes Excel to
;  adjust column widths accordingly during data import of this file)
OutputLine = ""
OutputLine = StrCat(OutputLine, "NDS Path (reversed)  ", SpreadsheetDelimiter)
OutputLine = StrCat(OutputLine, "User      ", SpreadsheetDelimiter)
OutputLine = StrCat(OutputLine, "Last Name          ", SpreadsheetDelimiter)
OutputLine = StrCat(OutputLine, "First Name         ", SpreadsheetDelimiter)
OutputLine = StrCat(OutputLine, "Disabled?", SpreadsheetDelimiter)
OutputLine = StrCat(OutputLine, "Last Login")
;a CR-LF is appended at the end of the line by the write
FileWrite(FileHandle, OutputLine)

;Set tolerant error mode.... as intolerant mode aborts program if attribute missing
;(example: "Given Name" returns value for most, but errors on some) 
; @OFF		will suppress minor errors and keep running
; @CANCEL	will end program on any error
ErrorMode(@OFF)

; Loop through and process all users
For UserListIndex = 1 to NumberOfUsers by 1
	; Process current user
	; Extract User full typeless name from tab delimited list
	UserFullTypelessName = ItemExtract(UserListIndex, UserListDistinguishedTabbed, @TAB)
	; To be safe, verify that user still exists... otherwise go to next user
	; NOTE: Does not appear to work reliably (returns non-zero value of 275 for some that exist)
	;       Worthwile to test for 'NDS' (vs. 'BINDERY') return value instead?
	;NDSorBinderyObject = ''
	;NDSorBinderyObject = nwGetObjInfo(UserFullTypelessName, 2)
	;ResultCode = LastError()
	;MessageLine = StrCat('UserFullTypelessName=', UserFullTypelessName, 'NDSorBinderyObject=', NDSorBinderyObject, ' ResultCode=', ResultCode)
	;Message(DialogTitle, MessageLine)
	; If (ResultCode) then Continue ???????
	;  User still exists
	; (note: As in "Netware user report by snivling" example program on ehelp author forum,
	;        attribute values could be gotten/handled via a list of attribute names and a loop)
	; (NOTE: Attributes are not guaranteed to exist.  May exist and contain/not contain something
	;        or may not exist (depends on whether has ever had something entered?).
	;        Empty or not exist returns value of 0.)
	; Extract User simple name from full typeless name (period delimited, with leading period)
	User = ItemExtract(2, UserFullTypelessName, ".")
	; Generate a reversed (root-to-container) version of user object's NDS path
	; Remove leading period (empty item and its period delimiter, in terms of period delimited list)
	NDSPath = ItemRemove(1, UserFullTypelessName, ".")
	; Remove User simple name item (and its period delimeter)
	NDSPath = ItemRemove(1, NDSPath, ".")
	; Determine number items (number of NDS path segments) remaining in the list
	ItmCnt = ItemCount(NDSPath, ".")
	; Swap/reverse the items (path segments)
	; Initialize reversed name list with proper number of items and delimiters by brute force cheat
	; of copying the "to be reversed" list into it
	ReversedNDSPath = NDSPath
	For Index = 1 to ItmCnt by 1
		; This could be done as a single line, but is broken out for readability and troubleshooting
		Item = ItemExtract(Index, NDSPath, ".")
		ReversedNDSPath = ItemReplace(Item, (ItmCnt - Index + 1), ReversedNDSPath, ".")
	Next
	; Now get the rest of desired info for this particular user
	; NOTE: Current method of querying for each piece of info is ineffecient
	;       and should be replaced with a comprehensive means of 
	;       getting and parsing out all related info from a single call
	; Set flag for subsequent nwGetObjValue calls to 1 (because that's how it is in the example)
	flag = 1
	; Need to worry about protecting against empty return value?  If so, could first set to " "
	; Get Surname (last name)
	Surname = nwGetObjValue(UserFullTypelessName, 'Surname', "", "", flag)
	; Protect against/flag (as "(None)") empty name (i.e. if returned a 0)
	If (Surname == 0)
		Surname = '(None)'
	Endif
		; Get Given Name (first name)
	GivenName = nwGetObjValue(UserFullTypelessName, 'Given Name', "", "", flag)
	If (GivenName == 0)
		GivenName = '(None)'
	Endif
	; Get Full Name (first last) (this may be redundant/unneeded)
	;FullName = nwGetObjValue(UserFullTypelessName, 'Full Name', "", "", flag)
	; Get Login Disabled boolean (0 = not disabled) and convert to Yes or No
	LoginDisabled = nwGetObjValue(UserFullTypelessName, 'Login Disabled', "", "", flag)
	If (LoginDisabled == "0")
		LoginDisabled = "No"
	Else
		LoginDisabled = "Yes"
	Endif
	; Get Last Login Time (this may actually be next to last login time
	; but Login Time appears to be unreliable)
	; and convert it from integer seconds since Jan 01 1970 to human readable form
	; and shorten it to just Ymd (year month day)
	;LoginTimeYmdHms = nwGetObjValue(UserFullTypelessName, 'Login Time', "", "", flag)
	LastLoginTimeYmdHms = nwGetObjValue(UserFullTypelessName, 'Last Login Time', "", "", flag)
	LastLoginTimeYmdHms = TimeAdd("1970:01:01:00:00:00", "0000:00:00:00:00:%LastLoginTimeYmdHms%")
	LastLoginTimeYmd = StrSub(LastLoginTimeYmdHms, 1, 10)

	; Get Login Maximum Simultaneous (maximum connections)
	;LoginMaximumSimultaneous = nwGetObjValue(UserFullTypelessName, 'Login Maximum Simultaneous', "", "", flag)
	; Get Login Allowed Time Map (time restrictions)
	; and interpret it as "time restrictions exist Yes or No"
	;LoginAllowedTimeMap = nwGetObjValue(UserFullTypelessName, 'Login Allowed Time Map', "", "", flag)
	;If (LoginAllowedTimeMap == NoTimeRestrictions)
	;	TimeRestrictions = "No"
	;Else
	;	TimeRestrictions = "Yes"
	;Endif
	; Write a record with current user's information delimited by tabs
	OutputLine = ""
	OutputLine = StrCat(OutputLine, ReversedNDSPath, SpreadsheetDelimiter)
	OutputLine = StrCat(OutputLine, User, SpreadsheetDelimiter)
	OutputLine = StrCat(OutputLine, Surname, SpreadsheetDelimiter)
	OutputLine = StrCat(OutputLine, GivenName, SpreadsheetDelimiter)
	OutputLine = StrCat(OutputLine, LoginDisabled, SpreadsheetDelimiter)
	OutputLine = StrCat(OutputLine, LastLoginTimeYmd)
	; a CR-LF is appended at the end of the line by the write
	FileWrite(FileHandle, OutputLine)
	; Diagnostic message of per user data
	MessageLine = StrCat("User ", User)
	; Message ("NDS Users Inactive For XX Days", MessageLine)
Next

; close the output file
FileClose(FileHandle)

; close "please wait" box and provide ending message to user
If (param0 == 0)
	BoxShut()
	MessageLine = StrCat("Number of users inactive for %DaysInactive% days found = ", NumberOfUsers, @CR)
	MessageLine = StrCat("DONE.", @CR)
	MessageLine = StrCat(MessageLine, "Number of users inactive for %DaysInactive% days found = ", NumberOfUsers, @CR)
	MessageLine = StrCat(MessageLine, "See delimited text file file:", @CR, OutputFileName)
	Message(DialogTitle, MessageLine)
Endif

; offer the user the option of also saving the file in spreadsheet file format
If (param0 == 0)
	; ask user if wish to also save the file as a spreadsheet file
	; change output file name file type from .txt to .xls
	SpreadsheetFileName = ItemReplace("xls", ItemCount(OutputFileName, "."), OutputFileName, ".")
	MessageLine = "Also save as spreadsheet file (same name, with .xls file type)?"
	MessageLine = StrCat(MessageLine, @CR, SpreadsheetFileName)
	YesNoAnswer = AskYesNo(DialogTitle, MessageLine)
	If (YesNoAnswer == @YES)
		; note: might be better to do the following via OLE extender
		;       for reliability, timing/speed sake?
		; start the spreadhsheet program and wait for it to be ready
		Run("excel.exe", "")
		TimeDelay(1)
		WinWaitExist("~Microsoft Excel", 60)
		; start a new sheet (to be safe, in case Excel is already being used on a sheet)
		; File | New | OK and wait for it to be ready
		SendKeysTo("~Microsoft Excel", "!fn{ENTER}")
		TimeDelay(1)
		; import the delimited text file (doing this, instead of opening the file,
		; causes the column widths to be adjusted according to incoming data values maximum width)
		; Data | Get External Data | Import Text File
		; (copy/paste the output file name into the entry field, as paste is faster than typein)
		ClipPut(OutputFileName)
		SendKeysTo("~Microsoft Excel", "!ddt^v{ENTER}")
		TimeDelay(1)
		; supply the import the next, next, next, ... that it needs
		SendKeysTo("~Microsoft Excel", "{ENTER}{ENTER}{ENTER}{ENTER}{ENTER}")
		TimeDelay(1)
		; save the sheet out as spreadsheet format file
		; (copy/paste the spreadsheet file name into the entry field, as paste is faster than typein)
		ClipPut(SpreadsheetFileName)
		SendKeysTo("~Microsoft Excel", "!fa^v{ENTER}")
		TimeDelay(5)
		; let the user know the sheet has been saved and can now be viewed, etc. as they wish
		MessageLine = "Spreadsheet has been saved.  View or close as you wish."
		Message(DialogTitle, MessageLine)
	Endif
Endif

; Set intolerant error mode.... will terminate execution on any error
ErrorMode(@CANCEL)

; end program
exit

Article ID:   W16061

File Created: 2004:03:30:15:42:38

Last Updated: 2004:03:30:15:42:38

Database Search

TechHome

NetwareX NDS Users Inactive For XX Days program