Excel to Excel

[Vinny]

Ok, I'm new to this forum but I have an issue I have not been able to figure out even reading all the Excel post.

To start I'm using version 3.3.10.2  it has been working great so far with no issues. 

I have written a script to take info from an Excel Spreadsheet and put only some of the data into another to update one of our softwares.  It works great, havn't had any issues with it so far.

Now I'm working on a new version but this time it has to take dates from one sheet and put them into the other.  The problem I'm having is it will not format properly into the currect date format for me. 

In the new Excel it formats it all as ########## or as text but not as dates:  Here is the code I use:

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>
#include <ClipBoard.au3>
#include <String.au3>
#include <GUIConstantsEx.au3>
#include <ExcelReadSheetToArrayEX.au3>

If Not WinExists("Microsoft Excel") Then
$message = "Select the Microsoft Excel Workbook that you would like to use."
$book = FileOpenDialog($message, "\\TVU Updates\TVU\NFL" & "\", "Excel Workbook (*.xlsx)", 1 + 2 )  ;update this path to the location of your TLO append files
    If @error Then
    MsgBox(0,"Error","No File Chosen"&@CR&@CR&"Now Exiting")

    Exit

    EndIf
$oExcel = _ExcelBookOpen($book)
If @error Then
    MsgBox(0,"Error","That was not a valid workbook"&@CR&@CR&"Now Exiting")
        Exit
    EndIf
WinWaitActive("Microsoft Excel")

$filename = StringTrimLeft(StringTrimRight($book, 11), 32)

EndIf

$Acct = _ExcelReadSheetToArrayex($oExcel, 2, 3, 0, 1, True) ;Acc
$a1 = _ExcelReadSheetToArrayex($oExcel, 2, 4, 0, 1, True) ;Date
$a2 = _ExcelReadSheetToArrayex($oExcel, 2, 5, 0, 1, True) ;Manner
$a3 = _ExcelReadSheetToArrayex($oExcel, 2, 6, 0, 1, True) ;Add1
$a4 = _ExcelReadSheetToArrayex($oExcel, 2, 7, 0, 1, True) ;Add2
$a5 = _ExcelReadSheetToArrayex($oExcel, 2, 8, 0, 1, True) ;City
$a6 = _ExcelReadSheetToArrayex($oExcel, 2, 9, 0, 1, True) ;State
$a7 = _ExcelReadSheetToArrayex($oExcel, 2, 10, 0, 1, True)  ;Zip
$a8 = _ExcelReadSheetToArrayex($oExcel, 2, 11, 0, 1, True) ;SerComm
$a9 = _ExcelReadSheetToArrayex($oExcel, 2, 12, 0, 1, True)  ;Jobtype
$a10 = _ExcelReadSheetToArrayex($oExcel, 2, 13, 0, 1, True)  ;Descript
$a11 = _ExcelReadSheetToArrayex($oExcel, 2, 14, 0, 1, True)  ;ServiceAddtype
$a12 = _ExcelReadSheetToArrayex($oExcel, 2, 15, 0, 1, True)  ;LeftWith
$a13 = _ExcelReadSheetToArrayex($oExcel, 2, 16, 0, 1, True) ;Title
$a14 = _ExcelReadSheetToArrayex($oExcel, 2, 18, 0, 1, True) ;Answerdue date

;_ExcelBookClose($oExcel,0,0)

$oExcel2 = _ExcelBookNew()

_ExcelSheetAddNew($oExcel2,"Sheet0")
_ExcelSheetDelete($oExcel2,"Sheet1")
_ExcelSheetDelete($oExcel2,"Sheet2")
_ExcelSheetDelete($oExcel2,"Sheet3")
_ExcelSheetActivate($oExcel2,"Sheet0")

_ExcelWriteCell($oExcel2, "Custfile",1,1)
_ExcelWriteCell($oExcel2, "DateCompleted",1,2)
_ExcelWriteCell($oExcel2, "MannerOfService",1,3)
_ExcelWriteCell($oExcel2, "Address1",1,4)
_ExcelWriteCell($oExcel2, "Address2",1,5)
_ExcelWriteCell($oExcel2, "City",1,6)
_ExcelWriteCell($oExcel2, "State",1,7)
_ExcelWriteCell($oExcel2, "Zip",1,8)
_ExcelWriteCell($oExcel2, "ServiceComment",1,9)
_ExcelWriteCell($oExcel2, "JobType",1,10)
_ExcelWriteCell($oExcel2, "Description",1,11)
_ExcelWriteCell($oExcel2, "ServedAddressType",1,12)
_ExcelWriteCell($oExcel2, "PersonLeftWith",1,13)
_ExcelWriteCell($oExcel2, "Title",1,14)
_ExcelWriteCell($oExcel2, "AnswerDueDate",1,15)

For $i = 1 to Ubound($Acct, 1) -1
If Not $Acct[$i][0] = "" Then
$note = ""
_ExcelRowInsert($oExcel2, 2,1)
_ExcelWriteCell($oExcel2, $Acct[$i][0],2,1)
$sFormat = "000000000000"
_ExcelNumberFormat($oExcel2, $sFormat, 2, 1, 2, 1)
_ExcelWriteCell($oExcel2, $a1[$i][0],2,2)
_ExcelNumberFormat($oExcel2, "MM-DD-YYYY", 2, 2, 2, 2)
_ExcelWriteCell($oExcel2, $a2[$i][0],2,3)
_ExcelWriteCell($oExcel2, $a3[$i][0],2,4)
_ExcelWriteCell($oExcel2, $a4[$i][0],2,5)
_ExcelWriteCell($oExcel2, $a5[$i][0],2,6)
_ExcelWriteCell($oExcel2, $a6[$i][0],2,7)
_ExcelWriteCell($oExcel2, $a7[$i][0],2,8)
_ExcelWriteCell($oExcel2, $a8[$i][0],2,9)
_ExcelWriteCell($oExcel2, $a9[$i][0],2,10)
_ExcelWriteCell($oExcel2, $a10[$i][0],2,11)
_ExcelWriteCell($oExcel2, $a11[$i][0],2,12)
_ExcelWriteCell($oExcel2, $a12[$i][0],2,13)
_ExcelWriteCell($oExcel2, $a13[$i][0],2,14)
_ExcelWriteCell($oExcel2, $a14[$i][0],2,15)

    EndIf

Next

;MsgBox(0,"$filename",$filename)
;'$skipname = "Skip-"&$filename&"_tlo.xls"
;MsgBox(0,"$skipname",$skipname)

;$skipname = "Skip-"&$filename&"_tlo.xls"
;_ExcelBookSaveAs($oExcel2.Application, "\\dc001\Collections\Vince\TLO Updates\TLO\"&$skipname, "xls", 0, 1)    ;update this path as an output location for the SQ2 uploads
;'_ExcelBookSaveAs($oExcel2.Application, "\\nas1\inventory\skip trace\_toSQ2\"&$skipname, "xls", 0, 1)   ;update this path as an output location for the SQ2 uploads


MsgBox(0,"Status","Complete.")

Any help with this would be great.

Thanks

[JLogan3o13]

You can set the formatting of your range of cells first. Something like this:

#include <Excel.au3>
 
$oApp = _Excel_Open()
$oWorkbook = _Excel_BookNew($oApp)
$oWorkbook.Worksheets("Sheet1").Columns("A").NumberFormat = "mm/dd/yyyy" ;set Column A to date format

[Vinny]

JLogan3o13,

 

Thanks for your reply.  I just gave it a try. 

The new Excel is still giving the ###### instead of the date but if you cklick on the cell it shows the date as  20140814000000.  I even attepmted to make the old Excel change to mm/dd/yyyy which worked perfectly.  The second date I didn't mess with yet(Trying to get the first one working first) it's still coming up like 2.01409E+13(the scientific numbering).  This is causing me to pull what little hair I have left out faster and faster.

Any other suggestions?

[232showtime]

adjust the width in your worksheet where your date is located....

Edited August 21, 2014 by 232showtime

[Vinny]

232showtime,

Thanks I tryied that also.  Never worked just kept adding more #### the biger the field was. 

I'm such a full.  I finally figured it out.  With the help of JLogan3o13 post.  I revesed the idea.

In the old Excel

#include <Excel.au3>   
$oApp = _Excel_Open() 
$oWorkbook = _Excel_BookNew($oApp) 
$oWorkbook.Worksheets("Sheet1").Columns("A").NumberFormat = "00000" ;set Column A to text format

Then in the new workbook

_ExcelWriteCell($oExcel2, $a1[$i][0],2,2)
$sFormat = "mm/dd/yyyy"
_ExcelNumberFormat($oExcel2, $sFormat, 2, 2, 2, 2)

It worked perfectly.  Now it turns the date into a text in the old workbook and when copied to the new it formats it back to a date.  It solved all the issues.

I just wish I had thought of it myself and sooner.

Thanks again for the posts to my question.

[232showtime]

mark it as solved...