How to Append data in excel sheet

[viv]

Hi,

I'm working on the excel automation 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

 

[merjet]

You should post your question to a C (?) newsgroup. VBA is the usual
language here. Book1.xls is the default workbook name when Excel
opens. Maybe you aren't writing to the correct file because "C:\
\vivek.xls" has 2 slashes.

Merjet

 

[viv]

Ya realised it the moment i had posted my question. Anyways i'll post my
issue in C/C++ section.

as for the path, that's the way we pass the path of a file in c++, as the
first slash is treated as the escape sequence.

Thanks

viv