Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Excel 2003: Can't show a Workbook opened by GetObj (Excel 2003)

    Hi all,

    I can't make visible a Workbook that I opened using GetObject().
    Somebody suggested using the code below:


    Dim DB_Workbook As Workbook
    On Error Resume Next
    Set DB_Workbook = GetObject("C:FullPathToWorkbook.xls")
    If Err.Number = 432 Then

    MsgBox "Workbook not open."

    Else

    MsgBox "Workbook open."

    End If
    DB_Workbook.Application.Visible = True

    First, when I run that code, then even when the workbook happens to be closed, GetObject() always opens it -- so MsgBox "Workbook not open." is never called -- which is fine by me.

    But the main thing is that I get a phenomenon I have already bumped into. When GetObject() finds that DB_Workbook is not open, and so it opens it, then, I can access the DB Excel file contents through another Excel app that I have written (my "Viewer"), but:

    - the DB_Workbook remains invisible

    - the DB_Workbook does not appear in the Windows task list

    So now the next thing I want to be able do, since I am debugging, is to make the Excel DB_Workbook visible. So I tried coding:

    DB_Workbook.Application.Visible = True

    But it does not make the DB_Workbook visible. As a matter of fact, I see from the IDE that DB_Workbook.Application.Visible was already set to True.

    Ideas?

    Tia,

    - avi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel 2003: Can't show a Workbook opened by GetObj (Excel 2003)

    Are you trying to do this from within Excel or from within another application?
    If the latter, have you already started Excel from the other application?

  3. #3
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Excel 2003: Can't show a Workbook opened by GetObj (Excel 2003)

    I have two Excel files. One is a large list of "records" , like a personnel database. actually, I am using this Excel file as a database.

    I have an additional Excel file that I have designed to be a "viewer" on to the DB above. The appearance of the Viewer is designed so that it will view the person entries one entry at a time. The Viewer provides me Frwd/Back/Update/Clear/New operations.

    When I open the Viewer file. it automatically opens the DB Excel file. In actual use the DB Excel file should be invisible. However, while I am debugging I want it to be visible. Also, while I am debugging, sometimes the session resets, eg I have a bug, so I have to reset the VBA env, so all the variables lose their values. So I have to rerun my Init routine, so I have to get a handle to the DB Excel file. The DB Excel file is still open, so I want to use GetObject() or something to get a handle to it. I can do this, but the problem is that I can't then make the DB Excel file visible, and also it does not appear in the Windows task list. Thus also, I can't close the DB Excel file, but I know it is there since my Viewer can access it.

    Tia

    - avi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel 2003: Can't show a Workbook opened by GetObj (Excel 2003)

    GetObject is meant to be used when automating Excel from another application. I wouldn't use it on an Excel workbook from within Excel.

    If you're running the code from within Excel, you can check whether the workbook is already open, and if not, open it:

    Dim wbk As Workbook
    On Error Resume Next
    Set wbk = Workbooks("Workbook.xls")
    If wbk Is Nothing Then
    Set wbk = Workbooks.Open("C:FullPathToWorkbook.xls")
    If wbk Is Nothing Then
    MsgBox "Cannot find workbook!"
    Exit Sub
    End If
    End If
    On Error GoTo 0 ' or to an error handler

  5. #5
    2 Star Lounger
    Join Date
    Dec 2007
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Excel 2003: Can't show a Workbook opened by GetObj (Excel 2003)

    As far as I remember this is the first method I tried (long before I knew GetObject() existed...), and I found that I could not set the Workbook to invisible. Maybe I wrong. Can I set it to Invisible this way? I read that I had to open the workbook in a new app.

    Maybe I can open it as a sheet and make the sheet invisible ?

    tia

    - avi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel 2003: Can't show a Workbook opened by GetObj (Excel 2003)

    After setting the wbk variable as indicated in my previous reply, you can use

    wbk.Windows(1).Visible = False

    to hide the workbook's window.

Posting Permissions

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