[Solved] Trying to insert @CRLF to RichText

[knightstalker]

ok, so I completed this with excel and word UDF and it worked great but the runtime was huge.  So I found a way to generate .rtf files instead of .docx files which in turn the files were smaller, program ran faster, and they were still formatted.  However, there is a condition were I need to add data to the document if more data is found in the spreadsheet.  If more data is needed then replace to text then add more markers to show more text needs to be inserted.

Normally I would just go with what works originally, but I am generating 200,000 documents.

All I want to do is add @CRLF to the end of "$Var7" and "$Var8".  $Var7 and $Var8 do get replaced properly but no hard return after the text.  It just runs on like there was nothing there.  Msgbox text of the variables indicate a @CRLF but nothing is passed to $Edit2 control.

 

Any input is greatly appreciated

 

 

expandcollapse popup

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiRichEdit.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <FileConstants.au3>

$Form1 = GUICreate("Test", 800, 600) ;Creates The GUI and makes it centered in the screen
Global $Edit1 = _GUICtrlRichEdit_Create($Form1, "", 8, 8, 393, 541, BitOR($ES_MULTILINE, $WS_VSCROLL, $ES_AUTOVSCROLL, $WS_HSCROLL, $ES_AUTOHSCROLL)) 
Global $Edit2 = _GUICtrlRichEdit_Create($Form1, "", 400, 8, 393, 541, BitOR($ES_MULTILINE, $WS_VSCROLL, $ES_AUTOVSCROLL, $WS_HSCROLL, $ES_AUTOHSCROLL)) 
GUICtrlSetData(-1, "")

$Button2 = GUICtrlCreateButton("Save Text", 176, 564, 113, 25) ;creates a button
$Button3 = GUICtrlCreateButton("Close", 304, 564, 89, 25) ;creates a button
$Button4 = GUICtrlCreateButton("Run",400,564,89,25) ;Create Run Button
$Label1 = GUICtrlCreateLabel("0",650,564,100,25)


 _GUICtrlRichEdit_StreamFromFile($Edit1, "C:\Users\knightstalker\Desktop\PMTA\testing.rtf")
 $s = _GUICtrlRichEdit_StreamToVar($Edit1)
_GUICtrlRichEdit_StreamFromVar($Edit2, $s)
$counter = 1

; Create application object and open an example workbook
$oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
$oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\knightstalker\Desktop\PMTA\Extras\unc70602-Product_Listings-file_1.xlsx")
;Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\knightstalker\Desktop\PMTA\unc70602-Product_Listings-07252019-124028.xlsx")

If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf


$aRange = _Excel_RangeRead($oWorkbook, 2, "A2:T207", 1)
    ;_ArrayDisplay($aRange)
$Varx = ""
GUISetState(@SW_SHOW) ;Makes the GUI visible
for $x = 1 to 3

     Local $Var3        =   _Excel_RangeRead($oWorkbook, 1, "A" & $x+1, 1)
     $Varx = "$Var3"
     $Vary = $Var3
    runner($s)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)

     Local $Var1        =   _Excel_RangeRead($oWorkbook, 1, "D" & $x+1, 1)
     $Varx = "$Var1"
     $Vary = $Var1
    runner($s)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)

     Local $Var2        =   _Excel_RangeRead($oWorkbook, 1, "G" & $x+1, 1)
     $Varx = "$Var2"
     $Vary = $Var2
    runner($s)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)

     ;MsgBox($MB_SYSTEMMODAL,"Range",$Var1 & @CRLF & $Var2 & $Var3)

     $splitSize = StringSplit($Var1,",")
     $Var4              =   $splitSize[3]
     $Varx = "$Var4"
     $Vary = $Var4
    runner($s)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)

     $Var6              =   $splitSize[2]
     $Varx = "$Var6"
     $Vary = $Var6
    runner($s)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)

     Local $Var9        =   _Excel_RangeRead($oWorkbook, 1, "F" & $x+1, 1)
     $Varx = "$Var9"
     $Vary = $Var9
    runner($s)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)

     Local $Var5a       =   _Excel_RangeRead($oWorkbook, 1, "E" & $x+1, 1)



    ;MsgBox($MB_SYSTEMMODAL,"Range",$Var1 & @CRLF & $Var2 & @CRLF & $Var3 & @CRLF & $Var4 & @CRLF & $Var5 & @CRLF & $Var6 & @CRLF & $Var7 & @CRLF & $splitSize[1] & $Var2)


    $splitFlavor = StringSplit($Var5a,",")
     $Var5 = ""
     $Var7 = ""
     $Var8 = ""

     for $y = 1 to Ubound($splitFlavor)-1
         $iIndex = _ArraySearch($aRange, $splitFlavor[$y], 0, 0, 0, 1, 1, 5)
         $Var7 =  $aRange[$iIndex][4] & " purchased by " & $aRange[$iIndex][18]
         $Var8 =  $aRange[$iIndex][19]
        if $y <> UBound($splitFlavor)-1 Then

            $Var8 =  $aRange[$iIndex][19] & @CRLF & " $Var7" & @CRLF & " $Var8" & @CRLF
            

         Else
             $Var8 =  $aRange[$iIndex][19]
         EndIf




             $Varx = "$Var7"
             $Vary = $Var7 & @CRLF
            runner($s)
            _GUICtrlRichEdit_StreamFromVar($Edit2, $s)

             $Varx = "$Var8"
             $Vary = $Var8
            runner($s)
            _GUICtrlRichEdit_StreamFromVar($Edit2, $s)
        ;MsgBox($MB_SYSTEMMODAL,"Range",$Var7 & @CRLF & $Var8)

     Next
    $Varx = "||"
    runner2($s)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)

    _save()
    $s = _GUICtrlRichEdit_StreamToVar($Edit1)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)
