Jump to content

Now a new Excel & Notepad issue


Go to solution Solved by Vinny,

Recommended Posts

Posted

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.

Posted

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.

  • Solution
Posted

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

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
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...