Solved

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

Posted on 2008-07-01
6
4,605 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
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

828 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