Sign in to follow this  
Followers 0
bb01

Simple Replacer Script - But too much txt

9 posts in this topic

#1 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites

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

#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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


Are you telling me something I need to know or something I want to know?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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

Are you telling me something I need to know or something I want to know?

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