Hoth2001

Select Column in Excel

16 posts in this topic

Hello all.....I've been tinkering through all of the excel help files.

everything I see seems to always make references to both Row and Column.  For example "A2" etc.

 

Here is what I'm trying to do:

Select a column in one workbook

Copy that column

and then Paste that column in a specific column on another workbook.

 

I was going to start to go down the manual process of a bunch of MouseClick commands...but there has to be a better way??

Is there an excel function I don't know about that can target a specific column and perform actions like copy/paste??

Share this post


Link to post
Share on other sites



_Excel_RangeCopyPaste: Cuts or copies one or multiple cells, rows or columns to a range or from/to the clipboard

Example: To process column D use "D:D" as the range specifier (parameter $vSourceRange).


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
19 minutes ago, water said:

_Excel_RangeCopyPaste: Cuts or copies one or multiple cells, rows or columns to a range or from/to the clipboard

Example: To process column D use "D:D" as the range specifier (parameter $vSourceRange).

 

hmmmm...something not working with this code:

I'm trying to open a new file

open an exisiting file

Copy column B from existing file

Paste into Column A on new file

THe files open...but the copy/paste doesn't execute.

What's wrong with the code?

    Local $oExcel =_Excel_Open()
            _Excel_BookNew($oExcel)
            sleep(300)


            Local $oExcel_1 = _Excel_Open()
            Local $sWorkbook = ($aArray[1][3])
            Local $oWorkbook = _Excel_BookOpen($oExcel_1,$sWorkbook)
            sleep(300)

            _Excel_RangeCopyPaste($sWorkbook,"B:B")
            _Excel_RangeCopyPaste($oExcel,"A:A", $xlPasteValues)

 

Share this post


Link to post
Share on other sites

Should be something like this (untested):

Local $oExcel =_Excel_Open()
Local $oWorkbookOut = _Excel_BookNew($oExcel)
Local $sWorkbook = $aArray[1][3]
Local $oWorkbookIn = _Excel_BookOpen($oExcel, $sWorkbook)
_Excel_RangeCopyPaste($oWorkbookIn, "B:B")
_Excel_RangeCopyPaste($oWorkbookIn, Default, $oWorkbookOut.ActiveSheet.Range("A1"), Default, $xlPasteValues)

If it doesn't work, please add some error checking (@error and @extended are set in case of a problem).


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
16 minutes ago, water said:

Should be something like this (untested):

Local $oExcel =_Excel_Open()
Local $oWorkbookOut = _Excel_BookNew($oExcel)
Local $sWorkbook = $aArray[1][3]
Local $oWorkbookIn = _Excel_BookOpen($oExcel, $sWorkbook)
_Excel_RangeCopyPaste($oWorkbookIn, "B:B")
_Excel_RangeCopyPaste($oWorkbookIn, Default, $oWorkbookOut.ActiveSheet.Range("A1"), Default, $xlPasteValues)

If it doesn't work, please add some error checking (@error and @extended are set in case of a problem).

ummm...I'd love to do @error....etc.  

But I have no clue how to use that.

I tried the code and it didn't work.  hmmmmm....

where can I go to get my head around this @error @extended stuff?

 

Share this post


Link to post
Share on other sites

When you check the help file you will see how a function returns an error. Either by setting the returnvalue or the macros @error and/or @extended.
Check any of the _Excel* examples in the help file to see how this macros are used.


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

umm...well...I made an attempt to use the errors from the example.

The code now gleefully reports to me that copying to the clipboard and pasting to my desired column was a success

HOWEVER......what I want to happen does not happen.  

Copying and pasting Im' guessing should be a simple task to achieve.  There must be a simple syntax error somewhere.