Next


While 1
    $msg = GUIGetMsg() ;gets the GUI "changes"
    Switch $msg
        Case $GUI_EVENT_CLOSE ;on click on the X
            _GUICtrlRichEdit_Destroy($Edit1) ;VERY IMPORTANT!
            Exit
        Case $Button2
            _save() ;calls function _save()
        Case $Button3
            _GUICtrlRichEdit_Destroy($Edit1) ;IMPORTANT before exit!
            Exit
        Case $Button4
            runner($s)
            _GUICtrlRichEdit_StreamFromVar($Edit2, $s)
            _save()
            $s = _GUICtrlRichEdit_StreamToVar($Edit1)
            _GUICtrlRichEdit_StreamFromVar($Edit2, $s)
    EndSwitch ;Ends the switch
WEnd ;ends the while

Func _save()

    $savetofile =  _GUICtrlRichEdit_StreamToFile($Edit2, "C:\Users\knightstalker\Desktop\PMTA\Docs\testing" & $counter & ".rtf")
    $counter += 1
     GUICtrlSetData($Label1, $counter)

EndFunc
Func runner(ByRef $s1)

    Local $sString = StringReplace($s1, $Varx, $Vary)
    $s1 = $sString

EndFunc
Func runner2(ByRef $s1)

    $Vary = "." & @CRLF & "."
    Local $sString = StringReplace($s1, $Varx, $Vary)
    $s1 = $sString

EndFunc

 

[Neutro]

Hey,

Perhaps what you could try is copy paste your script to a new file, run it once to get the content of the var where CRLF is not displayed properly then delete everything but the creation of the GUI, paste the text into 

_GUICtrlRichEdit_SetText 

and check if it's working or not.

This should be an easier way to see where the problem is.

[Nine]

6 hours ago, knightstalker said:

All I want to do is add @CRLF to the end of "$Var7" and "$Var8"

_GUICtrlRichEdit_AppendText ($hRichEdit, @CRLF)

Maybe ?

[knightstalker]

tried the above nine in several different ways. same result.  I can add alphanumerics all day, but not @CRLF, @CR, @LF

[Nine]

Well it works for me.  Unless you are doing something different, it will add a \par at the end of the richtext field.

[junkew]

Not sure but maybe doing html files is easier? Why do you want to generate that many files?

[Jos]

Have you tried adding the  $ES_WANTRETURN to the controls?

...if that doesn't work then please post a simple example of this issue we can work with.  

Jos

Edited February 19, 2020 by Jos

[knightstalker]

nope, same result  Here is the code again and test files to run and see what is going on

expandcollapse popup

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiRichEdit.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <FileConstants.au3>

