Unable to close excel instance

[carrie09]

Hi,
I have a code that creates an excel report from a template file and query output.
System was freezing if user runs this reports again without closing the previously created file. So I included a code that checks if a file "customerpricing.xls" is already open on user's system and if it is then it gives user a message and stops running the report. However if there is no such file open then it runs fine and creates the report.
The problem I'm having after including the code(included in asterisks ****) is that if there is a file with same name open, then it gives the error message and user can go ahead and close the file. However it doesn't kill the excel instance in task manager for some reason.
Can someone please advise whats wrong with this code?


Public Function getrmpricing()
Dim queryoption As String
Dim ans, Msg As String
Dim fs As Object
Dim sTemplateFile As String
Dim e_TemplateFile As String

*****Dim oWB As Excel.Workbook

For Each oWB In Excel.Workbooks
If oWB.Name = "customerpricing.xls" Then
MsgBox "Your spreadsheet was already open. Please close the file and run the report again.", _
vbCritical, "WARNING"
On Error Resume Next
DoCmd.CLOSE acForm, "rmpricingdataform"
GoTo Errortrap
Exit For
End If

Next
Set oWB = Nothing ******

On Error Resume Next

sTemplateFile = g_dashboard & "crm proposal input.XLT"
e_TemplateFile = "C:\"


If Forms!rmpricingdataform!BU = "CS" Then
MsgBox "No template available for CS!", vbOKOnly, "RM Pricing Report"
Else


Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile sTemplateFile, e_TemplateFile, True


Dim xl As Excel.Application
Set xl = New Excel.Application

xl.Workbooks.Open e_TemplateFile & "crm proposal input.XLT"



DoCmd.OutputTo acOutputQuery, "CustPricingbyRMCrosstabquery", acFormatXLS, "c:\customerpricing.xls", True


Dim xs As Excel.Application
Set xs = New Excel.Application

xs.Workbooks("customerpricing").Activate
xs.ActiveWorkbook.Activate



Select Case Forms!rmpricingdataform!BU

Case "CRM"
xl.Run "'crm proposal input.XLT'!CRM_CAPSPriceTemplate.CRM_CAPSPriceTemplate"

End Select
'xs.Workbooks.CLOSE - NEWLY COMMENTED OUT
xl.Workbooks("crm proposal input.XLT").CLOSE
'xl.Workbooks("crmpricing.xls").Save - NEVER USED

fs.deletefile e_TemplateFile & "crm proposal input.XLT", True
Set fs = Nothing

DoCmd.CLOSE acForm, "rmpricingdataform"
Call AuditTrail("RM Pricing report", "Execute")


End If

Errortrap:

End Function

 

[rvBasic]

you have to quit excel when the job is finished

xs.quit
set xs=nothing

_________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]

 

[carrie09]

Thanks but its still not making any difference. The important thing is that the code works fine if I get rid of the code added to check if file is open.
Its only after I added this code that it doesn't kill the excel instance even if user closes the file.

 

[HughLerwill]

Not tested but suspect it is due to a probable unqualified reference in your line;

For Each oWB In Excel.Workbooks

You are referring to the Excel.Workbooks collection directly rather than wrapping it in an Application object you have created.

Typically this causes Excel to get trapped in memory and the instance can only be quit via Task Manager. Such code will often run on the first pass but fails on a second.

Try;

For Each oWB in ObjXl.Worksheets

where ObjXl describes an Excel Application object you have set up.

 

[carrie09]

Hi Hughlerwill,

What you said does make sense however I guess I'm not doing something correctly.
I changed the code as below and am getting error "Method Worksheets of object '_Application' failed". Can you please suggest what I', doing wrong here:

Public Function getrmpricing()
Dim queryoption As String
Dim ans, Msg As String
Dim fs As Object
Dim sTemplateFile As String
Dim e_TemplateFile As String
Dim xl As Excel.Application
Set xl = New Excel.Application

Dim oWB As Excel.Workbook

For Each oWB In xl.Worksheets