Any help is MUCH appreciated!

            Local $oExcel_2 =_Excel_Open()
            Local $sWorkbook2 = "C:\Users\User\Desktop\ToGraph.xlsx"
            Local $oWorkbook2 = _Excel_BookOpen($oExcel_2,$sWorkbook2)
            Sleep(400)

            Local $oExcel_1 = _Excel_Open()
            Local $sWorkbook = ($aArray[1][3])
            Local $oWorkbook = _Excel_BookOpen($oExcel_1,$sWorkbook)
            sleep(300)

            _Excel_RangeCopyPaste($sWorkbook,"B:B")
            Sleep(400)
            If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 3", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 3", "Column B Successfuly Copied to the Clipboard.")


            _Excel_RangeCopyPaste($sWorkbook2,"A:A", $xlPasteValues)
            Sleep(400)
            If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "THe Column was successfully pasted.")

 

Share this post


Link to post
Share on other sites

Please remove the Sleep statements from your script. They are not needed. @error always reports the value of the last called function. In your case it is the value for the Sleep statement - which always runs successfully.


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

BTW: Why do you create two instances of Excel? Call _Excel_Open once and use this instance for all following _Excel_* functions.


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
13 minutes ago, water said:

BTW: Why do you create two instances of Excel? Call _Excel_Open once and use this instance for all following _Excel_* functions.

I suppose adding useless code comes with the territory of lacking experience o:)

I've changed the code to this:

            Local $oExcel_1 = _Excel_Open()
            Local $sWorkbook2 = "C:\Users\User\Desktop\ToGraph.xlsx"
            Local $oWorkbook2 = _Excel_BookOpen($oExcel_1,$sWorkbook2)
            Sleep(400)

            Local $sWorkbook = ($aArray[1][3])
            Local $oWorkbook = _Excel_BookOpen($oExcel_1,$sWorkbook)
            sleep(300)

            _Excel_RangeCopyPaste($sWorkbook,"B:B")
            If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 3", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 3", "Column B Successfuly Copied to the Clipboard.")


            _Excel_RangeCopyPaste($sWorkbook2,"A:A", $xlPasteValues)
            If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "THe Column was successfully pasted.")

and yes the computer is telling me there's an error now.

It is letting me know that it is unable to copy...................

I've attached an image of the error message.

How can I make this message useful?? and how is it giving me further insight on how to correct the syntax error in my code?

 

ErrorCopying.png

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

so..when I refer to the help files...

it seems that my worksheet is not an object? since the error is 1

How is that so?

 

Help FIle.png

Edited by Hoth2001

Share this post


Link to post
Share on other sites

You specified the filepath not the worksheet object for the copy/paste operation. This works for me:

#include <Excel.au3>

Local $sWorkbookIn = "C:\temp\TestIn.xlsx"
Local $sWorkbookOut = "C:\temp\TestOut.xlsx"

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox(0, "", "Error when opening Excel. @error = " & @error)

Local $oWorkbookIn = _Excel_BookOpen($oExcel, $sWorkbookIn)
If @error Then Exit MsgBox(0, "", "Error when opening WorkbookIn. @error = " & @error)

Local $oWorkbookOut = _Excel_BookOpen($oExcel, $sWorkbookOut)
If @error Then Exit MsgBox(0, "", "Error when opening WorkbookOut. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn.ActiveSheet, "B:B")
If @error Then Exit MsgBox(0, "", "Error when copying WorkbookIn. @error = " & @error)

_Excel_RangeCopyPaste($oWorkbookIn.ActiveSheet, Default, $oWorkbookOut.ActiveSheet.Range("A1"), Default, $xlPasteValues)
If @error Then Exit MsgBox(0, "", "Error when pasting WorkbookOut. @error = " & @error)

 


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

Eureka!

THat worked!  Thanks for all the help!

Share this post


Link to post
Share on other sites

:) 


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

Is that function real? I don't know that. But it sounds really useful in excel 


“Reality continues to ruin my life.” Utkarsh Classes Jodhpur apk

Share this post


Link to post
Share on other sites

Which function are you talking about?


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

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