Jump to content

Excel to Excel


Vinny
 Share

Go to solution Solved by Vinny,

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

Link to comment
Share on other sites

  • Moderators

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

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

  • Solution

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.

Link to comment
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
 Share

  • Recently Browsing   0 members

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