Saving and closign multiple Excel applications?

[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 permantly (until the program finishes) or not attach to the next
open Excel application once I save and close down the first. I have used
timers, and I thought that this may help when releasing the references, and
better able to see what is happening.

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

class Excelfile4
{

public static void ExcelClass4(string datestring, object dirString,
string timestring)
{
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();
}
}

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

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

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

class wkbs1
{
public static void wkbs2(Process theprocess)
{
DateTime nowdate = DateTime.Now;
string datestring = nowdate.Date.ToLongDateString();
string tempstring = nowdate.ToShortTimeString(); //use to create
directory with time stamp
string timestring = tempstring.Replace(":", "_"); //replace the
colon with an underscore
string dirSt = "C:\\" + datestring;

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 = dirSt + @"\" + timestring + "
Copy of " + wkb.Name;

//Save each workbook
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;
}
}

Regards,

Stephen

 

[Oliver Bock]

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 permantly (until the program finishes) or not attach to the next
open Excel application once I save and close down the first. I have used
timers, and I thought that this may help when releasing the references, and
better able to see what is happening.

Sorry but I do not know exactly what is causing your problem. However...

You don't seem to be considering that an Excel instance may host
multiple documents or each document may be hosted by a separate Excel
instance, or some combination of these two scenarios. I suggest a
different approach: Find all open Excel Workbooks using the Running
Object Table and close them. While doing this, accumulate a set of
distinct Excel instances. Quit the distinct Excel instances.


Oliver