hi,

I'm working on the excel automation using VC++ 6.0 in which i need to write
a script
that'll aceept three command line arguments and then place them under the
coloumns say NAME STATUS and TIME. The script will run multiple no. of times
in a day and everytime it will open the same excel and then put the data in
the respective columns without deleting the previous content.Also, depending
on the STATUS passed as the argument it should be able to change the color of
the row. eg:- if status is passed as CRITICAL then the particular row should
become RED.The process should run at background.

I'm able to open the file and write data for the first time but when excute
the script for the second time it gives me message " cannot access filename"
i.e. it does not allow me to append the values.

Secondly, how can i make it run at the background?

One more thing that I noticed and I was not able to understand is that when
I try to open my *.xls file it opens it but it writes in some book1.xls . Is
it a temporary file that the system opens or something else is happening?
here is my code:-

void CExcelFormatDlg::OnOK()
{
_Application app;
Workbooks books;
_Workbook book;

Worksheets sheets;
_Worksheet sheet;
Range range;
Font font;
Range cols;
LPDISPATCH lpDisp; // Often reused variable.


// Commonly used OLE variants.

COleVariant
covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);


// Start Excel and get Application object.

if(!app.CreateDispatch("Excel.Application"))
{
AfxMessageBox("Couldn't start Excel and get Application object.");
return;
}


lpDisp = app.GetWorkbooks(); // Get an IDispatch pointer.
ASSERT(lpDisp);
books.AttachDispatch(lpDisp); // Attach the IDispatch pointer
// to the books object.

// Open a new workbook and attach that IDispatch pointer to the
// Workbook object


lpDisp = books.Add( covOptional );
ASSERT(lpDisp);
book.AttachDispatch( lpDisp );

lpDisp = books.Open("C:\\vivek.xls",
covOptional, covFalse, covTrue, covOptional,
covOptional, covTrue, covOptional, covOptional,
covTrue, covOptional, covOptional, covOptional,
covOptional, covOptional);
// Get the Sheets collection and attach the IDispatch pointer to your
// sheets object.


lpDisp = book.GetSheets();
ASSERT(lpDisp);
sheets.AttachDispatch(lpDisp);


// Get sheet #1 and attach the IDispatch pointer to your sheet
// object.
lpDisp = sheets.GetItem( COleVariant((short)(1)) );
//GetItem(const VARIANT &index)
ASSERT(lpDisp);
sheet.AttachDispatch(lpDisp);



// Fill range A1 with "1/25/98", the settlement date.
lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.SetValue2(COleVariant("change")); // Excel 2002 and Excel 2003

// checking for the particular cell if it's empty

VARIANT value1;
char str[]="A";
char strf[]="";
int i = 1;
char str1[10];
_itoa(i,str1,10);
strcpy(strf,str);
strcat(strf,str1);
range = sheet.GetRange(COleVariant(strf),COleVariant(strf));
value1 = range.GetValue2();


while(!value1.vt == VT_EMPTY)
{
i++;
_itoa(i,str1,10);
strcpy(strf,str);
strcat(strf,str1);
range = sheet.GetRange(COleVariant(strf),COleVariant(strf));
value1 = range.GetValue2();

}

range.SetValue2(COleVariant("Country"));
book.Save();
{
COleSafeArray saRet;

saRet.Detach();
}
app.SetVisible(TRUE);
app.SetUserControl(TRUE);

Please help me out here as I don't have much time in my hand.

Regards,
vivek

I don't use C++, but I seem to remember our post that mention more rubbish
collection is required to properly close the file and set the reference
count to 0.
Also, have you looked at using ADO to do the inserts and Excel's conditional
formatting for the cell colours.

NickHK

