+ Reply to Thread
Results 1 to 6 of 6

Workbooks.Open() fails in ThisWorkbook.Workbook_Open()

  1. 10-03-2011, 04:58 AM #1
    GordonRehling
    GordonRehling is offline
    Registered User
    Join Date
    10-03-2011
    Location
    Waltham Corss, Hertfordshire
    MS-Off Ver
    Excel and Access 2003
    Posts
    3

    Workbooks.Open() fails in ThisWorkbook.Workbook_Open()

    I would really appreciate some pointers as I have run into something where I clearly don't understand enough about Excel.

    I have now reduced the problem to the following
    Please Login or Register  to view this content.
    Is this too early in Excel startup to start opening another Excel workbook?



    ---- orignal question ----
    I have library code / classes that are successfully used in several Access based projects but one is generating an Error 91 object error when used in Excel.

    The code creates a separate Excel instance (via a global variable gXapp - excuse the ugliness) then opens an input file. In Access this works fine, but in Excel I get an error when exiting the OpenXbook function that returns an Excel.Workbook.

    If you step through the code, you can see the Excel instance in Windows Task Mgr, then the file contents in the Excel instance that is visible, then error as you exit the function.
    I've even tried dropping the gXapp and altering OpenXbook to use Application.Workbook.Open and still the same problem.

    Any ideas?

    Gordon

    ThisWorkbook contains
    Please Login or Register  to view this content.
    A separate module contains
    Please Login or Register  to view this content.
    ClassFileSystemFile - includes
    Please Login or Register  to view this content.
    Last edited by GordonRehling; 10-03-2011 at 08:19 AM. Reason: Refined problem
    Reply With QuoteRegister To Reply
  2. 10-03-2011, 06:43 AM #2
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Workbooks.Open() fails in ThisWorkbook.Workbook_Open()

    It's rather curious to open a new instance of excel -createobject("Excel.application")- if Excel is loaded already. What is it's purpose ?

    Reply With QuoteRegister To Reply
  3. 10-03-2011, 07:22 AM #3
    GordonRehling
    GordonRehling is offline
    Registered User
    Join Date
    10-03-2011
    Location
    Waltham Corss, Hertfordshire
    MS-Off Ver
    Excel and Access 2003
    Posts
    3

    Re: Workbooks.Open() fails in ThisWorkbook.Workbook_Open()

    The library code was originally written to run within Access, so the Excel instance was necessary.
    I'm just trying to re-use the existing code.

    Ideally I would want to write gXapp = Application to hook into the Excel I am in, but I can't figure out the syntax, so I have continued to use a new instance.

    I seem to be able to open the new instance, I can't pass the object handle back cleanly in Excel, whereas it works in Access.

    Even in my cut down code I can't see to generate a handle on the Workbook that I opened... I'm struggling with the Workbook.Activate syntax.

    Big picture is that there is a huge planning spreadsheet developed by someone else, and I am trying to extract pieces of information out of it, but the layoutis subject to movement, so I am trying to read config files that say where the data is located, rather than hardcode as I originally got working.

    Any pointers or ideas appreciated...
    Reply With QuoteRegister To Reply
  4. 10-03-2011, 07:24 AM #4
    quekbc
    quekbc is offline
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Workbooks.Open() fails in ThisWorkbook.Workbook_Open()

    My initial gut feel is that under Workbook_Open

    This...

    Please Login or Register  to view this content.
    should be this...
    Please Login or Register  to view this content.
    and same goes for InitialisePerformance.

    This
    Please Login or Register  to view this content.
    should be this...

    Please Login or Register  to view this content.
    Last edited by quekbc; 10-03-2011 at 07:26 AM. Reason: included InitialisePerformance
    Reply With QuoteRegister To Reply
  5. 10-03-2011, 07:55 AM #5
    romperstomper
    romperstomper is offline
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Workbooks.Open() fails in ThisWorkbook.Workbook_Open()

    I would agree. I'd also be amazed if this worked properly in any application (other than VB.Net since you don't need Set there)
    Remember what the dormouse said
    Feed your head
    Reply With QuoteRegister To Reply
  6. 10-03-2011, 08:17 AM #6
    GordonRehling
    GordonRehling is offline
    Registered User
    Join Date
    10-03-2011
    Location
    Waltham Corss, Hertfordshire
    MS-Off Ver
    Excel and Access 2003
    Posts
    3

    Re: Workbooks.Open() fails in ThisWorkbook.Workbook_Open()

    Well thank you guys for spotting that schoolboy error, a few Set's later and I'm through to the next stage. It's odd how you can write sophisticated automation code one day, yet get simple syntax wrong and not be able to spot it another day.

    I'm still not entirely clear why the syntax requires the use of Set... one would have thought that the compiler could figure out the intention of the following code and just assume the Set... 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1