Clear Excel data using VB Script - File Already Open

Clear Excel data using VB Script - File Already Open

Post by Shami » Wed, 28 Aug 2002 06:09:56



HI All,
     Using VBScript trying to clear a Excel Report - Clear data  and Save
the file.

 Set XLApp = CreateObject("Excel.Application")
 Set xlWorkbook = XLApp.Workbooks.Open("C:\MyExcel.xls")

With xlWorkbook.ActiveSheet.UsedRange
  For n = 2 to .Rows.Count.Rows(n).EntireRow.Clear
  Next
 End With

xlWorkbook .Save

' Close Excel with the Quit method on the Application object.
 xlWorkbook.Application.Quit

 ' Release the object variable.
 Set xlWorkbook = Nothing

Error with File is already open, how to handle this ??

Thanks
Shamim

 
 
 

Clear Excel data using VB Script - File Already Open

Post by Dinesh.T.K » Wed, 28 Aug 2002 06:16:06


Shamim,

Try adding this...

xlWorkbook .close

Dinesh
--
--
SQL Server FAQ at
www.tkdinesh.com


Quote:> HI All,
>      Using VBScript trying to clear a Excel Report - Clear data  and Save
> the file.

>  Set XLApp = CreateObject("Excel.Application")
>  Set xlWorkbook = XLApp.Workbooks.Open("C:\MyExcel.xls")

> With xlWorkbook.ActiveSheet.UsedRange
>   For n = 2 to .Rows.Count.Rows(n).EntireRow.Clear
>   Next
>  End With

> xlWorkbook .Save

> ' Close Excel with the Quit method on the Application object.
>  xlWorkbook.Application.Quit

>  ' Release the object variable.
>  Set xlWorkbook = Nothing

> Error with File is already open, how to handle this ??

> Thanks
> Shamim


 
 
 

Clear Excel data using VB Script - File Already Open

Post by Anith Se » Wed, 28 Aug 2002 06:29:54


Shouldn't Application.Quit automatically close the
application?

--
- Anith

 
 
 

Clear Excel data using VB Script - File Already Open

Post by Allan Mitchel » Wed, 28 Aug 2002 06:34:23


Close the workbook just as I do in this article

Deleting an Excel WorkSheet from within a package
http://www.sqldts.com/default.aspx?t=6&s=101&i=245&p=1&a=0

--

Allan Mitchell
MCSE, MCDBA
www.SQLDTS.com

 
 
 

Clear Excel data using VB Script - File Already Open

Post by Dinesh.T.K » Wed, 28 Aug 2002 06:59:55


Anith,

Shouldnt necessarily be.We also had the same issue long while back and hence
my hint.

Dinesh
--
--
SQL Server FAQ at
www.tkdinesh.com


Quote:> Shouldn't Application.Quit automatically close the
> application?

> --
> - Anith

 
 
 

Clear Excel data using VB Script - File Already Open

Post by Shami » Wed, 28 Aug 2002 23:42:44


I tried the following with 'Run Time Error during the execution of the
script'

Set XLApp = CreateObject("Excel.Application")
Set xlWorkbook = XLApp.Workbooks.Open("C:\MyReport.xls")

 With xlWorkbook.ActiveSheet.UsedRange
   For n = 2 to .Rows.Count
      .Rows(n).EntireRow.Clear
   Next
 End With

 'Save the File
 xlWorkbook.Save

 'Close the File
 xlWorkbook.Close

 ' Close Excel with the Quit method on the Application object.
 xlWorkbook.Application.Quit

 ' Release the object variable.
 Set xlWorkbook = Nothing

Any Catch.......

Thanks
Shamim


> Anith,

> Shouldnt necessarily be.We also had the same issue long while back and
hence
> my hint.

> Dinesh
> --
> --
> SQL Server FAQ at
> www.tkdinesh.com



> > Shouldn't Application.Quit automatically close the
> > application?

> > --
> > - Anith

 
 
 

Clear Excel data using VB Script - File Already Open

Post by Shami » Thu, 29 Aug 2002 00:08:16


Thanks Allan.. It worked..
This Group is GREAT...

Shamim


Quote:> Close the workbook just as I do in this article

> Deleting an Excel WorkSheet from within a package
> http://www.sqldts.com/default.aspx?t=6&s=101&i=245&p=1&a=0

> --

> Allan Mitchell
> MCSE, MCDBA
> www.SQLDTS.com

 
 
 

Clear Excel data using VB Script - File Already Open

Post by Allan Mitchel » Thu, 29 Aug 2002 05:58:46


The amount of people I have seen with Excel.EXE still running on their
servers after incorrectly closing it down overnight is amazing.

--

Allan Mitchell
MCSE, MCDBA
www.SQLDTS.com

 
 
 

Clear Excel data using VB Script - File Already Open

Post by Darren Gree » Thu, 29 Aug 2002 06:34:04




Quote:>The amount of people I have seen with Excel.EXE still running on their
>servers after incorrectly closing it down overnight is amazing.

..and even when you do close it down correctly.

We added a SQL Agent Job, with a single CmdExec step of Kill Excel to
cope with this.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

 
 
 

Clear Excel data using VB Script - File Already Open

Post by MVP » Thu, 29 Aug 2002 10:26:49



> I tried the following with 'Run Time Error during the execution of the
> script'

You're calling the Quit method via the workbook's application object *after* you have released the workbook by setting it to nothing.  You already have a separate xlApp object to use for the Quit call.

 'Close the File
 xlWorkbook.Close

 ' Release the object variable.
 Set xlWorkbook = Nothing

 ' Close Excel with the Quit method on the Application object.
 xlApp.Application.Quit
 set xlApp = Nothing

--
Michael Harris
Microsoft.MVP.Scripting
Seattle WA US
--