Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Worksheet Hidden (2000)

    I have some VBA code running in Access that copies an existing Excel file containing a reference worksheet, adds another worksheet and some data to the file, then closes it. The code works fine except that when the new Excel file is subsequently opened, there is nothing to see. If you go to Window | Unhide from the Excel menus, and select the name of the xls, everything appears and is as it should be. I'm not doing anything in my code to hide the any of the worksheets, let alone the entire workbook, so I am a bit confused.
    The reason that I close the file and then open it again (using a Shell command) is that if I open it from the object model (XLApp.Visible = True) some add ins that are required for the next step in the process are missing from the Excel menus. They only seem to get loaded if Excel is started more traditionally.
    The original file, containing the reference worksheet is not hidden when you open it.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Excel Worksheet Hidden (2000)

    Are you using the ability to link to Excel workbooks, or are you doing an import and an export, or are you using Automation to actually run commands in Excel? And are you creating a new worksheet in an existing workbook, or are you using a template to store the data in an existing worksheet, and then saving the workbook with a different name? I've never experienced the kind of behavior you are seeing - but from your description, it sounds like you may be using Automation. If that's the case, then you should be able to open the Excel Add-ins in code and get what you want without closing and reopening the application.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Worksheet Hidden (2000)

    Hi Wendell,
    I use FileCopy to copy a straight (non template) xls, which contains some static data, to a new xls. I then use automation to add a new worksheet to the new xls and to populate it with data from various sources. I would then like to make the new xls visible, but when I do an Add-In that is required by the next step in the process is not loaded. This is why I leave automation and then open the new xls using Shell.
    How do I load an Add-In using Automation?
    Thanks again
    Ian

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

    Re: Excel Worksheet Hidden (2000)

    When I use Automation to run Excel from Access, Excel add-ins are loaded according to Tools | Add-Ins, but their menu options are not displayed.

    When I create or open a workbook using Automation, it is visible normally when I subsequently open it. But just to make sure it will be visible, you could use a line

    wbk.Windows(1).Visible = True

    where wbk is the Excel workbook object. Insert this line before the line where the workbook is saved.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Worksheet Hidden (2000)

    That worked. Thanks.

Posting Permissions

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