Jump to content

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

 

Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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)

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...