Save and close multiple Excel applications?

S

sclarke18

Hi,

I am trying to automate saving and shutting down Excel applications. At the
moment, this is saving and closing workbooks in applications, as I can't see
a way to directly save applications. The problem I have is that when I get
the current Excel application using GetActive object, it seems to either hold
on to this permanently (until the program finishes) or not attach to the next
open Excel application once I save and close down the first. I tried using
times, as it seems to work if I step slowly through it, but not if it runs
itself, but timers don’t seem to help. Any ideas on this?

My code is as below, so any help to enable saving and closing each open
application would be appreciated. Also this doesn't work if one of the cells
in any of the workbooks in the application is being worked on i.e. if someone
starts entering numbers, letters, etc in a cell but doesn't hit return - any
ideas on this too?

class Excelfile
{

public static void ExcelClass()
{
try
{

Object oMissing = System.Reflection.Missing.Value;
Object oTrue = true;
Object oFalse = false;

Process[] processlist = Process.GetProcessesByName("Excel");
//Shows number of running Excel apps
foreach (Process theprocess in processlist) //foreach Excel
app running
{
wkbs1.wkbs2(theprocess);
System.Threading.Thread.Sleep(500);
theprocess.Kill(); //kill the Excel process when
returning from the wkbs1 class ie after saving the workbooks in the first
application
}
}

catch (Exception e)
{
string error = e.Message;
}

finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
}

class wkbs1
{
public static void wkbs2(Process theprocess)
{
Excel.Application oExcelApp = (Excel.Application)System.Runtime

..InteropServices.Marshal.GetActiveObject("Excel.Application");

if (oExcelApp.Workbooks.Count >= 0)
{
foreach (Excel.Workbook wkb in oExcelApp.Workbooks)
{
oExcelApp.DisplayAlerts = false; //Turn display alert off

//Save files using their own names in the specified folder
Object oSaveAsFileExcel1 = "C:\\" + "Copy of " + wkb.Name;

//Save each workbook in the application
wkb.SaveAs(oSaveAsFileExcel1, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange,
XlSaveConflictResolution.xlLocalSessionChanges,
true, Type.Missing, Type.Missing, Type.Missing);

//Release the wbk object
Marshal.ReleaseComObject(wkb); //Release the Excel wkb object
System.Threading.Thread.Sleep(500);
}
}

//Close active workbook so as to move on to the next one
System.Threading.Thread.Sleep(500);
oExcelApp.Workbooks.Close();
System.Threading.Thread.Sleep(500);
oExcelApp.Quit();

return;
}
}
 

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

Top