Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how can I Append values to an existing excel file.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I paste to append instead of replace existing data? | Excel Discussion (Misc queries) | |||
How do I create an excel macro to append to a cell with existing i | Excel Discussion (Misc queries) | |||
How to append existing series? | Charts and Charting in Excel | |||
Append Text to Cell Values Using Replace | Excel Discussion (Misc queries) | |||
Append the data given in diff sheets of an Excel File to one sheet | Excel Worksheet Functions |