Simple Replacer Script - But too much txt

[bb01]

Ok folks, this is the problem.

I have a spreedsheet, that my replace script opens & replaces information in it..

For example one of the parts reads:-

$find16 = "Ans by Pager"
$replace16 =  "Advised that if needs exist or they have a question on their case, to contact their Program Specialist. Reminded them to submit verification forms based on the requirement in their service plan and the last week’s verifications must be submitted no later than the last working day of each month.  Message was delivered successfully"

However the text is too much & ends up being split into too cells, however a cell can take much more that this.. So is there a way of adding either an Enter into it, like have way..

$find16 = "Ans by Pager"
$replace16 =  "Advised that if needs exist or they have a question on their case, to contact their Program Specialist. "  & _
 @CRLF & " Reminded them to submit verification forms based on the requirement in their service plan and the last week’s verifications must be submitted no later than the last working day of each month.  Message was delivered successfully"

however this causes it to goto the next line, which isnt what i want...

I've been looking all over the place & just not having any luck

Any ideas??

Edited March 15, 2011 by bb01

[MrMitchell]

If you are using Excel you should include Excel.au3 and use the ExcelWriteCell() function to get the information into the cell. See following example...then see the help file for more on Excel.au3:

#include <Excel.au3>
$replace16 =  "Advised that if needs exist or they have a question on their case, to contact their Program Specialist. Reminded them to submit verification forms based on the requirement in their service plan and the last week’s verifications must be submitted no later than the last working day of each month.  Message was delivered successfully"
$oExcel = _ExcelBookNew()
_ExcelWriteCell($oExcel, $replace16, 1, 1)
Exit

Edited March 16, 2011 by MrMitchell

[bb01]

Even doing it that way, it still writes over 2 cells..

[iamtheky]

over the empty cells in the row, or in them??

if the adjoining cell is not empty does it reflect as you expect?

#include <Excel.au3>

$replace16 =  "Advised that if needs exist or they have a question on their case, to contact their Program Specialist. Reminded them to submit verification forms based on the requirement in their service plan and the last week’s verifications must be submitted no later than the last working day of each month.  Message was delivered successfully"
$oExcel = _ExcelBookNew()
_ExcelWriteCell($oExcel, $replace16, 1, 1)
_ExcelWriteCell($oExcel, " ", 1, 2)
_ExcelBookSaveAs ($oExcel , "c:\testWRITE.xls" , "xls" , 0 , 1)
Exit

Edited March 17, 2011 by iamtheky

[bb01]

when it fills in the cell, for example cell A3, it overlaps & moves the text from Cell A4 to A5..

Problem is, i have another script that reads straight afterwards & copys & pastes from the cells..

Heres a copy of the complete script (Have tried adding your parts & it still does the same):-

& yes i know its not a clean script, but it works well..

expandcollapse popup

#NoTrayIcon
#RequireAdmin
#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_icon=..\..\nfocus.ico
#AutoIt3Wrapper_outfile=..\..\replacerscript.exe
#AutoIt3Wrapper_Compression=4
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <IE.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <ProgressConstants.au3>
#include <WindowsConstants.au3>
#include <ButtonConstants.au3>
#include <GuiConstantsEx.au3>
#include <Date.au3>
#include <WindowsConstants.au3>
#include <_Zip.au3>

; Opens a search handle to find csv files in the current directory
$hSearch = FileFindFirstFile(@ScriptDir & "\*.csv")

; Check if the search handle is valid
If $hSearch = -1 Then
    MsgBox(0, "Error", "No Phonetree csv files found")
    Exit
EndIf

While 1
    ; Find the next file
    ; i.e. returns a string containing the value of 01-08-2010.csv
    $sFoundFile = FileFindNextFile($hSearch)
    If @error Then ExitLoop
    ; Create a full path using @ScriptDir and the found file
    $sFilePath = @ScriptDir & '\' & $sFoundFile

    ; The found file and the filepath are now available for use

    ExitLoop
WEnd

; Close the search handle
FileClose($hSearch)

;~ ; ************ END OF THE SEARCH SECTION, NOW YOU CAN EDIT BELOW THIS LINE ********************


Global $filename = @ScriptDir & '\' & $sFoundFile

$find1 = "Telco Msg"
$replace1 = "Unable to Deliver Message Successfully"

$find2 = "Not Selected"
$replace2 = "Unable to Deliver Message Successfully"

$find3 = "Not Called Yet"
$replace3 = "Unable to Deliver Message Successfully"

$find4 = "Not Accepted"
$replace4 = "Unable to Deliver Message Successfully"

$find5 = "No Connect"
$replace5 = "Unable to Deliver Message Successfully"

$find6 = "OGM Too Long"
$replace6 = "Unable to Deliver Message Successfully"

$find7 = "Max No Answers"
$replace7 = "Unable to Deliver Message Successfully"

$find8 = "Hung Up Early"
$replace8 = "Unable to Deliver Message Successfully"

$find9 = "Learned Machine"
$replace9 = "Unable to Deliver Message Successfully"

$find10 = "Fax or Modem"
$replace10 = "Unable to Deliver Message Successfully"

$find11 = "Call Failed!"
$replace11 = "Unable to Deliver Message Successfully"

$find12 = "Busy After Voice"
$replace12 = "Unable to Deliver Message Successfully"

$find13 = "No Answer"
$replace13 = "Unable to Deliver Message Successfully"

$find14 = "Blocked Number"
$replace14 = "Unable to Deliver Message Successfully"

$find15 = "Ans by Person"
$replace15 = "Advised that if needs exist or they have a question on their case, to contact their Program Specialist. Reminded them to submit verification forms based on the requirement in their service plan and the last week’s verifications must be submitted no later than the last working day of each month. Message was delivered successfully"

$find16 = "Ans by Pager"
$replace16 = "Advised that if needs exist or they have a question on their case, to contact their Program Specialist. Reminded them to submit verification forms based on the requirement in their service plan and the last week’s verifications must be submitted no later than the last working day of each month. Message was delivered successfully"

$find17 = "Ans by Machine"
$replace17 = "Advised that if needs exist or they have a question on their case, to contact their Program Specialist. Reminded them to submit verification forms based on the requirement in their service plan and the last week’s verifications must be submitted no later than the last working day of each month. Message was delivered successfully"

$find18 = "Telco Message"
$replace18 = "Unable to Deliver Message Successfully"

$find19 = "Ans by +Machine"
$replace19 = "Advised that if needs exist or they have a question on their case, to contact their Program Specialist. Reminded them to submit verification forms based on the requirement in their service plan and the last week’s verifications must be submitted no later than the last working day of each month. Message was delivered successfully"

$find20 = "9 Not Confirmed"
$replace20 = "Unable to Deliver Message Successfully"

$find21 = "Busy"
$replace21 = "Unable to Deliver Message Successfully"


;begin replacing phonetree message with those from above..

$msg1 = $find1

FileWrite($filename, $msg1 )



$retval1 = _ReplaceStringInFile($filename,$find1,$replace1)

if $retval1 = -1 then
    exit
else

endif

; ------------------ next replace ----------------------------
$msg2 = $find2

FileWrite($filename, $msg2 )



$retval2 = _ReplaceStringInFile($filename,$find2,$replace2)

if $retval2 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg3 = $find3

FileWrite($filename, $msg3 )



$retval3 = _ReplaceStringInFile($filename,$find3,$replace3)

if $retval3 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg4 = $find4

FileWrite($filename, $msg4 )



$retval4 = _ReplaceStringInFile($filename,$find4,$replace4)

if $retval4 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg5 = $find5

FileWrite($filename, $msg5 )



$retval5 = _ReplaceStringInFile($filename,$find5,$replace5)

if $retval5 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg6 = $find6

FileWrite($filename, $msg6 )



$retval6 = _ReplaceStringInFile($filename,$find6,$replace6)

if $retval6 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg7 = $find7

