Jump to content
Hoth2001

Select Column in Excel

Recommended Posts

Hoth2001

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
water

_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
Hoth2001
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
water

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
Hoth2001
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
water

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
Hoth2001

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
water

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
water

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
Hoth2001
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
Hoth2001

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
water

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
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
water

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

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.