Jump to content
Sign in to follow this  
Vinny

Excel to Excel

Recommended Posts

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:

#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

Share this post


Link to post
Share on other sites
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
  • Like 1

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
232showtime

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

Edited by 232showtime

ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

Share this post


Link to post
Share on other sites
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.

  • Like 1

Share this post


Link to post
Share on other sites
232showtime

mark it as solved...


ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

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  

×