_Excel_RangeWrite with line break

[Basement]

Hi,

 

im writing from an array to an excel sheet and want to use line breaks.

 

The following piece of code does not do this job (instead of the line break the "[...]chr(10)[...]" is displayed in the cell:

 

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and create a new workbook
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($oAppl)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf

; *****************************************************************************
; Write a 1D array to the active sheet in the active workbook
; *****************************************************************************
Local $aArray1D[3] = ['"Zeile 1 " & vbLf & " Zeile 2"', 'B', 'CC']
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray1D, "A3")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "1D array successfully written.")

What am i doing wrong?

Any help is appreciated.

 

Best regards

 

Daniel

[water]

The help file explains how to do  

[Basement]

  On 4/7/2016 at 10:14 AM, water said:

 

Expand  

Hi,

sorry, i asked to quick ;-)

When i use  @CRLF in my previous example the line break works fine.

But my Array-Input comes from a csv file.

In the CSV-File i want to define the line breaks.

After reading it to an array (and processing it) the line breaks in the output Excel file should be overtaken.

But this is what i get (Test.csv=Input file, Mappe8.csv= output file)

Now unfortunately the help file is not helping me ;-(

Do you have an idea?

 

Best regards

 

Daniel

[water]

If I understand you correctly you get a line like "Line 1 @CRLF & Line 2" from the CSV.
To have it displayed in two lines in Excel I would use the following to translate the string into a "real" @CRLF:

$sRealString = Execute("Line1 & @CRLF & Line 2")

Then write $sRealString to Excel.

[Basement]

Hi.

 

i have tried your example with the following 4 versions:

Version 1: Line 1 & @CRLF & Line 2
Version 2: "Line 1" & @CRLF & "Line 2"
Version 3: Line 1' & @CRLF & 'Line 2'
Version 4: "Line 1 & @CRLF & Line 2"

Version 1 and 3 result in an empty string ;-(

 

Here's my code:

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <File.au3>


; Create application object and create a new workbook
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($oAppl)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf

; *****************************************************************************
; Write a 1D array to the active sheet in the active workbook
; *****************************************************************************
Local $aArray
_FileReadToArray("Test.csv", $aArray);
_ArrayDisplay($aArray);
For $y=1 to UBound($aArray)-1 Step +1
   MsgBox(0, "Line prior to conversion", $aArray[$y])
    $sRealString= Execute($aArray[$y])
    MsgBox(0, "Line after conversion", $sRealString)
   _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $sRealString, "A"&$y)
Next
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "1D array successfully written.")

Seems that i'm too blind to find the solution this day...maybe tommorrow will be better ;-)

[water]

Or try:

$sRealString = StringReplace("Line1 & @CRLF & Line 2", " & @CRLF & ", @CRLF)

 

[Basement]

Thanx!!!

This works perfectly for me.

You made my day ;-)

 

Best wishes

Daniel

[water]