If oWB.Name = "customerpricing.xls" Then
MsgBox "Your spreadsheet was already open. Please close the file and run the report again.", _
vbCritical, "WARNING"
On Error Resume Next
DoCmd.CLOSE acForm, "rmpricingdataform"

GoTo Errortrap
Exit For
End If

Next

Set oWB = Nothing

On Error Resume Next

sTemplateFile = g_dashboard & "crm proposal input.XLT"
e_TemplateFile = "C:\"


If Forms!rmpricingdataform!BU = "CS" Then
MsgBox "No template available for CS!", vbOKOnly, "RM Pricing Report"
Else


Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile sTemplateFile, e_TemplateFile, True




xl.Workbooks.Open e_TemplateFile & "crm proposal input.XLT"



DoCmd.OutputTo acOutputQuery, "CustPricingbyRMCrosstabquery", acFormatXLS, "c:\customerpricing.xls", True


Dim xs As Excel.Application
Set xs = New Excel.Application

xs.Workbooks("customerpricing").Activate
xs.ActiveWorkbook.Activate



Select Case Forms!rmpricingdataform!BU

Case "CRM"
xl.Run "'crm proposal input.XLT'!CRM_CAPSPriceTemplate.CRM_CAPSPriceTemplate"

End Select
'xs.Workbooks.CLOSE - NEWLY COMMENTED OUT
xl.Workbooks("crm proposal input.XLT").CLOSE
'xl.Workbooks("crmpricing.xls").Save - NEVER USED

fs.deletefile e_TemplateFile & "crm proposal input.XLT", True
Set fs = Nothing

DoCmd.CLOSE acForm, "rmpricingdataform"
Call AuditTrail("RM Pricing report", "Execute")

End If

Errortrap:


End Function

 

[HughLerwill]

>For Each oWB In xl.Worksheets

Sorry not to have noticed before but you are apparently looking for WorkBooks in a Collection of WorkSheets; that is not going to work.

 

[HughLerwill]

My mistake, try;

For Each oWB in ObjXl.WorkBooks

 

[carrie09]

If I use "For Each oWB in Xl.WorkBooks" instead of the line "For Each oWB In xl.Worksheets", it runs the code fine for the first time.
However if I run the report again with the first file open, it doesn't give the message and stop the code like its supposed to but keeps trying to create the report freezes the system.

 

[HughLerwill]

Your On Error Resume nexts may be masking the real problems in your code which should (when it is right) run without them. If you must use Resume Next be sure to turn it off with an On Error Goto 0 as soon as possible.

You are currently using early binding of the Excel Object which is (Excel)version dependent and personally discouraged. Late binding using Object variables is personally preferred.

Be sure to set all object variables to nothing when you are done with them.

 

[JerryKlmns]

carrie09
To see if one excel workbook is opened

Dim objExcel As Object 'Excel.Application
Dim objWrkBook As Object 'Excel.WorkBook
Dim objWrkSheet As Object 'Excel.WorkSheet

On Error Goto 0
'Grab excel if running
Set objExcel = GetObject(, "Excel.Application")
If Err = 0 Then
  'Well, it was running indead
  If objExcel.WorkBooks.Count Then
     'User has workbooks open!
     Err.Clear 
     'Get the offending workbook
     Set objWrkBook = objExcel.WorkBooks("customerpricing")
     If Err = 0 Then
         'Well, the offending workbook was open
         MsgBox "The bloody workbook is open"
     End If
   End If
Else
Set objExcel = CreateObject("Excel.Application")
End If

or

On Error Goto 0
FileCopy  "c:\customerpricing.xls",  "c:\Temp\customerpricing.xls"
If Err <>0 Then
   MsgBox "The bloody workbook is open"
Else
   MsgBox "The bloody workbook is NOT open"
'Clean then mess
   Kill "c:\Temp\customerpricing.xls"
End If

 

[carrie09]

