Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    249
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA is a file already open (Excel 2000)

    I am writing an excel VBA program which needs to open or access various files
    I need an if statement to do the following:

    If the file is already open then choose Window.Select."file mane"
    Else open the file."file name"

    I have currently coded the program so that it will open the file. I need to introduce the If bit of the statement. Also I would like to automatically choose the option of not updating any extrenal links which the file to be open may have.

    I would be grateful to hear from anyone who knows how to do this

    Thanks

    Alex

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA is a file already open (Excel 2000)

    Try this:

    Function IsOpen(sFilename As String) as Boolean
    Dim oWkbk as Object
    On Error Resume Next
    Set oWkbk=Workbooks(sFilename)
    IsOpen = Not oWkbk Is Nothing
    End Function

    Now in your code:
    sFileName="YourFileName"
    sPath="C:YourPath"
    If IsOpen("YourFileName") Then
    'Do not open it
    Else
    Workbooks.Open sPath & sFilename, UpdateLinks=0
    End If
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    249
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA is a file already open (Excel 2000)

    Jan

    Thanks very much for the reply. If I may ask a supplementary question re the update links problem:

    My file name is held in a string variable, when I and some colleagues tried the solution by coding

    workbooks.open(Stringvariable, Updatelinks =0) ' and various other iterations of this line

    compile error messages were returned.

    Can you give us a hint as to how to get around this?

    many thanks

    Best regards

    Alex

  5. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA is a file already open (Excel 2000)

    There are syntax errors in your code. Arguments to functions must be either all be without their qualifier, or all be with a qualifier. In the first case, they must be in the correct order. Also, the assignment should include a semicolon, not: filename="Hithere", but Filename:="Hithere".

    Also, parens are only used when one wants the method to return a return value or object (whatever it may be). Since you don't use a return value, no parens are needed.
    Your code should therefor read:

    Workbooks.Open Filename:=Stringvariable, Updatelinks:=0 ' and no more iterations of this line <smile>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    249
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA is a file already open (Excel 2000)

    Many thanks from the whole department. It works now. You have been elevated to the status of Excel Guru.

    Best regards

    Alex

  7. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA is a file already open (Excel 2000)

    <<You have been elevated to the status of Excel Guru>>

    So should I be able to levitate now <vbg>?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •