Jump to content

Problem with Excel


Ace08
 Share

Recommended Posts

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

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