Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Salisbury, Maryland, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Test for open workbook (2000)

    Hello,
    I need to test for an open workbook from another application. I need to open it and bring it forward if if it is not open; if it is open, I need to simply bring it forward.

    I tried the following but it opens the workbook all the time, even if it is open...

    Sub OpenExcel()
    Dim appExcel As Excel.Application
    Dim exWkBook As Excel.Workbooks
    On Error Resume Next

    Set exWkBook = Workbooks("Cocuments and Settingsudn4973My DocumentsProjectsplantsysdemotemps.xls")

    If exWkBook Is Nothing Then 'Workbook not open, open it
    Set appExcel = New Excel.Application
    Set exWkBook = appExcel.Workbooks
    exWkBook.Open "Cocuments and Settingsudn4973My DocumentsProjectsplantsysdemotemps.xls", , 1
    appExcel.Visible = True
    Set exWkBook = Nothing
    Set appExcel = Nothing
    On Error GoTo 0
    Else ' Workbook open, bring it forward
    appExcel.Visible = True
    Set exWkBook = Nothing
    Set appExcel = Nothing
    On Error GoTo 0
    End If


    End Sub

    Could someone help please?

    Thanks!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Test for open workbook (2000)

    Does this work? I didn't know what application was calling it so I couldn't test it except in excel.

    Be careful with on error resume next, it will show you NO ERRORS whatsoever. It is usually better to turn it on for a particular reason and then remove it immediately after the purpose is past. It is used here to test for a file open.

    Steve


    <pre>option explicit
    Sub OpenExcel()
    Dim sPath As String
    Dim sName As String
    Dim exWkBook As Excel.Workbook

    sPath = "Cocuments and Settingsudn4973My DocumentsProjectsplantsysdemo"
    sName = "temps.xls"

    On Error Resume Next
    Set exWkBook = Workbooks(sName)
    On Error GoTo 0

    If exWkBook Is Nothing Then 'Workbook not open, open it
    Workbooks.Open sPath & sName, , 1
    Else ' Workbook open, bring it forward
    exWkBook.Activate
    End If
    Set exWkBook = Nothing
    End Sub</pre>


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

    Re: Test for open workbook (2000)

    I think you need to change this line:

    Set exWkBook = Workbooks("Cocuments and Settingsudn4973My DocumentsProjectsplantsysdemotemps.xls")

    To:

    Set exWkBook = AppExcel.Workbooks("Cocuments and Settingsudn4973My DocumentsProjectsplantsysdemotemps.xls")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    New Lounger
    Join Date
    Sep 2002
    Location
    Salisbury, Maryland, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for open workbook (2000)

    Steve,
    Thanks for the reply. It works fine from Excel only. It doesn't seem to do anything from other applications. I tested it it in Word also. I did add the Excel 9.0 reference library to the projects. I am trying to use it from an industrial HMI software package called RSView32 by Rockwell Software.

  5. #5
    New Lounger
    Join Date
    Sep 2002
    Location
    Salisbury, Maryland, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for open workbook (2000)

    Thanks for the reply Jan,
    I changed the line but it still opens a new copy of the workbook every time the code runs. I tested it from both MS Word and the software I am trying to use it in.

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for open workbook (2000)

    hi Dan,

    you'll need to use the getobject method.

    Sub OpenandorActivateandBringForward()
    Dim ExcelFile As Object
    'dim ExcelFile as excel.workbook 'early binding
    Set ExcelFile = GetObject("Cocuments and Settingsudn4973My DocumentsProjectsplantsysdemotemps.xls")
    ExcelFile.Parent.Visible = True
    ExcelFile.Parent.UserControl = True
    ExcelFile.Windows(1).Visible = True
    excelfile.Activate
    End Sub

  7. #7
    New Lounger
    Join Date
    Sep 2002
    Location
    Salisbury, Maryland, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for open workbook (2000)

    Hi pieter

    Thanks for the reply, this code does almost what I need it to do. It does open the workbook if not open and bring it forward. It does not allow more than one copy to be open.
    However, it will not bring the workbook forward if it is already open. It will activate the task bar and highlight the excel button, but that's all. I tested it in MSWord also with the same results.

    Thanks!

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for open workbook (2000)

    hi Dan,

    set the windowstate by inserting the following statement just after the excelfile.parent.visible=true codeline:

    ExcelFile.Parent.WindowState = -4137

Posting Permissions

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