Sign in to follow this  
Followers 0
NvdM

Move sheets between workbooks in Excel

5 posts in this topic

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.

Share this post


Link to post
Share on other sites



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.

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by NvdM

Share this post


Link to post
Share on other sites

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


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

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.

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