how to apeend data to the excel

V

viv

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
 
N

NickHK

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 said:
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
Click to expand...
 
V

viv

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 said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to Append data in excel sheet 2
how can I Append values to an existing excel file. 0
Embedded Excel Problem via c++ 0
Embedded Excel Problem with Menus/Commandbars/Toolbars etc 0
MS Excel: how to stop select and copy of sheet contents if sheet is protected 0
Inserting .dwg file in excel opens the drawing in autocad... 0
Always prompted to save even when not modified 0
I really want to understand why this does not work. 0

Top