Jump to content

Select Column in Excel


Hoth2001
 Share

Recommended Posts

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

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

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)

 

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

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?

 

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

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.")

 

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

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

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

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

:) 

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

Which function are you talking about?

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

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