Results 1 to 4 of 4
  1. #1
    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

    Force loading of New Excel Copy on file open (Excel97)

    Is it possible to have excel open a NEW copy of itself when a particular file opens (via the file -open or through explorer)?
    Also in doing so if excel is already running to "disable/acknowledge" the "personal.xls is being modified" message when a new copy opens.

    I would like to create an excel workbook that seems more like an "application" with custom menubars, etc but realize that people might be (while using my application) still wanting to use excel with the "normal" menus, etc.

    Thanks for your help.
    Steve

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Force loading of New Excel Copy on file open (Excel97)

    This is not very simple. But here goes.

    In the application you have, insert a class module (In the VBE: Insert, Class module).

    In the project explorer window, Click on the class module and change it's name (in the properties window) to: "Events" (without the quotes)

    Paste this code into it:

    Option Explicit

    Public WithEvents App As Application

    Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    If bOpenHere then exit sub
    Dim sFileName As String
    sFileName = Wb.FullName
    Wb.Close False
    Shell Application.Path & "excel.exe " & sFileName
    End Sub

    In the Thisworkbook module of the same project (workbook), paste this :

    Option Explicit
    Dim AppClass As New Events

    Private Sub Workbook_Open()
    Set AppClass.App = Application
    End Sub

    (or add the lines to your thisworkbook module)

    In a Normal module, put this public Boolean at the declaration section (above all subs):

    Public bOpenHere as Boolean

    Now what does it do?

    The class module creates an event handler for the application that will respond to the opening of any workbook (You'll have to catch workbooks that do NOT need to be opened in a separate window !). A soon as a workbook is opened, the event code stores it's name and path, closes it and opens it in a separate Excel session.

    To prevent the sub from opening workbooks in separate excel session, set bOpenHere to True before opening (and set it back to False afterwards!):

    bOpenHere=True
    Workbooks.Open blah, blah, blah
    bOpenHere=False
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    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: Force loading of New Excel Copy on file open (Excel97)

    Thank you, Jan for your response. But I had several problems / questions with this.

    1) The application file opens, closes and never opens again. It seems that it closes and never gets to the reloading line before it reopens. I tried adding an "OR" to check that the file is the "Application" and this worked, but it opens even if there is another excel application open in the current version. I thought about checking to make sure that it is the only workbook open, but personal.xls may or may not be there. I suppose I could see if workbooks(1).name = "personal.xls" and then check the count and act accordingly. Are there any other files that may also be loaded?

    2) the shell line did not work. I think it did not like file names containing spaces: I changed the line
    sFileName = Wb.FullName
    to:
    sFileName = chr(34) & Wb.FullName & chr(34)
    and this seemed to fix it.

    3) When a NEW application is loaded I get an error that "personal.xls" is already opened. Is there a way to acknowledge this without it displaying?

    Alternately is there a way to prevent personal.xls from opening?

    The option is there to If excel is already open when the application is loaded (file is application, check for personal.xls and any other workbooks?) open a copy WITHOUT personal.xls (or alternaltely open with message acknowledged, and then close personal.xls if it is there).

    If excel is NOT already opened, open it and then close personal.xls.

    I think I can work through most of the logic (if there is an easier way, please let me know). The snag I think I have (and I am not sure yet if I understand the class module bit) how to open a new copy of excel without opening personal.xls or how do I open excel with it loading as a Read-only copy without the message box.

    Any suggestions are greatly appreciated.

    Steve

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Force loading of New Excel Copy on file open (Excel97)

    Hi,

    I was a bit afraid my solution would be too simple.

    1) I forgot to think about more files being loaded at startup. One way to overcome this when you are loading your application is to set the bOpenHere to True inside the Workbook_open event and then add a line at the end of the Workbook_open event code that starts a macro in a second or so that sets the OpenHere boolean back to false.

    in the thisworkbook module:

    Private Sub Workbook_Open()
    bOpenHere = True
    Set AppClass.App = Application
    Application.OnTime Now + TimeValue("00:00:02"), "ResetOpenHere"
    End Sub

    and in the normal module:

    Sub ResetOpenHere()
    bOpenHere = False
    End Sub

    2) Sorry 'bout that.

    3)

    You can avoid loading of add-ins (and personal.xls) by adding the automation switch:

    Command line:

    "crogram filesmicrosoft officeofficeExcel.exe" /Automation

    But that prevents loading of all add-ins, which is not always wanted.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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