Sign in to follow this  
Followers 0
Ace08

Problem with Excel

5 posts in this topic

uhm guys i have this script which opens a dbf file and place it to an array if the status of the record is cancelled remove it, if the ref# has a letter "c" on it just remove "c" here is what i did

#include <excel.au3>
#include <array.au3>

$file = FileOpenDialog ("Choose The File to be Converted.", @ScriptDir, "TransactionFile(*.CON)" , 2)
If @error Then
    Exit
Else
    Global $NewCont
    $oExcel = _ExcelBookOpen($file, 0) ; open excel in the background

    $aArray = _ExcelReadSheetToArray($oExcel, 1) ; start from the first row

    For $i = 1 to UBound($aArray)-1
        $TREFNO = $aArray[$i][1]
        $RMCODE = $aArray[$i][2]
        $BFCODE = $aArray[$i][3]
        $REMARKS1 = $aArray[$i][4]
        $TRTYPE = $aArray[$i][5]
        $REMARKS2 = $aArray[$i][6]
        $STATUS = $aArray[$i][7]
        If $TREFNO = "C_TREFNO" Then
            $NewCont = $NewCont & $TREFNO & "," & $RMCODE & "," & $BFCODE & "," & _
            $REMARKS1 & "," & $TRTYPE & "," & $REMARKS2 & "," & $STATUS & @CRLF
        EndIf
        If $STATUS = "CANCELLED" Then

        Else
            $NewTREFNO = StringReplace($TREFNO,"C", "")
            $NewTREFNO = StringReplace($NewTREFNO,"c", "")
            WriteCSV()
        EndIf
    Next
    _ExcelBookClose($oExcel) ; close excel
    CSVFile()
    $NewCont = ""
    MsgBox(0,"Notice","Finished Converting!")
EndIf
Func CSVFile()
    $UnFilePathC = StringLen($file)
    $UnFilePath = StringLeft($file, $UnFilePathC - 3) & "csv"
    If FileExists($UnFilePath) Then
        $Path = FileOpen($UnFilePath, 2)
    Else
        $Path = FileOpen($UnFilePath, 10)
    EndIf
    FileWriteLine($UnFilePath, $NewCont)
    FileClose($Path)
EndFunc

Func WriteCSV()
    If $TREFNO = "C_TREFNO" Then
    Else
        $NewCont = $NewCont & chr(34) & $NewTREFNO & chr(34) & "," & chr(34) & $RMCODE & chr(34) & "," & chr(34) & $BFCODE & chr(34) & "," & chr(34) & $REMARKS1 & chr(34) & "," & _
        chr(34) & $TRTYPE & chr(34) & "," & chr(34) & $REMARKS2 & chr(34) & "," & chr(34) & $STATUS & chr(34) & @CRLF
    EndIf
EndFunc

it has no problem when i open the new file with notepad but when i open it with excel some of the datas where displayed differently like for example 462000000996 became 4.62E+11 or 08 became 8.... i figured it might have something to do with how the cells are formated since they are in all General formating, can someone help me with this? Thank you so much for your help :x


Work smarter not harder.My First Posted Script: DataBase

Share this post


Link to post
Share on other sites



Yes - this is the formatting of excel cells + regional settings of your PC. Has nothing to do with Autoit.

If you want just change it from "general" to "text".

Share this post


Link to post
Share on other sites

o.O so there is no way to set it while im writing to csv format?


Work smarter not harder.My First Posted Script: DataBase

Share this post


Link to post
Share on other sites

To CSV no but if you use the excel UDF and COM objects i think you can to do it to xls or xlsx.

Share this post


Link to post
Share on other sites

i see.... i see, in that case Thanks a lot Juvigy ive done a manual excel to csv conversion and the results where the same, 462000000996 became 4.62E+11 and 08 became 8 :x


Work smarter not harder.My First Posted Script: DataBase

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