Move sheets between workbooks in Excel

[NvdM]

Hi,

I'm trying to move a sheet from one workbook in Excel to another, but I can't get it working.

I can't even get a sheet moved within it's own workbook.

I had a look at the "ExcelCOM_UDF.au3", but I don't quite know how to use it.

Therefore I copied the code of the ""_ExcelSheetMove"-function to my own script and replaced the parts where the "_ExcelSheetList"-funcion is called.

So I could try to figure out how it works.

I have tried to give parameters when calling the function, like:

call("_ExcelSheetMove",$oExcel, 1, 1, 0)
oÝ÷ ØZ½æ¥²kçpØZaz»!yëZ°-÷ç­nëg£²!Ƨëmë®*mv§Z´(ºWeG¢¶Ú¡*"*.¦W±ì(ºWmjG$jØ­jw`÷¦y¨bÚ'-¢èqçN¬ËayÊzÚ&¢÷²·­)â·l£   𢹢«Þ²Ú­«­¢+Ø((ì ½(ÀÌØí½á°ô=©
ÉÑ ÅÕ½Ðíá°¹ÁÁ±¥Ñ¥½¸ÅÕ½Ðì¤(ÀÌØí½á°¹ÕѽµÑ¥½¹MÕÉ¥ÑäôÄ$$$$ìMÑÌÑ¡5ɼMÕÉ¥ÑäèÄô¹±µÉ½Ì°Èô¥±½½à°Ìô¥Í±µÉ½Ì(ÀÌØí½á°¹]½É­  ½½­Ì¹=Á¸¡MÉ¥ÁѥȵÀìÅÕ½ÐìÀäÈí  Í¥ÌM¡Ð¹á±ÌÅÕ½Ðì¤ìQ¡¥Ìݽɭ½½¬½¹Ñ¥¹ÌÌÍ¡Ñ̸(ÀÌØí½á°¹Y¥Í¥±ôÄ$(ÀÌØí½á°¹]½É­Í¡ÑÌ Ä¤¹Ñ¥ÙÑ)±½°ÀÌØíM¡Ñ1¥ÍÐôÀÌØí½á°¹Ñ¥Ù]½É­½½¬¹M¡Ñ̹
½Õ¹Ð)5Í    ½à À°ÅÕ½Ðí9յȽ͡ÑÌÅÕ½Ðì°ÀÌØíM¡Ñ1¥Íа̤$ì¥ÙÌÌ̬°ÕÍѡݽɭ½½¬½¹Ñ¥¹ÌÌÍ¡Ñ̸)M±À ̤)±° ÅÕ½Ðí}á±M¡Ñ5½ÙÅÕ½Ðì¤()Õ¹}á±M¡Ñ5½Ù ÀÌØí½á°°ÀÌØíÙ5½ÙM¡Ð°ÀÌØíÙI±Ñ¥ÙM¡ÐôÄ°ÀÌØí   ½ÉôQÉÕ¤(%1½°ÀÌØíM¡Ñ1¥ÍаÀÌØí¥½Õ¹5½ÙôÀ°ÀÌØí¥½Õ¹   ½ÉôÀ(%%9=P%Í=¨ ÀÌØí½á°¤Q¡¸IÑÕɸMÑÉÉ½È Ä°À°À¤(%%%Í9ÕµÈ ÀÌØíÙ5½ÙM¡Ð¤Q¡¸($%%ÀÌØí½á°¹Ñ¥Ù]½É­½½¬¹M¡Ñ̹
½Õ¹Ð±ÐìÀÌØíÙ5½ÙM¡ÐQ¡¸IÑÕɸMÑÉÉ½È È°À°À¤(%±Í($%½ÈÀÌØíáàôÄQ¼ÀÌØíM¡Ñ1¥ÍÑlÁt($$%%ÀÌØíM¡Ñ1¥ÍÑlÀÌØíáátôÀÌØíÙ5½ÙM¡ÐQ¡¸ÀÌØí¥½Õ¹5½ÙôÀÌØíáà($%9áÐ($%%9=PÀÌØí¥½Õ¹5½ÙQ¡¸IÑÕɸMÑÉÉ½È Ì°À°À¤(%¹%(%%%Í9ÕµÈ ÀÌØíÙI±Ñ¥ÙM¡Ð¤Q¡¸($%%ÀÌØí½á°¹Ñ¥Ù]½É­½½¬¹M¡Ñ̹
½Õ¹Ð±ÐìÀÌØíÙI±Ñ¥ÙM¡ÐQ¡¸IÑÕɸMÑÉÉ½È Ð°À°À¤(%±Í($%½ÈÀÌØíáàôÄQ¼ÀÌØíM¡Ñ1¥ÍÑlÁt($$%%ÀÌØíM¡Ñ1¥ÍÑlÀÌØíáátôÀÌØíÙI±Ñ¥ÙM¡ÐQ¡¸ÀÌØí¥½Õ¹  ½ÉôÀÌØíáà($%9áÐ($%%9=PÀÌØí¥½Õ¹   ½ÉQ¡¸IÑÕɸMÑÉÉ½È Ô°À°À¤(%¹%(%%ÀÌØí  ½ÉQ¡¸($$ÀÌØí½á°¹M¡ÑÌ ÀÌØíÙ5½ÙM¡Ð¤¹5½Ù ÀÌØí½á°¹M¡ÑÌ ÀÌØíÙI±Ñ¥ÙM¡Ð¤¤(%±Í($$ÀÌØí½á°¹M¡ÑÌ ÀÌØíÙ5½ÙM¡Ð¤¹5½Ù¡Õ±Ð°ÀÌØí½á°¹M¡ÑÌ ÀÌØíÙI±Ñ¥ÙM¡Ð¤¤(%¹%$(%IÑÕɸÄ)¹Õ¹(ì½(

Thanks in advance!!!

Cheers,

Nico.

[NvdM]

Hi,

I found some way to get one of the sheets to be moved within it's own workbook.

The function call and parameters used:

call("_ExcelSheetMove",$oExcel,3,1,1)

This will move the 3rd sheet infront of the original first sheet. Start situation: S1;S2;S3 => End situation: S3;S1;S2.

But still a long way to go before I get there...

Cheers,

Nico.

[NvdM]

Hi,

Got it!!! Finally!!!

It took me more than 15 hours to figure it out (Didn't post it immediately). Now I am able to have a sheet moved from one workbook to another workbook.

Here's how I've done it:

; ### BoC ###
$oExcel = ObjCreate("Excel.Application")
$oExcel.AutomationSecurity = 1  ; Sets the Macro Security: 1 = Enable macros, 2 = Dialog box, 3 = Disable macros
$oWorkbook=$oExcel.WorkBooks.Open(@ScriptDir & "\Basis Sheet.xls") ; This workbook contains only one sheet.
$oExcel.Visible = 1 
Sleep(3000)
$oWorkBook2=$oExcel.WorkBooks.Open(@ScriptDir & "\GL1.csv") ; This workbook contains only one sheet.
$oExcel.workbooks("Basis Sheet.xls").activate
Sleep(3000)
$oWorkBook2.Sheets(1).Move($oWorkbook.Sheets(1))
Sleep(3000)
$oWorkBook.Sheets(2).Move($oWorkbook.Sheets(1)) ; This line moves the original sheet of the workbook back to the first position.

; ### EoC ###

So afterall this post wouldn't have been necassary, but maybe someone out there might learn from it...

Cheers,

Nico.

Edited February 26, 2007 by NvdM

[Locodarwin]

That's not a bad idea for a function to be included with my collection. I don't have a routine to move sheets between workbooks, but now that you mention it I can see how it would be useful. I'll write a function up and give you due credit. Good job on your troubleshooting and knowledge extrapolation!

-S

[NvdM]

That's not a bad idea for a function to be included with my collection. I don't have a routine to move sheets between workbooks, but now that you mention it I can see how it would be useful. I'll write a function up and give you due credit. Good job on your troubleshooting and knowledge extrapolation!

-S

@Locodarwin

;-)

Thanks!!!

Nico.