Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB Databases
|
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 12th, 2008, 03:33 AM
Registered User
 
Join Date: Nov 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Ident Open Workbooks in Multiple Excel Instances

I'm working through 'Access 2003 VBA', which is proving very useful.
The section on Working with Excel is where I am at present.

A test piece I've built uses:
- comdlg32.dll to pick a file name,
- If a file of that name already exists, and is open, it must be closed before it is deleted (so that it can be overwritten); the following is used to refer to any open instance of Excel:

xlApp = GetObject(, "Excel.Application")

- Originally, code then checked each open file in xlApp, and closed it if it had the same name as that returned by the file picker.

However, if there was more than one instance of Excel open, that was inadequate. Alternative code to quit each instance of Excel was written:

    Set xlApp = Nothing
    On Error GoTo en429
    Set xlApp = GetObject(, "Excel.Application")
    Do
        xlApp.Quit
        Set xlApp = Nothing
        Set xlApp = GetObject(, "Excel.Application")
    Loop
en429:
    Select Case Err.Number
        Case 429 'xlAPP cannot be created, because there are no open instances of Excel.
        Case Is <> 0
        Debug.Print Err.Number
        Debug.Print Err.Description
        Stop 'Unexpected error that should be recorded and trapped.
    End Select
    Err.Clear
    On Error GoTo 0

This worked when it was stepped through, but when run at normal speed, if crashed, typically with the following:

Error 462: The remote server machine does not exist or is unavailable.

Please help - Ideally, I want all instances of Excel checked and only the file with the same name as that picked with comdlg32.dll being closed.

Any thoughts / experience you have with identifying open files in multiple instances of Excel would gratefully be received.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating Report from Multiple Workbooks krism Excel VBA 1 June 28th, 2007 08:52 AM
excel workbooks.open bug smiler_00 Pro VB 6 0 May 9th, 2007 05:24 AM
Multiple browser instances Dilip Nagle Classic ASP Basics 1 September 29th, 2005 10:33 PM
Looping through Excel Workbooks collection clickco Excel VBA 0 April 14th, 2004 12:21 PM
Display names of open workbooks in a MsgBox rmcc Excel VBA 1 November 11th, 2003 05:59 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.