Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open and formatting workbook (XP)

    Hi
    I'm using TransferSpreadsheet (from vba in Access) to export 2 queries to a new workbook. This works fine.
    I'm then trying to open the resultant workbook to apply some formatting.

    This seems to work ok sometimes but on others I get 'Run-time error 9 - Subscript out of range' on the 'Set sht' line

    Set appExcel = CreateObject("Excel.Application")
    appExcel.Workbooks.Open (strWorkbookPath) 'path and filename 'this works
    appExcel.Application.Visible = True

    Workbooks(strWorkbookName).Activate

    Set sht = ActiveWorkbook.Worksheets(2) 'this sometime works sometimes fails

    Any ideas appreciated - this is driving me nuts
    There seems to be no pattern as to why it works or why it fails.

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

    Re: Open and formatting workbook (XP)

    When using Automation, you must fully qualify all objects in the application you automate. Otherwise, you will create a hidden second instance of the application, and this will cause all kinds of problems.

    Change

    Workbooks(strWorkbookName).Activate

    to

    appExcel.Workbooks(strWorkbookName).Activate

    and change

    Set sht = ActiveWorkbook.Worksheets(2)

    to

    Set sht = appExcel.ActiveWorkbook.Worksheets(2)

    Check the rest of your code too, to make sure that there are no unqualified references to Workbooks, ActiveWorkbook, ActiveSheet, ActiveCell etc.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open and formatting workbook (XP)

    Thanks, it seems to be behaving itself now.

    I discovered as I ran it and missed out some changes, errors cropped up unpredictably at different points.

    Why do the references need full qualification in this instance?

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

    Re: Open and formatting workbook (XP)

    If you don't tell VBA what (for example) Workbooks belongs to, it isn't always smart enough to guess that you meant appExcel.Workbooks. Sometimes it will create a new instance of Excel as "parent" for the unqualified Workbooks object. Some things will still work, but as you found unpredictable errors will occur.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open and formatting workbook (XP)

    As usual, a huge thanks

    <img src=/S/skipping.gif border=0 alt=skipping width=30 height=30>

Posting Permissions

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