Solved

Excel VBA: run sub/function when workbook is REopened from vba code

Posted on 2008-07-01
6
4,655 Views
Last Modified: 2013-11-25
This sounds simple enough, but after a few hours have yet to find a solution.

lets say in code I reopen the same workbook with something like:
Application.Workbooks.Open (ThisWorkbook.Name)

Now on reopening I would like to run some more code, perhaps a form, how can I achieve this.
I have tried, without success:
Workbook_Open
auto_open
Workbook_Activate

they all work fine when opening the workbook manually, but stop working if opened in code.

any solutions would be appreciated. I would prefer not to have to create a dummy workbook.
0
Comment
Question by:thydzik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 24

Expert Comment

by:purplepomegranite
ID: 21906927
You are trying to open the same workbook from itself?  That doesn't make a lot of sense, and I'm not surprised you are not having much success.

Open events DO fire if you open a macro enabled workbook programmatically.  But you cannot open a workbook from itself.

If you create a new instance of Excel, however, you can open a read-only copy of your current workbook - and any open events will fire correctly.  If you use the same instance of Excel, the workbook won't be reopened.  
Sub Test()
    Dim x As New Excel.Application
    x.Visible = True
    x.Workbooks.Open ThisWorkbook.Name
End Sub

Open in new window

0
 
LVL 11

Author Comment

by:thydzik
ID: 21907127
thanks for the reply.

I guess I should explain the reason for wanting to reopen the workbook first:
I have an Excel 2003 file, that when it is opened in Excel 2007 I would like it not to be in compatibilty mode.

Now the only way I have found to solve this, is save the file on auto_open as Excel 2007, but this is still in compatibility mode. So I must reopen the file again to then get out of compatibility mode.

I have tried your solution, it is a suitable option, but means having a read only file, and closing the old workbook and excel.

Do you know of any other approaches to the compatibility mode issue?
0
 
LVL 24

Accepted Solution

by:
purplepomegranite earned 500 total points
ID: 21907377
Ok, that makes a little more sense, and I have worked out some code that will do this.

The attached autoopen macro will save the file in Excel 2007 Macro enabled format, changing the extension appropriately (and this is important to prevent a loop).

It does require a dummy subroutine in a normal module (I called mine Test) so that the Application.OnTime event works properly.

The process is:
- Save as new Excel 2007 workbook.
- Set timer event to call subroutine.
- Close workbook.
- When timer event fires (in five seconds), the new workbook is opened and the dummy subroutine (which in my case did nothing) is called.
Private Sub Workbook_Open()
    Dim strWBName As String
    strWBName = ThisWorkbook.Name
    If LCase(Right(strWBName, 4)) <> "xlsm" Then
        strWBName = Left(strWBName, InStr(1, strWBName, "."))
        strWBName = strWBName & "xlsm"
        ThisWorkbook.SaveAs strWBName, xlOpenXMLWorkbookMacroEnabled
        Application.OnTime Now + TimeValue("00:00:05"), "Test"
        ThisWorkbook.Close
    End If
End Sub

Open in new window

0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 11

Author Comment

by:thydzik
ID: 21907643
thanks,

works great, very smart. pity about the dummy subroutine but guess can't have it all.

never used the ontime method before so learnt something there.
0
 
LVL 24

Expert Comment

by:purplepomegranite
ID: 21907678
One thing I didn't try was to set the subroutine called to "Workbook_Open".  It may work, but I wasn't confident.  If it does, then that gets rid of the dummy subroutine - but if it doesn't (as I suspect), then the above is the only way I can think of!
0
 
LVL 11

Author Comment

by:thydzik
ID: 21907809
doesn't work on the Workbook_Open, as it is not in a module, but I moved all the code to auto_open, with the OnTime procedure as auto_open and that worked great.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question