"viv" <(E-Mail Removed)> wrote in message
news84C09ED-90BB-437A-8B07-(E-Mail Removed)...
> hi,
>
> I'm working on the excel automation using VC++ 6.0 in which i need to
write
> a script
> that'll aceept three command line arguments and then place them under the
> coloumns say NAME STATUS and TIME. The script will run multiple no. of
times
> in a day and everytime it will open the same excel and then put the data
in
> the respective columns without deleting the previous content.Also,
depending
> on the STATUS passed as the argument it should be able to change the color
of
> the row. eg:- if status is passed as CRITICAL then the particular row
should
> become RED.The process should run at background.
>
> I'm able to open the file and write data for the first time but when
excute
> the script for the second time it gives me message " cannot access
filename"
> i.e. it does not allow me to append the values.
>
> Secondly, how can i make it run at the background?
>
> One more thing that I noticed and I was not able to understand is that
when
> I try to open my *.xls file it opens it but it writes in some book1.xls .
Is
> it a temporary file that the system opens or something else is happening?
> here is my code:-
>
> void CExcelFormatDlg::OnOK()
> {
> _Application app;
> Workbooks books;
> _Workbook book;
>
> Worksheets sheets;
> _Worksheet sheet;
> Range range;
> Font font;
> Range cols;
> LPDISPATCH lpDisp; // Often reused variable.
>
>
> // Commonly used OLE variants.
>
> COleVariant
> covTrue((short)TRUE),
> covFalse((short)FALSE),
> covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
>
>
> // Start Excel and get Application object.
>
> if(!app.CreateDispatch("Excel.Application"))
> {
> AfxMessageBox("Couldn't start Excel and get Application object.");
> return;
> }
>
>
> lpDisp = app.GetWorkbooks(); // Get an IDispatch pointer.
> ASSERT(lpDisp);
> books.AttachDispatch(lpDisp); // Attach the IDispatch pointer
> // to the books object.
>
> // Open a new workbook and attach that IDispatch pointer to the
> // Workbook object
>
>
> lpDisp = books.Add( covOptional );
> ASSERT(lpDisp);
> book.AttachDispatch( lpDisp );
>
> lpDisp = books.Open("C:\\vivek.xls",
> covOptional, covFalse, covTrue, covOptional,
> covOptional, covTrue, covOptional, covOptional,
> covTrue, covOptional, covOptional, covOptional,
> covOptional, covOptional);
> // Get the Sheets collection and attach the IDispatch pointer to your
> // sheets object.
>
>
> lpDisp = book.GetSheets();
> ASSERT(lpDisp);
> sheets.AttachDispatch(lpDisp);
>
>
> // Get sheet #1 and attach the IDispatch pointer to your sheet
> // object.
> lpDisp = sheets.GetItem( COleVariant((short)(1)) );
> //GetItem(const VARIANT &index)
> ASSERT(lpDisp);
> sheet.AttachDispatch(lpDisp);
>
>
>
> // Fill range A1 with "1/25/98", the settlement date.
> lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
> ASSERT(lpDisp);
> range.AttachDispatch(lpDisp);
> range.SetValue2(COleVariant("change")); // Excel 2002 and Excel 2003
>
> // checking for the particular cell if it's empty
>
> VARIANT value1;
> char str[]="A";
> char strf[]="";
> int i = 1;
> char str1[10];
> _itoa(i,str1,10);
> strcpy(strf,str);
> strcat(strf,str1);
> range = sheet.GetRange(COleVariant(strf),COleVariant(strf));
> value1 = range.GetValue2();
>
>
> while(!value1.vt == VT_EMPTY)
> {
> i++;
> _itoa(i,str1,10);
> strcpy(strf,str);
> strcat(strf,str1);
> range = sheet.GetRange(COleVariant(strf),COleVariant(strf));
> value1 = range.GetValue2();
>
> }
>
> range.SetValue2(COleVariant("Country"));
> book.Save();
> {
> COleSafeArray saRet;
>
> saRet.Detach();
> }
> app.SetVisible(TRUE);
> app.SetUserControl(TRUE);
>
> Please help me out here as I don't have much time in my hand.
>
> Regards,
> vivek

Hi Nick,

I haven't seen how to use ADO to do the inserts because i didn't know if it
can be done using ADO. if you know any link where i can get sufficient amount
of help on that , then it'll be really helpful.

Thanks & Regards,
Vivek

"NickHK" wrote:

> I don't use C++, but I seem to remember our post that mention more rubbish
> collection is required to properly close the file and set the reference
> count to 0.
> Also, have you looked at using ADO to do the inserts and Excel's conditional
> formatting for the cell colours.
>
> NickHK
>
> "viv" <(E-Mail Removed)> wrote in message
> news84C09ED-90BB-437A-8B07-(E-Mail Removed)...
> > hi,
> >
> > I'm working on the excel automation using VC++ 6.0 in which i need to
> write
> > a script
> > that'll aceept three command line arguments and then place them under the
> > coloumns say NAME STATUS and TIME. The script will run multiple no. of
> times
> > in a day and everytime it will open the same excel and then put the data
> in
> > the respective columns without deleting the previous content.Also,
> depending
> > on the STATUS passed as the argument it should be able to change the color
> of
> > the row. eg:- if status is passed as CRITICAL then the particular row
> should
> > become RED.The process should run at background.
> >
> > I'm able to open the file and write data for the first time but when
> excute
> > the script for the second time it gives me message " cannot access
> filename"
> > i.e. it does not allow me to append the values.
> >
> > Secondly, how can i make it run at the background?
> >
> > One more thing that I noticed and I was not able to understand is that
> when
> > I try to open my *.xls file it opens it but it writes in some book1.xls .
> Is
> > it a temporary file that the system opens or something else is happening?
> > here is my code:-
> >
> > void CExcelFormatDlg::OnOK()
> > {
> > _Application app;
> > Workbooks books;
> > _Workbook book;
> >
> > Worksheets sheets;
> > _Worksheet sheet;
> > Range range;
> > Font font;
> > Range cols;
> > LPDISPATCH lpDisp; // Often reused variable.
> >
> >
> > // Commonly used OLE variants.
> >
> > COleVariant
> > covTrue((short)TRUE),
> > covFalse((short)FALSE),
> > covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
> >
> >
> > // Start Excel and get Application object.
> >
> > if(!app.CreateDispatch("Excel.Application"))
> > {
> > AfxMessageBox("Couldn't start Excel and get Application object.");
> > return;
> > }
> >
> >
> > lpDisp = app.GetWorkbooks(); // Get an IDispatch pointer.
> > ASSERT(lpDisp);
> > books.AttachDispatch(lpDisp); // Attach the IDispatch pointer
> > // to the books object.
> >
> > // Open a new workbook and attach that IDispatch pointer to the
> > // Workbook object
> >
> >
> > lpDisp = books.Add( covOptional );
> > ASSERT(lpDisp);
> > book.AttachDispatch( lpDisp );
> >
> > lpDisp = books.Open("C:\\vivek.xls",
> > covOptional, covFalse, covTrue, covOptional,
> > covOptional, covTrue, covOptional, covOptional,
> > covTrue, covOptional, covOptional, covOptional,
> > covOptional, covOptional);
> > // Get the Sheets collection and attach the IDispatch pointer to your
> > // sheets object.
> >
> >
> > lpDisp = book.GetSheets();
> > ASSERT(lpDisp);
> > sheets.AttachDispatch(lpDisp);
> >
> >
> > // Get sheet #1 and attach the IDispatch pointer to your sheet
> > // object.
> > lpDisp = sheets.GetItem( COleVariant((short)(1)) );
> > //GetItem(const VARIANT &index)
> > ASSERT(lpDisp);
> > sheet.AttachDispatch(lpDisp);
> >
> >
> >
> > // Fill range A1 with "1/25/98", the settlement date.
> > lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
> > ASSERT(lpDisp);
> > range.AttachDispatch(lpDisp);
> > range.SetValue2(COleVariant("change")); // Excel 2002 and Excel 2003
> >
> > // checking for the particular cell if it's empty
> >
> > VARIANT value1;
> > char str[]="A";
> > char strf[]="";
> > int i = 1;
> > char str1[10];
> > _itoa(i,str1,10);
> > strcpy(strf,str);
> > strcat(strf,str1);
> > range = sheet.GetRange(COleVariant(strf),COleVariant(strf));
> > value1 = range.GetValue2();
> >
> >
> > while(!value1.vt == VT_EMPTY)
> > {
> > i++;
> > _itoa(i,str1,10);
> > strcpy(strf,str);
> > strcat(strf,str1);
> > range = sheet.GetRange(COleVariant(strf),COleVariant(strf));
> > value1 = range.GetValue2();
> >
> > }
> >
> > range.SetValue2(COleVariant("Country"));
> > book.Save();
> > {
> > COleSafeArray saRet;
> >
> > saRet.Detach();
> > }
> > app.SetVisible(TRUE);
> > app.SetUserControl(TRUE);
> >
> > Please help me out here as I don't have much time in my hand.
> >
> > Regards,
> > vivek
>
>
>