FileWrite($filename, $msg7 )



$retval7 = _ReplaceStringInFile($filename,$find7,$replace7)

if $retval7 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg8 = $find8

FileWrite($filename, $msg8 )



$retval8 = _ReplaceStringInFile($filename,$find8,$replace8)

if $retval8 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg9 = $find9

FileWrite($filename, $msg9 )



$retval9 = _ReplaceStringInFile($filename,$find9,$replace9)

if $retval9 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg10 = $find10

FileWrite($filename, $msg10 )



$retval10 = _ReplaceStringInFile($filename,$find10,$replace10)

if $retval10 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg11 = $find11

FileWrite($filename, $msg11 )



$retval11 = _ReplaceStringInFile($filename,$find11,$replace11)

if $retval11 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg12 = $find12

FileWrite($filename, $msg12 )



$retval12 = _ReplaceStringInFile($filename,$find12,$replace12)

if $retval12 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg13 = $find13

FileWrite($filename, $msg13 )



$retval13 = _ReplaceStringInFile($filename,$find13,$replace13)

if $retval13 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg14 = $find14

FileWrite($filename, $msg14 )



$retval14 = _ReplaceStringInFile($filename,$find14,$replace14)

if $retval14 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg15 = $find15

FileWrite($filename, $msg15 )



$retval15 = _ReplaceStringInFile($filename,$find15,$replace15)

if $retval15 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg16 = $find16

FileWrite($filename, $msg16 )



$retval16 = _ReplaceStringInFile($filename,$find16,$replace16)

if $retval16 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg17 = $find17

FileWrite($filename, $msg17 )



$retval17 = _ReplaceStringInFile($filename,$find17,$replace17)

if $retval17 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg18 = $find18

FileWrite($filename, $msg18 )



$retval18 = _ReplaceStringInFile($filename,$find18,$replace18)

if $retval18 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg19 = $find19

FileWrite($filename, $msg19 )



$retval19 = _ReplaceStringInFile($filename,$find19,$replace19)

if $retval19 = -1 then

    exit
else

endif

; ------------------ next replace ----------------------------
$msg20 = $find20

FileWrite($filename, $msg20 )



$retval20 = _ReplaceStringInFile($filename,$find20,$replace20)

if $retval20 = -1 then
    exit
else
endif

; ------------------ next replace ----------------------------
$msg21 = $find21

FileWrite($filename, $msg21 )



$retval21 = _ReplaceStringInFile($filename,$find21,$replace21)

if $retval21 = -1 then
    exit
else
endif

; ------------------ next replace ----------------------------


$msg = FileRead($filename, 1000)

ShellExecuteWait(@ScriptDir & "\MCS1loop.exe")
exit

& heres the excel sheet it reads from...

test.zip

[MrMitchell]

Maybe you could post two screen shots; one each of your expected and unexpected results?

[Mallie99]

bb01, can I ask why you're using AutoIT? MS Excel VBA should be more than capable of handling this functionality? Or is there more in the background?

On the subject of the code above, I'm guessing it's not complete since you're not actually writing anything back to the spreadsheet that I can see... As mentioned MrMitchell, if you use the Excel plugin you should specify the actual CELL you want to put the data into. Theoretically it should just _work_.

Is this data confidential? Can you post up an XLS with the issue in it and the supporting code? I guess we'd all understand if that's not possible in a public forum. If that's the case then I'd be happy to help you out further 1-2-1 if you still have trouble.

Mal

[Tvern]

I'm guessing the problem is that you're using a CVS file, while having a comma in your string. If you use xls instead, or place the string within quotes (on top of the ones around the string already) it'll probably work.

[Mallie99]

I'm guessing the problem is that you're using a CVS file, while having a comma in your string. If you use xls instead, or place the string within quotes (on top of the ones around the string already) it'll probably work.

Haha... Knew there'd be a simple solution there somewhere... Well spotted

There you go bb01... Problem sorted I'll guess!

Mal

Edited March 21, 2011 by Mallie99