$Form1 = GUICreate("Test", 800, 600) ;Creates The GUI and makes it centered in the screen
Global $Edit1 = _GUICtrlRichEdit_Create($Form1, "", 8, 8, 393, 541, BitOR($ES_WANTRETURN, $ES_MULTILINE, $WS_VSCROLL, $ES_AUTOVSCROLL, $WS_HSCROLL, $ES_AUTOHSCROLL)) ;Creates The richtext edit and makes it global, so that every function can read it out
Global $Edit2 = _GUICtrlRichEdit_Create($Form1, "", 400, 8, 393, 541, BitOR($ES_WANTRETURN, $ES_MULTILINE, $WS_VSCROLL, $ES_AUTOVSCROLL, $WS_HSCROLL, $ES_AUTOHSCROLL)) ;Creates The richtext edit and makes it global, so that every function can read it out
GUICtrlSetData(-1, "")


$Button3 = GUICtrlCreateButton("Close", 304, 564, 89, 25) ;creates a button

$Label1 = GUICtrlCreateLabel("0",650,564,100,25)


 _GUICtrlRichEdit_StreamFromFile($Edit1, "testing.rtf")
 $s = _GUICtrlRichEdit_StreamToVar($Edit1)
_GUICtrlRichEdit_StreamFromVar($Edit2, $s)
$counter = 1


$oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
$oWorkbook = _Excel_BookOpen($oExcel, "Test_data.xlsx")
;Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\knightstalker\Desktop\PMTA\unc70602-Product_Listings-07252019-124028.xlsx")

If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf


$aRange = _Excel_RangeRead($oWorkbook, 2, "A2:T207", 1)
    ;_ArrayDisplay($aRange)
$Varx = ""
GUISetState(@SW_SHOW)
for $x = 1 to 3

     Local $Var3        =   _Excel_RangeRead($oWorkbook, 1, "A" & $x+1, 1)
     $Varx = "$Var3"
     $Vary = $Var3
    runner($s)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)

     Local $Var1        =   _Excel_RangeRead($oWorkbook, 1, "D" & $x+1, 1)
     $Varx = "$Var1"
     $Vary = $Var1
    runner($s)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)

     Local $Var2        =   _Excel_RangeRead($oWorkbook, 1, "G" & $x+1, 1)
     $Varx = "$Var2"
     $Vary = $Var2
    runner($s)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)



     $splitSize = StringSplit($Var1,",")
     $Var4              =   $splitSize[3]
     $Varx = "$Var4"
     $Vary = $Var4
    runner($s)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)

     $Var6              =   $splitSize[2]
     $Varx = "$Var6"
     $Vary = $Var6
    runner($s)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)

     Local $Var9        =   _Excel_RangeRead($oWorkbook, 1, "F" & $x+1, 1)
     $Varx = "$Var9"
     $Vary = $Var9
    runner($s)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)

     Local $Var5a       =   _Excel_RangeRead($oWorkbook, 1, "E" & $x+1, 1)



    ;MsgBox($MB_SYSTEMMODAL,"Range",$Var1 & @CRLF & $Var2 & @CRLF & $Var3 & @CRLF & $Var4 & @CRLF & $Var5 & @CRLF & $Var6 & @CRLF & $Var7 & @CRLF & $splitSize[1] & $Var2)


    $splitFlavor = StringSplit($Var5a,",")
     $Var5 = ""
     $Var7 = ""
     $Var8 = ""

     for $y = 1 to Ubound($splitFlavor)-1
         $iIndex = _ArraySearch($aRange, $splitFlavor[$y], 0, 0, 0, 1, 1, 5)
         $Var7 =  $aRange[$iIndex][4] & " purchased by " & $aRange[$iIndex][18]
         $Var8 =  $aRange[$iIndex][19]
        if $y <> UBound($splitFlavor)-1 Then

            ;$Var8 =  $aRange[$iIndex][19] & @CRLF & " $Var7" & @CRLF & " $Var8" & @CRLF
            $Var8 =  $aRange[$iIndex][19] & @CRLF & "$Var7" & @CRLF & "$Var8"

         Else
             ;$Var8 =  $aRange[$iIndex][19]
         EndIf




             $Varx = "$Var7"
             $Vary = $Var7 & @CRLF
            runner($s)
            _GUICtrlRichEdit_StreamFromVar($Edit2, $s)

             $Varx = "$Var8"
             $Vary = $Var8
            runner($s)
            _GUICtrlRichEdit_StreamFromVar($Edit2, $s)
        MsgBox($MB_SYSTEMMODAL,"Range",$Var7 & @CRLF & $Var8)

     Next

    _save()
    $s = _GUICtrlRichEdit_StreamToVar($Edit1)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)
