How to Moving Excel Sheet by wxAutomationObject

[frog]

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]

[My 2 cents]

I believe the issue is at least with the following line

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]

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]

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]

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]

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