Jump to content
Sign in to follow this  

Excel to Excel

Recommended Posts

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  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:

#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")


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

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


$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


$oExcel2 = _ExcelBookNew()


_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)



;'$skipname = "Skip-"&$filename&"_tlo.xls"

;$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


Any help with this would be great.


Share this post

Link to post
Share on other sites

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

Share this post

Link to post
Share on other sites



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?

Share this post

Link to post
Share on other sites


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.

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