Copying from Excel sheet includes @CRLF in it ,how to remove it ?

[CyBoRgWaR]

As mentioned in the Subject  , when ever im copying in data from Excel which is includes with @CRLF.

need help to remove the additional space and to get data only value of the excel . means without any spaces.

 

expandcollapse popup

WinActivate("data.xls");Select excel
sleep (200)


sleep (100)
Send("^c");SITENAME / SITEID / CTRLNE
sleep (100)
$siteid = ClipGet()
send ("{RIGHT}")

sleep (100)
Send("^c");ADDITIONAL INFO
sleep (100)
$addinfo = ClipGet()
send ("{RIGHT}")

sleep (100)
Send("^c");TITLE/DESCRIPTION
sleep (100)
$title = ClipGet()
send ("{RIGHT}")

sleep (100)
Send("^c");TYPE
sleep (100)
$type = ClipGet()
send ("{RIGHT}")

sleep (100)
Send("^c");AL NO
sleep (100)
$alno = ClipGet()
send ("{RIGHT}")

sleep (100)
Send("^c");CIRCLE
sleep (100)
$V = ClipGet()
send ("{RIGHT}")

sleep (100)
Send("^c");Task Purpose
sleep (100)
$Tskp = ClipGet()
send ("{RIGHT}")

WinActivate ("Untitled - Notepad")
Sleep(1000)
MouseClick("left",875,347,1,4)
Sleep(1000)
send($siteid)
Sleep(800)
Sleep(800)
send($addinfo)
Sleep(800)
Sleep(800)
send($title)
Sleep(800)
send($type)
Sleep(800)
send($alno)
Sleep(800)

im getting o/p as below

a
b
c
d
e
rather than i need     abcde

 

[water]

Why don't you use the Excel UDF that comes with AutoIt?

[Subz]

StringStripWS($var, 8) ;~ Strips all white space, but I agree with water recommend using the Excel UDF.

[CyBoRgWaR]

3 minutes ago, water said:

Why don't you use the Excel UDF that comes with AutoIt?

Bro im not fimiliar with that one , im using some basics of AUTOIT...  

[Subz]

Can you post an example Excel spreadsheet?

[CyBoRgWaR]

SITENAME / SITEID / CTRLNE	ADDITIONAL INFO	TITLE/DESCRIPTION	TYPE	AL NO	CIRCLE
a	b	c	d	e	f

 

 

[Subz]

So all you want is to copy the content directly into notepad?  Or do you want it formatted in some way?

[CyBoRgWaR]

No need o format , just to send it 

if required i can use TAB in between sending Keys 

 

ex: a       b      c      d      e

using tab for big space

[CyBoRgWaR]

It works 

 

sleep (100)
Send("{F2}")
sleep (100)
send ("^+{HOME}")
sleep (100)
Send("^c");
sleep (100)
$Tskp = ClipGet()
sleep (100)
send ("{TAB}")

[CyBoRgWaR]

Thanks all for your time .....

[Subz]

Here you go doing it using Excel UDF added comments so hope it makes sense  :

#include <Array.au3>

;~ Include the Excel UDF
#include <Excel.au3>


;~ Connects to an existing Excel instance or creates a new Excel instance
Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox(16, "Excel UDF", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

;~ Workbook name to open/read
Local $sWorkbook = @ScriptDir & '\data.xls'
;~ Open Workbook $sWorkbook or Connect to existing Workbook $sWorkbook
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
    If @error Then Exit MsgBox(16, "Excel UDF", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

;~ Copy the cells used within $sWorkbook to an Array
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange)
    If @error Then Exit MsgBox(16, "Excel UDF", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

_ArrayDisplay($aResult)

;~ Chane the Array into a String
Local $sResult = _ArrayToString($aResult, @TAB)

;~ Save the results to a data.txt in the same folder
$hFileOpen = FileOpen(@ScriptDir & '\Data.txt', 2)
;~ Write $sResults to data.txt
FileWrite($hFileOpen, $sResult)
;~ Close and Save the file
FileClose($hFileOpen)