Select Column in Excel

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

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

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

 

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

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

 

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

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

 

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

[water]

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

[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 

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?

 

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

 

Edited September 12, 2017 by Hoth2001

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

 

[Hoth2001]

Eureka!

THat worked!  Thanks for all the help!

[water]

 

[Karn]

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

[water]

Which function are you talking about?