Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 28th, 2007, 09:21 AM
Authorized User
 
Join Date: Mar 2007
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.



 
Old August 29th, 2007, 12:09 AM
Friend of Wrox
 
Join Date: Sep 2005
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)
 
Old August 29th, 2007, 11:56 AM
Authorized User
 
Join Date: Mar 2007
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?

 
Old September 3rd, 2007, 03:34 AM
Friend of Wrox
 
Join Date: Sep 2005
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)
 
Old September 5th, 2007, 01:54 AM
Registered User
 
Join Date: Sep 2007
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





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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.