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

Check if a Table is used in any sp or function

Hello, there,

I am trying to find out if a table is used by any of the stored
procedures or functions.

I can generate all the scripts and look for it. But is there an easy
way?

THX

JohnQuery the syscomments table looking for the name of the table.

Q. John Chen wrote:

Quote:

Originally Posted by

Hello, there,
>
>
I am trying to find out if a table is used by any of the stored
procedures or functions.
>
I can generate all the scripts and look for it. But is there an easy
way?
>
THX
>
John

|||Q. John Chen (qjchen@.email.com) writes:

Quote:

Originally Posted by

I am trying to find out if a table is used by any of the stored
procedures or functions.
>
I can generate all the scripts and look for it. But is there an easy
way?

sp_depends.

However, it's not reliable, since dependencies are lost if the table
is dropped and recreated. Or the proc/function was created before the
table was.

syscomments that Stu mentions is neither that safe, as text here is
sliced into chunks of 4000 chars, and the table name could occur
on a chunk border.

So scripting is the only way. Or searching the version-control system.
Because you do keep all your code under version control. don't you?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Q. John Chen schreef:

Quote:

Originally Posted by

Hello, there,
>
>
I am trying to find out if a table is used by any of the stored
procedures or functions.
>
I can generate all the scripts and look for it. But is there an easy
way?
>
THX
>
John

Yep, there is:

CREATE proc [FindInObjects] (@.Search varchar(300))
as

SELECT so.xtype, so.name, sc.TEXT
FROM dbo.syscomments sc RIGHT OUTER JOIN
dbo.sysobjects so ON sc.id = so.id
WHERE so.xtype IN ('P', 'V', 'FN', 'TR') AND (sc.TEXT LIKE '%' +
@.Search + '%')

This will show you a list of all the objects that contain the search
string.

Enjoy,

GJ|||(gjvdkamp@.gmail.com) writes:

Quote:

Originally Posted by

CREATE proc [FindInObjects] (@.Search varchar(300))
as
>
SELECT so.xtype, so.name, sc.TEXT
FROM dbo.syscomments sc RIGHT OUTER JOIN
dbo.sysobjects so ON sc.id = so.id
WHERE so.xtype IN ('P', 'V', 'FN', 'TR') AND (sc.TEXT LIKE '%' +
@.Search + '%')
>
This will show you a list of all the objects that contain the search
string.

...unless the procedure name in question has been split up over a
chunk border. Keep in mind that the procedure text in syscomments is
split up in chunks of 4000 characters, and the split can well be in
the middle of an identifier.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Chart X-axis & Y-axis Title

Does RS allow to use expression to generate titles for X- and Y-axis, either
through a field obtained from the database or a Parameter ?
Right now, even after SP1, I do not see a button that leads to <Expression>
dialog for X and Y-axis title, similar to say a <textbox> control.
Thanks.Although there is no expression builder button there, you can type any
expression you want for axes titles.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:9543D0CD-C766-4ED5-BEDA-CD43728F2728@.microsoft.com...
> Does RS allow to use expression to generate titles for X- and Y-axis,
> either
> through a field obtained from the database or a Parameter ?
> Right now, even after SP1, I do not see a button that leads to
> <Expression>
> dialog for X and Y-axis title, similar to say a <textbox> control.
> Thanks.