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. #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

  3. #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

  4. #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

  5. #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

  6. #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
  •