Sign in to follow this  
Followers 0
inote

how to trim Col0&Row0

14 posts in this topic

#1 ·  Posted (edited)

#include
#include

$sFilePath1 = @ScriptDir & "\TEP.HD#1.xlsx" ;This file should already exist
$oExcel = _ExcelBookOpen($sFilePath1,@SW_MAXIMIZE)
Send("{CTRLDOWN}{PGDN}{CTRLUP}")
sleep (1000)

$aArray = _ExcelReadSheetToArray($oExcel,1,1,41,4) ;Using Default Parameters
_ArrayDisplay($aArray, "Array using Default Parameters")
MsgBox(0, "Exiting", "Press OK to Save File and Exit")

_ArrayToClip($aArray)
ClipGet()

_ExcelBookClose($oExcel) ; And finally we close out
If @error = 1 Then
MsgBox(0, "Error!", "Unable to Create the Excel Object")
Exit
ElseIf @error = 2 Then
MsgBox(0, "Error!", "File does not exist - Shame on you!")
Exit
EndIf

Here is my Array result

Posted Image

How can I ?

- want to trim Row[0] and Col[0] that's added , i want to use only my data (Col1 Row 1)

- Copy Array to Clipboard

PS. i try to use

_ArrayToClip($aArray)
ClipGet()

and Paste on Notepad but nothing there

Thx so much

Edited by inote

Share this post


Link to post
Share on other sites



What is

Send("{CTRLDOWN}{PGDN}{CTRLUP}")
supposed to do?


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

What is

Send("{CTRLDOWN}{PGDN}{CTRLUP}")
supposed to do?

In Excel i've 2 sheet >>> so i send this for go to 2nd sheet :sweating:

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

- want to trim Row[0] and Col[0] that's added , i want to use only my data (Col1 Row 1)

Try
$aArray = _ExcelReadSheetToArray($oExcel,1,1,41,4, True) ;Using Default Parameters
Edited by water

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

In Excel i've 2 sheet >>> so i send this for go to 2nd sheet :sweating:

Use
_ExcelSheetActivate($oExcel, 2)
to make it more reliable.

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

Try

$aArray = _ExcelReadSheetToArray($oExcel,1,1,41,4, True) ;Using Default Parameters

that's the same result :sweating:

Share this post


Link to post
Share on other sites

Use

_ExcelSheetActivate($oExcel, 2)
to make it more reliable.

Thx so much :thumbsup:

Share this post


Link to post
Share on other sites

- Copy Array to Clipboard

You have to use function "ClipPut" to write the array to the clipboard.

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

To delete the first row you need to use

$aArray = _ExcelReadSheetToArray($oExcel,1,1,41,4) ;Using Default Parameters
_ArrayDelete($aArray, 0)


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

You have to use function "ClipPut" to write the array to the clipboard.

so i've to use ClipPut($aArray) , isn't it ?

Share this post


Link to post
Share on other sites

ClipPut expects a string. In a perfect world you would use _ArrayToString to convert the array to a string. Unfortunataley _ExcelSheetReadToArray returns a two-dimensional array. _ArrayToString only allows one-dimensional arrays.

You either have to search the forum for a version of _ArrayToString that supports 2D-arrays or write it yourself.


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

To delete the first row you need to use

$aArray = _ExcelReadSheetToArray($oExcel,1,1,41,4) ;Using Default Parameters
_ArrayDelete($aArray, 0)

Nothing change there's still 1st row

ClipPut expects a string. In a perfect world you would use _ArrayToString to convert the array to a string. Unfortunataley _ExcelSheetReadToArray returns a two-dimensional array. _ArrayToString only allows one-dimensional arrays.

You either have to search the forum for a version of _ArrayToString that supports 2D-arrays or write it yourself.

i'm a newbie that's too hard for me , i don't understand

but I've search and find How can i use it for my code :doh:

Share this post


Link to post
Share on other sites

Nothing change there's still 1st row

But the row saying "41 4" is gone?

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

But the row saying "41 4" is gone?

Posted Image

No , :sweating:

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  
Followers 0