Next


While 1
    $msg = GUIGetMsg() ;gets the GUI "changes"
    Switch $msg
        Case $GUI_EVENT_CLOSE ;on click on the X
            _GUICtrlRichEdit_Destroy($Edit1) ;VERY IMPORTANT!
            Exit
        Case $Button3
            _GUICtrlRichEdit_Destroy($Edit1) ;IMPORTANT before exit!
            Exit

    EndSwitch ;Ends the switch
WEnd ;ends the while

Func _save()


    $savetofile =  _GUICtrlRichEdit_StreamToFile($Edit2, "output" & $counter & ".rtf")
    $counter += 1
     GUICtrlSetData($Label1, $counter)

EndFunc
Func runner(ByRef $s1)

    Local $sString = StringReplace($s1, $Varx, $Vary)
    $s1 = $sString

EndFunc
Func runner2(ByRef $s1)

    $Vary = "." & @CRLF & "."
    Local $sString = StringReplace($s1, $Varx, $Vary)
    $s1 = $sString

EndFunc

 

output1.rtf output2.rtf output3.rtf Test Rich Text_debug.au3 Test_data.xlsx testing.rtf

[Jos]

What about you strip this all down to a bare minimum that shows your issue?

Jos

[knightstalker]

This is stripped

 

[knightstalker]

8 hours ago, junkew said:

Not sure but maybe doing html files is easier? Why do you want to generate that many files?

The requirement is formatted text files.  Using Word UDF is working fine but the overhead for word gives me a runtime of about 250 hours

[Jos]

34 minutes ago, knightstalker said:

This is stripped

 

Can't be as your "only" issue is sending  @crlf to a GUIcontrol, so should be only a 20 lines at most. and don't need at the excel stuff..... right?

[junkew]

Html is plain formatted text and much easier and faster to handle then rtf. Html is easy to convert to many other formats.

 

[knightstalker]

9 minutes ago, Jos said:

Can't be as your "only" issue is sending  @crlf to a GUIcontrol, 

Is it?

[Jos]

Thought that was what you title said, but I can't be bothered at this moment to wade through lengthy code and setups just to replicate your issue. Others might have the time and willing to put in the effort...who knows.  

Jos

 

Edited February 19, 2020 by Jos

[knightstalker]

Just now, Jos said:

Thought that was what you title said, but I can't be bothered at this moment to wade through lengthy code and setups just to replicate your issue. Other might have the time and willing to put in the effort...who know.  

Jos

 

then I appreciate your time.  Have a nice day

 

[Nine]

Alright, I understand what you are trying to do.  You got a rtf template and you replacing $var1-9 with a string inside the stream.  All goes well until you hit 7 and 8 where there is multiple  lines to replace with a single $var.  Since you are working with the stream, you cannot just put @CRLF, you need to add stream code with it.  So this following code will work for you.  But I must say that you should definitely reconsider optimizing your code.

For $y = 1 To UBound($splitFlavor) - 1
    $iIndex = _ArraySearch($aRange, $splitFlavor[$y], 0, 0, 0, 1, 1, 5)
    $Var7 = $aRange[$iIndex][4] & " purchased by " & $aRange[$iIndex][18]
    $Var8 = $aRange[$iIndex][19]
    If $y <> UBound($splitFlavor) - 1 Then
      $Var8 = $aRange[$iIndex][19] & "\par" & @CRLF & "$Var7" & "\par" & @CRLF & "$Var8"
    EndIf

    $Varx = "$Var7"
    $Vary = $Var7
    runner($s)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)

    $Varx = "$Var8"
    $Vary = $Var8
    runner($s)
    _GUICtrlRichEdit_StreamFromVar($Edit2, $s)
  Next

 

[knightstalker]

Solved, Thanks Nine

[Nine]

Edited February 20, 2020 by Nine

[junkew]

Wherever you are creating with \par a new paragraph and crlf you could use \line to insert a new next line. You are dealing with rtf format and that doesnt understand crlf.