Jump to content
Sign in to follow this  
Vinny

Now a new Excel & Notepad issue

Recommended Posts

Vinny

I'm using Excel 2010, Trying to pass information from Excel to another program "Testing with Notepad".  Everything is working great except passing the dates.  The dates are coming passing as the long form for dates.  I need to pass it as mm/dd/yy.  I have tried everything I can think of. 

Here is all the info:

Autoit ver: 3.3.10.2

Excel 2010

Notepad for testing, if it works in notepad it will worki in the other software.

If FileExists('H:\EZ-Messenger-Imports\Data-Files\Data_Upload.xlsx') Then
      $oExcel = _ExcelBookOpen($oQLaw)
   Else
      End()
   EndIf

   $Acct = _ExcelReadSheetToArrayex($oExcel, 2, 1, 0, 0, True) ;JobID
   $a1 = _ExcelReadSheetToArrayex($oExcel, 2, 2, 0, 0, True) ;Servee
   $a2 = _ExcelReadSheetToArrayex($oExcel, 2, 3, 0, 0, True) ;ClientReference
   $a3 = _ExcelReadSheetToArrayex($oExcel, 2, 4, 0, 0, True) ;DateCompleted
   $a4 = _ExcelReadSheetToArrayex($oExcel, 2, 5, 0, 0, True) ;MannerOfService
   $a5 = _ExcelReadSheetToArrayex($oExcel, 2, 6, 0, 0, True) ;AddressLine1
   $a6 = _ExcelReadSheetToArrayex($oExcel, 2, 7, 0, 0, True) ;AddressLine2
   $a7 = _ExcelReadSheetToArrayex($oExcel, 2, 8, 0, 0, True)  ;City
   $a8 = _ExcelReadSheetToArrayex($oExcel, 2, 9, 0, 0, True) ;State
   $a9 = _ExcelReadSheetToArrayex($oExcel, 2, 10, 0, 0, True)  ;Zip
   $a10 = _ExcelReadSheetToArrayex($oExcel, 2, 11, 0, 0, True)  ;ServiceComments
   $a11 = _ExcelReadSheetToArrayex($oExcel, 2, 12, 0, 0, True)  ;JobType
   $a12 = _ExcelReadSheetToArrayex($oExcel, 2, 13, 0, 0, True)  ;Description
   $a13 = _ExcelReadSheetToArrayex($oExcel, 2, 14, 0, 0, True) ;ServedAddressTyp
   $a14 = _ExcelReadSheetToArrayex($oExcel, 2, 15, 0, 0, True) ;PersonLeftWith
   $a15 = _ExcelReadSheetToArrayex($oExcel, 2, 16, 0, 0, True) ;Title
   $a16 = _ExcelReadSheetToArrayex($oExcel, 2, 17, 0, 0, True) ;eMailed
   $a17 = _ExcelReadSheetToArrayex($oExcel, 2, 18, 0, 0, True) ;AnswerDueDate
   $a18 = _ExcelReadSheetToArrayex($oExcel, 2, 19, 0, 0, True) ;Defendant
   $a19 = _ExcelReadSheetToArrayex($oExcel, 2, 20, 0, 0, True) ;File Date
   $a20 = _ExcelReadSheetToArrayex($oExcel, 2, 21, 0, 0, True) ;Case Number
   $a21 = _ExcelReadSheetToArrayex($oExcel, 2, 22, 0, 0, True) ;Cost(Billing)
   $a22 = _ExcelReadSheetToArrayex($oExcel, 2, 23, 0, 0, True) ;Legal Dismiss Date


      _ExcelBookClose($oExcel)

   For $i = 1 to Ubound($a2, 1) -1
   If Not $a2[$i][0] = "" Then
   $a32 = _DateTimeFormat($a19[$i][0], 2)
  ; WinActivate('EAGLE - Eagle by SquareTwo Financial')
   WinWaitActive('Untitled - Notepad')
   $note = ""
   MouseClick('left',1179,38,1)
   Send($a2[$i][0])  ;Account Number
   Send('{Enter}')
   Sleep(1500)
   ;WinWaitActive('Account')
   Send('^[') ;Legal Screen
   Sleep(2500)
   MouseClick('left', 480,123,1)
   Send('{Tab 4}')
   Send('{Enter}')
   Sleep(1500)
   Send('+{End}')
   Send($a20[$i][0]) ;Case Number
   Send('{Tab 3}')
   If $a8[$i][0] = 'AZ' Then
      Send('Arizona')
   ElseIf $a8[$1][0] = 'UT' Then
      Send('Utah')
   Else
      Send('Nevada')
   EndIf
   Send('{Tab 6}')
   Send('+{End}')
   Send(_DateTimeFormat($a19[$i][0])) ;Filing Date  Heres the issue spot and any date listed below
   Send('{Tab 2}')
   ;Send($a22[$i][0])  ; Legal Dismissal Date
   Send('{Tab 2}')
   Send($a21[$i][0]) ; Cost
   Send('{Tab 14}')
   Send('EZ-Messenger')
   Send('{Tab}')
   Send($a3[$i][0]) ; Serve Date
   Send('{Tab 2}')
   Send($a17[$i][0])  ; Answer Due Date
   Send('{Tab 10}')
   Send('Check for Answer Prepare App/Aff')  ;Next Action Needed
   MsgBox(0, 'Test', 'Did everything Work?')
   MouseClick('left', 386,799, 1)
   Sleep(1500)
   WinWaitActive('EAGLE - Eagle by SquareTwo Financial')
   MouseClick('left', 497,45, 1)
   Sleep(2000)
   MsgBox(0, 'Test', 'Test Worked')

 EndIf
   Next

Any help with this is greatly appreciated.

Share this post


Link to post
Share on other sites
computergroove

What is the date format in the excel sheet you are reading from? Do you want to convert it to your date format or can you convert the format in the excel document and then run your script?


Get Scite to add a popup when you use a 3rd party UDF -> http://www.autoitscript.com/autoit3/scite/docs/SciTE4AutoIt3/user-calltip-manager.html

Share this post


Link to post
Share on other sites
Vinny

The date format in Excel is "mm/dd/yy"  but it is still writing it as follows in Notepad "20140922000000"  Which I Need to be "09/22/14" or even "09/22/2014" would work as well as long as it is in the correct format, with no time added.

Share this post


Link to post
Share on other sites
Vinny

Sometimes I wonder how blind I am.  I figured it out using the _DateSetFormat.  It's working perfectly now, for both the testing in Notepad and the actual application.

Thanks for the help.  Just talking it though is what really is the helper for me.

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  

×