Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4742
  • Last Modified:

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

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
thydzik
Asked:
thydzik
  • 3
  • 3
1 Solution
 
purplepomegraniteCommented:
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
 
thydzikAuthor Commented:
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
 
purplepomegraniteCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
thydzikAuthor Commented:
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
 
purplepomegraniteCommented:
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
 
thydzikAuthor Commented:
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now