How to Moving Excel Sheet by wxAutomationObject

This forum can be used to talk about general design strategies, new ideas and questions in general related to wxWidgets. If you feel your questions doesn't fit anywhere, put it here.
Post Reply
frog
Earned a small fee
Earned a small fee
Posts: 16
Joined: Mon Aug 22, 2011 11:49 am

How to Moving Excel Sheet by wxAutomationObject

Post by frog » Mon Jan 28, 2013 9:48 am

Hi,
I suceed to read/write excel by wxAutomationObject. But can not Sheets position. Like to change Sheet1,Sheet2,Sheet3 to Sheet3,Sheet2,Sheet1
My code as following
//Open Excel and file
wxAutomationObject exl, workBooks, workbook, workSheets, sheet;
bool ret = exl.CreateInstance(wxT("Excel.Application")); // ex1:Excel, excel
ret = exl.GetObject(workBooks, wxT("Workbooks"));
exl.PutProperty("visible", true);
workBooks.CallMethod("open", edtFile->GetValue());
exl.GetObject(workbook, wxT("ActiveWorkbook"));
ret = workbook.GetObject(workSheets, wxT("Worksheets"));
ret = workSheets.GetObject(sheet , wxT("Item"), wxVariant(3)); //--> Get sheet3 to Sheet
sheet.PutProperty("move", wxVariant("Before:=Sheet(1)")); //------>To Move Sheet, Sheet3 move to before Sheet1, But to result is : Create a new WorkBook, then move Sheet3 to new WorkBook.//
I find a description at msdn - If you do not specify either Before or After, Microsoft Office Excel creates a new workbook that contains the moved sheet. So, it does not accept the "Before:=Sheet(1)" instruction.

How can I do To move the sheet?

Thanks

PB
Part Of The Furniture
Part Of The Furniture
Posts: 2465
Joined: Sun Jan 03, 2010 5:45 pm

Re: How to Moving Excel Sheet by wxAutomationObject

Post by PB » Mon Jan 28, 2013 12:36 pm

[My 2 cents]

I believe the issue is at least with the following line
frog wrote:

Code: Select all

sheet.PutProperty("move", wxVariant("Before:=Sheet(1)")); 
1. Based on the pasted code, it appears that sheet is not a valid automation object.
2. Move is a method, not a property.
3. I doubt the wxVariant containing string "Before:=Sheet(1)" translates to a valid sheet object, which Move method expects as a parameter. You have to pass a wxVariant named "Before", containing valid IDispatch of the sheet you want to move it in front of.

frog
Earned a small fee
Earned a small fee
Posts: 16
Joined: Mon Aug 22, 2011 11:49 am

Re: How to Moving Excel Sheet by wxAutomationObject

Post by frog » Mon Jan 28, 2013 2:24 pm

Hi,
Thanks, PB.
Original, I think "Move" is a method. I try sheet.CallMethod("move", wxVariant("after:=Sheets(1)"));, but have no any response.
try sheet.CallMethod("move", wxVariant("after"), wxVariant("Sheets(1)")); --> no response
sheet.CallMethod("move", wxVariant("after"), wxVariant(1)); --> no response
sheet.PutProperty("move", wxVariant("after"), wxVariant(1)); --> no response
sheet.PutProperty("move", wxVariant("after"), wxVariant("Sheets(1)")); --> no response
sheet.PutProperty("move", true); --> move to new WorkBook
sheet.PutProperty("move", XXX); --> "move" method and one parameter --> get same result : move to new WorkBook.
sheet.PutProperty("move", XXX, XXX); --> "move" method and two parameter --> get same result : no response.
I try any thing - Like the structrue. And can not get the good result.
Could you tell me another way, directly.

Thanks

PB
Part Of The Furniture
Part Of The Furniture
Posts: 2465
Joined: Sun Jan 03, 2010 5:45 pm

Re: How to Moving Excel Sheet by wxAutomationObject

Post by PB » Mon Jan 28, 2013 3:41 pm

As I wrote before, I believe you should call sheet->CallMethod("Move", variant) (assuming sheet is a valid wxAutomation object containing IDispatch of a worksheet), where variant should be a wxVariant named "Before", with type of void*, containing IDispatch of the sheet you wish to move sheet before. Alternatively, instead of IDispatch perhaps it is possible to use a long (sheet index) or a string (sheet name), I haven't tried that - maybe you should start trying that, as you can run into few unpleasant issues using IDispatches in wxVariants in wxAutomation.

BTW, if you're feeling adventurous, need to write a lot of wxAutomation code for MS Excel and not get crazy and are using wxWidgets 2.9.5 and MSVC 2008, you can give wxAutoExcel a try. ;)

frog
Earned a small fee
Earned a small fee
Posts: 16
Joined: Mon Aug 22, 2011 11:49 am

Re: How to Moving Excel Sheet by wxAutomationObject

Post by frog » Wed Jan 30, 2013 1:45 pm

Hi
Thanks anybody. I get the answer by other forum.
As following

Code: Select all

   wxAutomationObject exl, workBooks, workbook, workSheets, sheet,sheet2;
   bool ret = exl.CreateInstance(wxT("Excel.Application")); 
   ret = exl.GetObject(workBooks, wxT("Workbooks")); 
  exl.PutProperty("visible", true);
   workBooks.CallMethod("open", wxT("edtFile->GetValue()"));
   exl.GetObject(workbook, wxT("ActiveWorkbook"));
   ret = workbook.GetObject(workSheets, wxT("Worksheets"));
   ret = workSheets.GetObject(sheet , wxT("Item"),1,&wxVariant(3));
   ret = workSheets.GetObject(sheet2,wxT("Item"),1,&wxVariant(1));
   sheet.CallMethod(wxT("Move"),wxVariant(sheet2.GetDispatchPtr()));
Thanks

PB
Part Of The Furniture
Part Of The Furniture
Posts: 2465
Joined: Sun Jan 03, 2010 5:45 pm

Re: How to Moving Excel Sheet by wxAutomationObject

Post by PB » Thu Jan 31, 2013 7:27 am

That's exactly the same answer I already gave you. Twice.

Post Reply