Jump to content
Sign in to follow this  
CyBoRgWaR

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

Recommended Posts

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 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

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

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

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)

 

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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...