Thanks Jerry but I'm still having issues.
With the 2nd piece of code, it works fine and creates the report if there is no customerpricing file already open.
HOwever if there is such a file open, then instead of giving a message about file being open and exit the function, I get a runtime error that "permission denied". It might be coz customerpricing is not a read only file.
Anyways so I tried the 1st piece of code, this doesn't run the report at all and gives runtime error 429 "ActiveX component can't create object". This is giving error on 3rd line:
Set objExcel = GetObject(, "Excel.Application")

Do you want me to send over the macro as well that is used to create the report? Code runs fine if I don't include the code to check if file is open. Please advise.

 

[carrie09]

Please help

 

[JerryKlmns]

carrie09

Exapmles try to catch an error.
The 1st, grabs excel if already running or else you get an error.
The 2nd, copies the file if not open or else you get an error.

You did include
On Error Goto 0

If you need an error handler, trap the errors in there and act accordingly for those specific errors you faced by the examples.

 

[HughLerwill]

Jerry,

With respect you start both examples with On Error Goto 0, surely that should be On Error Resume Next.

 

[carrie09]

Thanks Jerry and Hugh however I'm lost.
I'm just not able to get this thing right and I have to get this done before end of this week. Can either one of you please look at the code I posted initially and make changes to that to include the option of checking already open file? I'll be really greatful.

Once again the requirement is:
Check if there is a file called "customerpricing.xls" already open on user's machine.
If such a file is open, then give a error message and stop code execution.
If no such file is open, run the code.

Thanks

 

[HughLerwill]

If you analyse what you have been given you should be able to solve your issues.
You are lost because you are waiting for one of us to write your code for you; that may happen but maybe not this week.

 

[HughLerwill]

My current best (and last) shot untested;

Public Function getrmpricing()
Dim fs As Object
Dim sTemplateFile As String
Dim e_TemplateFile As String

Dim oWB As Object

With CreateObject(Excel.Application)
For Each oWB In .Workbooks
If oWB.Name = "customerpricing.xls" Then
MsgBox "Your spreadsheet was already open. Please close the file and run the report again.", _
vbCritical, "WARNING"
On Error Resume Next
DoCmd.Close acForm, "rmpricingdataform"
GoTo Errortrap
End If

Next

sTemplateFile = g_dashboard & "crm proposal input.XLT"
e_TemplateFile = "C:\"

If Forms!rmpricingdataform!BU = "CS" Then
MsgBox "No template available for CS!", vbOKOnly, "RM Pricing Report"
Else

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile sTemplateFile, e_TemplateFile, True

.Workbooks.Open e_TemplateFile & "crm proposal input.XLT"

DoCmd.OutputTo acOutputQuery, "CustPricingbyRMCrosstabquery", acFormatXLS, "c:\customerpricing.xls", True

.Workbooks("customerpricing").Activate
.ActiveWorkbook.Activate

Select Case Forms!rmpricingdataform!BU

Case "CRM"
.Run "'crm proposal input.XLT'!CRM_CAPSPriceTemplate.CRM_CAPSPriceTemplate"

End Select
.Workbooks("crm proposal input.XLT").Close

fs.deletefile e_TemplateFile & "crm proposal input.XLT", True
Set fs = Nothing

DoCmd.Close acForm, "rmpricingdataform"
Call AuditTrail("RM Pricing report", "Execute")

End If
End With

Errortrap:

Set oWB = Nothing


End Function

From your 'DoCmd.CLOSE' it appears you are doing this in MS Access; you should have posted your question in the Access forum not this one which is for VB 5 & 6.

 

[JerryKlmns]

HughLerwill[\b]

You are absolutely right!

carrie09[\b]
Change this

On Error Goto 0

with this

On Error Resume Next

 

[carrie09]

Thanks Hugh...
I totally understand what you are saying but I have no formal training in VB and started working in it just 2-3 weeks back. I just had to get this report created and would then be done with VB coding. So I was trying to avoid getting books and understanding all basics.
That being said, i thank you for yor time. The code you gave still gives error 429 about ActiveX component.
I guess I will have to start with the basics now..
Thanks once again for all your suggestions.

 

[BobRodes]

<trying to avoid getting books and understanding all basics

That's the sort of person that we charge for our time...