_ExcelSheetMove - what am I doing wrong?

[deef99]

Help! I am in a time bind and have 1 issue I can't seem to figure out. I am building a workbook that each tab is the day of the month [1,2,3....30, etc.] That works fine... except it creates the new sheet at the beginning of the workbook and I need it to be moved to the end so the tabs are in calerdar order. There is a "Sheet1" tab that exists at the end of the workbook and I am using that for relative position.

But the new day sheet does not move - what is wrong with my syntax?

;-------------------------------PROCESS FILE

#include <Excel.au3>

#include <Array.au3>

#include <Date.au3>

$day = @Mon&@mday

$do = @mday - 1

global $oExcel = _ExcelBookOpen("H:\Callcenter\Reports\Client External Reports\GR_Monthly_Metrics.xls")

$oExcel.Visible = True

;add a new sheet named with the Day of the Month

_ExcelSheetAddNew($oExcel, $do)

;grab the columns of data

With $oEHD.ActiveSheet.Range("A:H")

.copy

EndWith

;Paste the data into Monthly xls

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

.Insert

EndWith

;adjust the column widths

WinActivate("Microsoft Excel - GR_Metrics.xls")

$oExcel.ActiveSheet.Range("A3").select

$oExcel.ActiveSheet.Paste

$oExcel.Columns("A").ColumnWidth = 14

$oExcel.Columns("B").ColumnWidth = 14

$oExcel.Columns("C").ColumnWidth = 8

$oExcel.Columns("D").ColumnWidth = 10

$oExcel.Columns("E").ColumnWidth = 11

$oExcel.Columns("F").ColumnWidth = 7

$oExcel.Columns("G").ColumnWidth = 9

;move the new sheet to the end of the workbook BEFORE Sheet1

_ExcelSheetMove($oExcel,$do,"Sheet1",True)

;get the Daily GR xls

GLobal $oEHD = _ExcelBookOpen("H:\Callcenter\Reports\Client External Reports\gr_daily_metrics.xls")

....save and close

[l3ill]

Well, a quick test tells me your syntax works fine.

_ExcelSheetMove($oExcel,$do,"Sheet1",True)

What I'm not sure of is the string or integer issue (looks like this may be your problem):

$vRelativeSheet [optional] The moved sheet will be placed before or after this sheet (a string or integer, defaults to first sheet)

because I tested with "test" and it works fine but with the variable I get this error:

Only Object-type variables allowed in a "With" statement.: With $oEHD.ActiveSheet.Range("A:H") With ^ ERROR

I would suggest trading in your {With, copy and paste} statements for a nice sturdy array.

Hope this helps a little... Good Luck!

Edited April 28, 2010 by billo