CyBoRgWaR

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

11 posts in this topic

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.

 

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

 

Share this post


Link to post
Share on other sites



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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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... :( 

Share this post


Link to post
Share on other sites

Can you post an example Excel spreadsheet?

Share this post


Link to post
Share on other sites
SITENAME / SITEID / CTRLNE ADDITIONAL INFO TITLE/DESCRIPTION TYPE AL NO CIRCLE
a b c d e f

 

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

It works 

 

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

Share this post


Link to post
Share on other sites

#10 ·  Posted

Thanks all for your time .....

Share this post


Link to post
Share on other sites

#11 ·  Posted

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)

 

1 person likes this

Share this post


Link to post
Share on other sites

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