Check if file is already open

Hi,

I wrote a VB code to generate a xls file. Users are able to run it fine but if they have another file with same name already open, then it just crashes excel.

So I want to include a code that checks if file "file.xls" is open on user's machine.

If file is open, then message "file "File.xls" is already open. Generating File_1.xls"

Run the code but create the file with file name "file_1.xls"

If file doesn't exist, then run code and create file with file name "File.xls"

So basically I want the code to run and generate the file. Only difference is that if file with same name is already open, then just rename the newly created file.

Here's the code I've created for generating the file:

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

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 New Excel.Application
xl.Workbooks.Open e_TemplateFile & "crm proposal input.XLT"

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

Dim xs As 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 - NEWLY COMMENTED OUT
Set fs = Nothing

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

End Function

Please advise.

Not the right forum, try this one

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?query=Excel&dg=&cat=en_US_d02fc761-3f6b-402c-82f6-ba1a8875c1a7&lang=en&cr=&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-us

sql