Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hidden Workbooks (2000/9.0.6926 SP-3)

    I have a VBA program that runs from MS Project and writes data to Excel. It writes to Excel properly the first time that I run it. After that, it runs through the entire macro to my ending message box, but when I click on the Excel spreadsheet to which it supposedly wrote, it is empty. If I check TaskManager, it doesn't show any other workbooks open. However, if I restart Windows, it asks me if I want to close workbooks that haven't shown up as open. Then, after I've rebooted, the same macro works again, the first time only, and I go through the entire routine again. I suspect that the data is writing to the workbook that I can't see. How can I find these hidden workbooks?

    Thanks,
    Caroline

  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: Hidden Workbooks (2000/9.0.6926 SP-3)

    Are you creating a new occurrence of excel in Project:
    set xlApp = Create...

    If you are, it is probably hidden (default)

    Use the line:
    xlApp.visible = true

    to see it

    Steve

  3. #3
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Workbooks (2000/9.0.6926 SP-3)

    Steve, that code (xlApp.Visible = True) is already in there. It works perfectly the first time that I use it after I've booted up. After that, it hides the workbook and I can't find it. I have to boot up again in order to get it visible when I run the macro.

    Caroline

  4. #4
    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: Hidden Workbooks (2000/9.0.6926 SP-3)

    Could you share the code?

    Steve

  5. #5
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Workbooks (2000/9.0.6926 SP-3)

    (edited by HansV to move very long code fragment to attachment.)

    It's pretty long so I took out the main body of the code.

    Thanks,
    Caroline

  6. #6
    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: Hidden Workbooks (2000/9.0.6926 SP-3)

    What if you move the xlapp.visible line after the end if?

    It is only called when you create the app, not when you get it. If you have a hidden copy in memory, it could be grabbing this.
    Steve

  7. #7
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Hidden Workbooks (2000/9.0.6926 SP-3)

    Caroline,


    Here are some general guidelines to use when automating Excel...

    1. Set a reference to the primary Excel objects used in your program.
    Dim xlApp As Excel.Application
    Dim WB As Excel.Workbook
    Dim WS As Excel.Worksheet

    Set xlApp = New Excel.Application
    Set WB = xlApp.Workbooks.Add
    Set WS = WB.Sheets(1)

    Use the appropriate reference Every Time you make reference _
    to a spreadsheet.
    Do not use Range(xx) - use WS.Range(xx)
    Cells should be WS.Cells(10, 20) or _
    WS.Range(WS.Cells(10, 20, WS.Cells(20, 40))

    2. Avoid the use of ActiveSheet, ActiveWorkbook, Selection etc.
    Use your object references.

    3. Avoid the use of the "With" construct.

    4. Set all objects to Nothing in the proper order - child then parent.
    Set WS = Nothing
    WB.Close SaveChanges:=True 'your choice
    Set WB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    '-------------------------------------------------------------

    It appears that you are leaving orphan references in your code:
    "Cells.Select"
    Should be...
    WS.Cells

    In addition, your use of the "xlR" range object appears questionable:
    "Set xlR = xlBook.Worksheets(1).Range("A1")
    With xlR.Range("A2")"

    If you set a reference to the Worksheet, then you can refer directly to
    each range that you want...
    WS.Range("A2")

    Also, you are not closing the workbook and quitting the application.

    Regards,
    Jim Cone
    San Francisco, CA

Posting Permissions

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