Assistance with Date Loop/Date Formats

[napalmmk9]

Hello: I'm working on a script that will take input of a beginning and ending date, and then loop for each day in that range, sending that date in two different formats to two different programs (a terminal program and ExCel) each time. I'm pretty much a total scripting newbie, and don't understand much of what I'm seeing in the help file.

In this example, $begin date would equal 3/04/03.

The problem is I've got not idea how to make the inputs recognized as dates in the proper formats, or how to export them. The script, with the things I've thought to do commented, is below.

;$begin_date=InputBox("Beginning Date","Enter the Start Date","","")

;$end_date=InputBox("Ending Date","Enter the Ending Date","","")

;iDateCalc=_DateDiff('d',$begin_date,$end_date)

;for $i=0 To $iDateCalc Step 1

WinActivate("AccuTerm 97 - [psi (1)]")

;send $begin_date+i in format mm/dd/yy twice

Send("3/04/03")

send("{ENTER}")

Send("3/04/03")

Send("{ENTER}")

Send("!TC")

WinWaitActive("Begin Capture")

Send("{ENTER}")

WinWaitActive("AccuTerm 97 - [psi (1)]")

Send("{F2}")

sleep(1000)

Send("{ENTER}")

Send("!TC")

WinWaitActive("End Capture")

Send("!e")

WinSetState("AccuTerm 97 - [psi (1)]","",@SW_MINIMIZE)

WinActivate("Microsoft Excel - att1.xls")

Sleep(1000)

Send("!IW")

Send("!OHR")

;send $begin_date+i in format mm.dd

Send("3.04")

Send("{ENTER}")

Sleep(1000)

Send("^d")

Sleep(1000)

Send("^{PGDN}")

;next $i

If you could help make this work you could save me hours of tedious drudgery. Thank you very much in advance.

[Kerros]

First off I would check out the UDF that help with Excel and different dates.

http://www.autoitscript.com/forum/index.php?showtopic=34302#

Also take a look at GUICtrlCreateDate in the help file, the example also give you a calender to work with.

Hope that points you in the right direction.

Kerros

Edited December 14, 2007 by Kerros

[napalmmk9]

Thanks for the response!

It actually has very little to do with Excel, Excel just happens to be one of the programs I'm using -- the script should make a new worksheet for every day in the range and rename it "mm.dd" -- the only things that go into Excel's cells are from an Excel macro the script calls. The GUICtrlCreateDate information is useful, though.

The thing is I need the input to be sent in two differect formats.

If I have:

$Begin_Date=GUICtrlCreateDate ("2000/01/01", 10,10,185,20 )

$End_Date=GUICtrlCreateDate ("NowCalc()",10,10,185,20)

For each value between these (I assume I'd use a DATEDIFF function, as above) I want to send "dd/mm/yy" to one program and "dd.mm" to another.

[Kerros]

The main reason that I use the Excel UDF is to use the Save As function, much easier then trying send keystrokes and having excel exit properly.

_ExcelSaveDocAs($oExcel, $sFilePath, $sType = "xls", $fAlerts = 0, $fOverWrite = 0)

As for sending different dates my best suggestion without doing some actual scripting is to use the $DTS_SHORTDATEFORMAT which give you 03/12/07 and use stringsplit to cut them up into the individual sections.

for example

CODE

#include<array.au3>

$date_parsed = StringSplit("03/12/07/",'/')

_ArrayDisplay($date_parsed)

will give you an array where :

$date_parsed[1] = month

$date_parsed[2] = day

$date_parsed[3] = year

You can then use those to save off the excel file

_ExcelSaveDocAs($excelDoc, @DocumentsCommonDir&'\'&$date_parsed[2]&'.'&$date_parsed[3]&'.xls',

Example of what could be used.

CODE

#include<array.au3>

#include<ExcelCOM_UDF.au3>

$date_parsed = StringSplit("03/12/07/",'/')

_ArrayDisplay($date_parsed)

$excelDoc = _ExcelBookNew(1)

$ret = _ExcelBookSaveAs($excelDoc, @MyDocumentsDir&'\'&$date_parsed[2]&'.'&$date_parsed[3]&'.xls',0,0)

if @error Then MsgBox(1,"Excel Save Error", @error)

_ExcelBookClose($excelDoc,0)

[napalmmk9]

I'm losing my mind here.

First off, there's no need for the save as, as far as I can tell. I am renaming workSHEETS in a single workBOOK. I have a function that will do it, which is Alt-O-H-R (fOrmat|sHeet|Rename).

I cannot get this array to work, or to figure out how to make it loop through. Right now I'm just sending it to Notepad to test it. The output I'm looking for, so I can see how the loop works, is one line in notepad for each day starting with $begin_date and ending with $end_date.

expandcollapse popup

#include <GUIConstants.au3>
#INCLUDE <DATE.AU3>
#INCLUDE <ARRAY.AU3>
Opt("GUIOnEventMode", 1) 

GUICreate ( "Start Date", 200,200,800,200)
GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEClicked")
Global $Begin_Date
Global $End_Date
$Begin_Date=GUICtrlCreateDate ("2000/01/01",10,10,185,20,$DTS_SHORTDATEFORMAT)
$okbutton = GUICtrlCreateButton("OK", 70, 50, 60)
GUICtrlSetOnEvent($okbutton, "OKButton1")
GUISetState (@SW_SHOW)


While 1
  Sleep(1000)
WEnd

Func OKButton1()

GUICreate ( "End Date", 200,200,800,200)
GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEClicked")
$End_Date=GUICtrlCreateDate ("2000/01/01",10,10,185,20,$DTS_SHORTDATEFORMAT)
$okbutton2 = GUICtrlCreateButton("OK", 70, 50, 60)
GUICtrlSetOnEvent($okbutton2, "OKButton2")
GUISetState (@SW_SHOW)
EndFunc

Func OKButton2()
msgbox(1,"It's Working!","Beginning date="&GUICtrlRead($Begin_Date)&", Ending Date ="&GUICtrlRead($End_Date))
$iDateCalc=_DateDiff('d',$byear&"/"&$bmonth&"/"&$bday&" 00:00:00",$eyear&"/"&$emonth&"/"&$eday&" 00:00:00")
winActivate("Untitled - Notepad")
For $i=0 to $iDateCalc
Dim $begin_date_parsed=StringSplit($Begin_Date,"/")
_arraydisplay($begin_date_parsed)

;the script here pops up with an actual, visible array, which I don't want.

dim $end_date_parsed=StringSplit($End_Date,"/")
_arraydisplay($end_date_parsed)

;the script here pops up with an actual, visible array, which I don't want.

$bmonth=$begin_date_parsed[1]
$bday=$begin_date_parsed[2]
$byear=$begin_date_year[3]
$emonth=$end_date_parsed[1]
$eday=$end_date_parsed[2]
$eyear=$end_date_year[3]

Send($bday&"."&$byear)
Send("{enter})
$begin_date=$begin_date+1
Next
EndFunc
    
Func CLOSEClicked()
  Exit
EndFunc

I'm not a coder -- all I know is HTML and how to record macros in ExCel. My IT department won't help me and I'm desperate to avoid doing all that busywork.

[Kerros]

Ok I found one error that is probally causing a one problem. Your Button2 was named the same as your function.

$okbutton2 = GUICtrlCreateButton("OK", 70, 50, 60)

Func OKButton2()

The script was not calling the function, but the button again.

I look at the rest of the code and see if there is something else I can find quickly

[napalmmk9]

Actually I just found a LOT of problems with it.

So don't bother with this iteration -- I got some of it down. If I need more, I'll post the revised code!

Thanks!