How to add a new worsheet into an existing workbook using ActiveX?

[rainer66]

My problem is:

I'm using CVI 7.1 and Excel2000 and want to communicate with Excel using ActiveX.

I want want to add a new worksheet in an existing workbook by using the command "Excel_WorksheetsAdd or Excel_SheetsAdd". Since there is no help available I don't know how to use these commands. Any hints or examples????

Thanks,

Rainer.

[Ian.W]

Rainer, here are some lines of code that should get you started, but since I just snipped them out, you may need to clean some details get it working:

#define ERR_BUF_SIZE 1000
#include "ExcelReport.h"
#include "excel2000.h"
void Exc_ErrRep(int err);
char cell[13];
char col='A';
int row=1;
CAObjHandle Excelxp_handle = 0;
CAObjHandle ws_handle = 0;
CAObjHandle wb_handle = 0;
       
// Open Excel & create wb and ws
Exc_ErrRep (  ExcelRpt_ApplicationNew (VTRUE, &Excelxp_handle)  ) Exc_ErrRep (  ExcelRpt_WorkbookNew (Excelxp_handle, &wb_handle) );
Exc_ErrRep (  ExcelRpt_WorksheetNew (wb_handle, 1, &ws_handle)  );
Exc_ErrRep (  ExcelRpt_SetWorksheetAttribute (ws_handle, ER_WS_ATTR_NAME, "MyNewSheet")  ) ;
//Put in some data in cell A1
Fmt(cell, "%s<%c%d", col, row);
Exc_ErrRep (  ExcelRpt_SetCellValue (ws_handle, cell , ExRConst_dataDouble, (double)123.456 ) ) ;
col++;
row++;
Fmt(cell, "%s<%c%d", col, row);
Exc_ErrRep (  ExcelRpt_SetCellValue (ws_handle, cell , ExRConst_dataDouble, (double)789.012 ) ) ;

//Clean Up and Quit (important!)
Exc_ErrRep (CA_DiscardObjHandle (ws_handle) );
Exc_ErrRep (CA_DiscardObjHandle (wb_handle) );
Exc_ErrRep (CA_DiscardObjHandle (Excelxp_handle) );

void Exc_ErrRep(int err)
{
char errstring[ERR_BUF_SIZE];
 if (err < 0) {
  CA_GetAutomationErrorString (err, errstring, ERR_BUF_SIZE);
  MessagePopup("ActiveX Error with Excel", errstring);
 }
return;
}

Hope this help.

--Ian

[rainer66]

Ian,

thanks for your answer. Meanwhile I could manage to add a workbook.

But, I'm really fighting with putting this new sheet to the end of the existing sheets. Using the "Excel_SheetsMove / Excel_WorksheetMove" I can't put it to the end. If you have any idea I would appreciate that :-))

Rainer.

[ashwincm]

Hi,

I agree with you that it is quite difficult to link excel and CVI. I had the same problem here.

You can use

error = Excel_GetProperty (ExcelAppHandle, NULL, Excel_AppSheets,
                    CAVT_OBJHANDLE, &ExcelSheetsHandle);
                if (error<0)
                    goto Error;

error = Excel_SheetsAdd (ExcelSheetsHandle, NULL, CA_DEFAULT_VAL,
             CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,
             &ExcelWorksheetHandle);

  if (error<0)
                    goto Error;

Hope this will solve your problem.

Bye

Ashwin

[rainer66]

@ashwincm:

It works, thanks.

But again, do you know a way to put a new sheet to the end of the active one by using the Move commands (simply appending)???

Rainer.

[rainer66]

I am using the CVI and trying to comunicate with Excel using Activex.

My problem is :

When a new excel file is generated i could easly save it but if an old file is opened and modified, the modified version could not overwright the old version.

If  either"Excel_workbookSaveAs" or " Excel_WorkbookSave"  is used to save the modified version an error code of -2147467262 is generated and the file would not be saved

thanks,

Rainer.