Jump to content
Sign in to follow this  
napalmmk9

Assistance with Date Loop/Date Formats

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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

Kerros===============================================================How to learn scripting: Figure out enough to be dangerous, then ask for assistance.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)


Kerros===============================================================How to learn scripting: Figure out enough to be dangerous, then ask for assistance.

Share this post


Link to post
Share on other sites

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.

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

Share this post


Link to post
Share on other sites

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


Kerros===============================================================How to learn scripting: Figure out enough to be dangerous, then ask for assistance.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...