Page 1 of 1 1
Topic Options
#188349 - 2008-06-25 02:13 PM Adding multiple lines into one cell in Excel
spocky184 Offline
Fresh Scripter

Registered: 2007-07-12
Posts: 5
Hi,

i like to read data out of an array and want to put the information in only one cell in excel. Is that possible ?

Example (but overwrites the contents of the cell):

$oXL = CreateObject("EXCEL.application")
$oXL.Visible = 1
$rc = $oXL.Workbooks.Add
$array = "Line1" , "Line2", "Line3"
$i = 0
For Each $item in $array
$oXL.Cells(1, 1).Value = $array[$i]
$i = $i + 1
Next

The result is Text "Line3" in the Cell.
But i want this result in the cell:
"Line1"
"Line2"
"Line3"

Thanx
spocky184

Top
#188350 - 2008-06-25 02:51 PM Re: Adding multiple lines into one cell in Excel [Re: spocky184]
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
Here is one way:
 Code:
$oXL = CreateObject("EXCEL.application")
$oXL.Visible = 1
$rc = $oXL.Workbooks.Add
$array = "Line1" , "Line2", "Line3"
 
$oXL.Cells(1, 1).Value = Join($array,Chr(10))

Top
#188351 - 2008-06-25 02:56 PM Re: Adding multiple lines into one cell in Excel [Re: Richard H.]
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
Here is another method which extends the value:
 Code:
$oXL = CreateObject("EXCEL.application")
$oXL.Visible = 1
$rc = $oXL.Workbooks.Add
$array = "Line1" , "Line2", "Line3"
 
For Each $sValue in $array
	$oXL.Cells(1, 1).Value = $oXL.Cells(1, 1).Value+IIF($oXL.Cells(1, 1).Value="","",Chr(10))+$sValue
Next

Top
#188352 - 2008-06-25 03:28 PM Re: Adding multiple lines into one cell in Excel [Re: spocky184]
spocky184 Offline
Fresh Scripter

Registered: 2007-07-12
Posts: 5
Thank you very much, your first reply works just fine for me !!!

Thanx
spocky184

Top
Page 1 of 1 1


Moderator:  Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 405 anonymous users online.
Newest Members
M_Moore, BeeEm, min_seow, Audio, Hoschi
17883 Registered Users

Generated in 0.053 seconds in which 0.024 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org