Excel Paste to a Different Workbook

[deef99]

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

[PsaltyDS]

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)

Edited September 4, 2009 by PsaltyDS

[Juvigy]

Change:

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

TO:

$oExcel.ActiveSheet.Range("A9").select
$oExcel.ActiveSheet.Paste

Edited September 4, 2009 by Juvigy

[deef99]

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

[PsaltyDS]

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.

[deef99]

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