Jump to content

[Solved] Trying to insert @CRLF to RichText


Recommended Posts

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

 

 

#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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Link to comment
Share on other sites

  • Developers

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

SciTE4AutoIt3 Full installer Download page   - Beta files       Read before posting     How to post scriptsource   Forum etiquette  Forum Rules 
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Link to comment
Share on other sites

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

#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

Link to comment
Share on other sites

  • Developers
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?

SciTE4AutoIt3 Full installer Download page   - Beta files       Read before posting     How to post scriptsource   Forum etiquette  Forum Rules 
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Link to comment
Share on other sites

  • Developers

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

SciTE4AutoIt3 Full installer Download page   - Beta files       Read before posting     How to post scriptsource   Forum etiquette  Forum Rules 
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

  • knightstalker changed the title to [Solved] Trying to insert @CRLF to RichText

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