Sign in to follow this  
Followers 0
deef99

Excel Paste to a Different Workbook

6 posts in this topic

I've looked and tried various things listed here in the forum, but I am still pulling my hair out!!! Hoping someone can see the error of my ways...

I am simply opening one workbook, selecting a range, and then copying it to another workbook. My code below gets me back to the workbook I want to "paste" to, but it does not paste! But I can click on the Paste button in Excel and it will paste...Urgh!

Additional question please: How can I tell Auto It what range to select if I do not know how many cells are populated?

ANY HELP WOULD BE GREATLY APPRECIATED...DEADLINE IS FAST APPROACHING!!!!

Thanks in advance!!!!

#include <Excel.au3>

#include <Array.au3>

$day = InputBox("Day of the Month", "Enter Day to Process:","", "")

Local $oExcel = _ExcelBookOpen("H:\HD Daily XLS\HD_Master.xlsx")

$oExcel.Visible = True

_ExcelSheetActivate($oExcel, "Header")

With $oExcel.ActiveSheet.Range("A1:H8")

.Copy

EndWith

_ExcelSheetAddNew($oExcel, $day)

With $oExcel.ActiveSheet.Range("A1:A1")

.Insert

.Columns("A").ColumnWidth = 24

EndWith

;_ExcelBookSave($oExcel)

GLobal $oEHD = _ExcelBookOpen("T:\HD\HD_Newsletter0723.xls")

With $oEHD.ActiveSheet.range("A8:S200")

.copy

EndWith

;_ExcelBookClose($oEHD)

---- somewhere down here I think is my issue? ----

WinActivate("Microsoft Excel - HD_Master.xlsx")

With $oExcel.ActiveSheet.Range("A9")

.Insert

EndWith

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

I've looked and tried various things listed here in the forum, but I am still pulling my hair out!!! Hoping someone can see the error of my ways...

I am simply opening one workbook, selecting a range, and then copying it to another workbook. My code below gets me back to the workbook I want to "paste" to, but it does not paste! But I can click on the Paste button in Excel and it will paste...Urgh!

Additional question please: How can I tell Auto It what range to select if I do not know how many cells are populated?

ANY HELP WOULD BE GREATLY APPRECIATED...DEADLINE IS FAST APPROACHING!!!!

Thanks in advance!!!!

If you're going to use the Excel.au3 UDF, then let it do the work for you. Most of what you are coding in your own object references is already in the UDF functions:
#include <Excel.au3>

Global $day, $oExcel, $avReadData

$day = InputBox("Day of the Month", "Enter Day to Process:","", "")
$oExcel = _ExcelBookOpen("H:\HD Daily XLS\HD_Master.xlsx")
_ExcelSheetActivate($oExcel, "Header")
$avReadData = _ExcelReadSheetToArray($oExcel)
_ExcelSheetAddNew($oExcel, $day)
_ExcelSheetActivate($oExcel, $day)
_ExcelWriteSheetFromArray($oExcel, $avReadData)

:D

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Change:

With $oExcel.ActiveSheet.Range("A9")
.Insert
EndWith

TO:

$oExcel.ActiveSheet.Range("A9").select
$oExcel.ActiveSheet.Paste
Edited by Juvigy

Share this post


Link to post
Share on other sites

OMG!!!! Duh! Thank you so very much for your assistance! You're life savers!

So does "ReadSheetToArray" answer this question then?

How can I tell Auto It what range to select if I do not know how many cells are populated?

Dee

Share this post


Link to post
Share on other sites

OMG!!!! Duh! Thank you so very much for your assistance! You're life savers!

So does "ReadSheetToArray" answer this question then?

How can I tell Auto It what range to select if I do not know how many cells are populated?

Dee

There was a function in the old ExcelCOM_UDF called _ExcelSheetUsedRangeGet(). I pulled the technique out of that and incorporated it into _ExcelReadSheetToArray(). By default it reads the entire used range and no more. The elements [0][0] and [0][1] of the returned array give the number of rows and columns used.

When the old UDF was cleaned up and added to the AutoIt distro as Excel.au3, the _ExcelSheetUsedRangeGet() was dropped off (I don't know why). You can still crack open the Excel.au3 file and look at the code for _ExcelReadSheetToArray() and see how it was done:

; Get size of current sheet as R1C1 string
    ;     Note: $xlCellTypeLastCell and $x1R1C1 are constants declared in ExcelCOM_UDF.au3
    Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)

You can code that directly, or just trust _ExcelReadSheetToArray() to get it for you.

:D


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Interesting insight...thank you so much. This will work better than selecting an oversized range for nothing. We shall trust the code!

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