Creating a workbook, pasting to it, close it - all hidden

[David Thielen]

Hi;

For Excal 2000 - 2003 (and 2007/2010 if necessary), how can I

1. create a hidden workbook with 1 worksheet
2. paste what I copied from part of a worksheet in the active workbook
to that new workbook - starting at cell [0,0]
3. save/close the hidden workbook.

??? - thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Colbert Zhou [MSFT]]

Hello Dave,

Workbook does not have a Visible property, so we cannot hide a workbook.
However, we can hide the workbook related Window via
Workbook.Windows[1].Visiable property. So to achieve your objective, I
write the following codes to simulate the scenario,

-----------------------------------------------------------------
static void Main(string[] args)
{
//Create the source workbook
Excel.Application app = new Excel.Application();
app.Visible = true;
Excel.Workbook wbSource = app.Workbooks.Open(@"D:\test.xlsx",
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
//Create a new workbook and hide it
Excel.Workbook wbHidden = app.Workbooks.Add(Type.Missing);
wbHidden.Windows[1].Visible = false;
//Copy the source range
Excel.Range rangToCopy =
((Excel.Worksheet)wbSource.ActiveSheet).get_Range("A1", "C10");
rangToCopy.Copy();
//Paste the source range
Excel.Range cell =
((Excel.Worksheet)wbHidden.ActiveSheet).Cells[1, 1];
cell.PasteSpecial(Excel.XlPasteType.xlPasteAll,
Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
Type.Missing, Type.Missing);
//Save and close the hidden workbook.
//Node we need to set the Window.Visible property back to true.
//Otherwise next time it is opened, it will still be invisble
wbHidden.SaveAs(@"D:\workbook.xlsx", Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing, Type.Missing);
wbHidden.Windows[1].Visible = true;
wbHidden.Close(true , Type.Missing, Type.Missing);
}
-----------------------------------------------------------------


Best regards,
Ji Zhou
MSDN Subscriber Support in Forum

 

[David Thielen]

Worked great - thanks - dave

Hello Dave,

Workbook does not have a Visible property, so we cannot hide a workbook.
However, we can hide the workbook related Window via
Workbook.Windows[1].Visiable property. So to achieve your objective, I
write the following codes to simulate the scenario,

-----------------------------------------------------------------
static void Main(string[] args)
{
//Create the source workbook
Excel.Application app = new Excel.Application();
app.Visible = true;
Excel.Workbook wbSource = app.Workbooks.Open(@"D:\test.xlsx",
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
//Create a new workbook and hide it
Excel.Workbook wbHidden = app.Workbooks.Add(Type.Missing);
wbHidden.Windows[1].Visible = false;
//Copy the source range
Excel.Range rangToCopy =
((Excel.Worksheet)wbSource.ActiveSheet).get_Range("A1", "C10");
rangToCopy.Copy();
//Paste the source range
Excel.Range cell =
((Excel.Worksheet)wbHidden.ActiveSheet).Cells[1, 1];
cell.PasteSpecial(Excel.XlPasteType.xlPasteAll,
Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
Type.Missing, Type.Missing);
//Save and close the hidden workbook.
//Node we need to set the Window.Visible property back to true.
//Otherwise next time it is opened, it will still be invisble
wbHidden.SaveAs(@"D:\workbook.xlsx", Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing,
Type.Missing,
Type.Missing, Type.Missing);
wbHidden.Windows[1].Visible = true;
wbHidden.Close(true , Type.Missing, Type.Missing);
}
-----------------------------------------------------------------


Best regards,
Ji Zhou
MSDN Subscriber Support in Forum

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm