Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 28th, 2007, 09:21 AM
Authorized User
 
Join Date: Mar 2007
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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_CAPSPriceTemp late"

        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.



Reply With Quote
  #2 (permalink)  
Old August 29th, 2007, 12:09 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

You can loop thru the Workbooks collection to find the one you need

Here is a hint

Sub Loop_Thru_Wkbooks()

Dim wkbk As Workbook
For Each wkbk In Workbooks
    MsgBox wkbk.Name
Next wkbk

End Sub

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
  #3 (permalink)  
Old August 29th, 2007, 11:56 AM
Authorized User
 
Join Date: Mar 2007
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks shasur..But I'm not sure how to do this. I'm pretty new to VB so would you be able to give me the exact code?
The filename generated by my code is "customerpricing.xls".
So I want a code to check if "customerpricing.xls" is already open on user's machine. Criteria used is:

If its open, then give a message that "customerpricing.xls is already open. Creating file customerpricing_1.xls"
Generate file customerpricing_1.xls"
If its not open, no message required. Create file "customerpricing.xls"

You might want to tweak my code to include this. Can you please help?

Reply With Quote
  #4 (permalink)  
Old September 3rd, 2007, 03:34 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Check if this can help you

Sub Loop_Thru_Wkbooks()

Dim bWBFound As Boolean

Dim wkbk As Workbook
For Each wkbk In Workbooks
    If InStr(1, wkbk.Name, "") Then
      ' The user already has an wrkbook by the name
        bWBFound = True
        Exit For
    End If
Next wkbk

If bWBFound = True Then
    Workbooks.Add
    Workbooks.SaveAs sPath & "customerpricing1.xls"
Else
    Workbooks.Open sPath & "customerpricing.xls"
End If



End Sub

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
  #5 (permalink)  
Old September 5th, 2007, 01:54 AM
Registered User
 
Join Date: Sep 2007
Location: Mablethorpe, Lincolnshire, United Kingdom.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This is a User Defined Function to test if a specific workbook is open

Option Explicit
Function wbOpen(wbName As String) As Boolean
' returns TRUE if the workbook is open
    wbOpen = False
    On Error GoTo wbNotOpen
    If Len(Application.Workbooks(wbName).Name) > 0 Then
        wbOpen = True
        Exit Function
    End If
wbNotOpen:
End Function

Below the UDF is used to check if the Duppliers.xls is open, if not open it.


Sub TestOpen()
If Not wbOpen("Suppliers.xls") Then
    Workbooks.Open "Suppliers.xls"
End If

End Sub

Hope that helps

Roy
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
mdi form check to see if childforms are open RainX C# 2005 0 August 28th, 2007 12:55 PM
Open the "Open File" dialogue box piratelordx Access VBA 4 March 14th, 2006 10:08 PM
check file open bala_7272 Visual C++ 1 September 15th, 2004 07:44 PM
Check for file open bala_7272 C++ Programming 0 September 6th, 2004 08:27 AM
check if file is an image file silver_cuts Classic ASP Basics 10 June 30th, 2004 12:33 AM



All times are GMT -4. The time now is 10:00 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.