Opening Excel Workbook Without Running Macros 1

[mattygriff]

Hi all,

I need to roll out some changes to some template spreadsheets I developed recently.

My plan was to write up an Excel macro that would ask the user to navigate to the workbook. My macro would then open the workbook, change the formulae that need changing and save the workbook back in the same location.

The problem I have is that the workbooks that need changing have macros in them that automatically run whenever they are opened and that is a bit of a problem so I need, if possible, to open them without running the macros.

I have searched about online and currently have the following :

Sub OpenFileForChanges(strFileToOpen As String)

    Dim NewWB As Object    ' Variable to hold reference to new Excel instance

    Set NewWB = GetObject(strFileToOpen)

    NewWB.Application.EnableEvents = False
    
End Sub

strFileToOpen is provided from a separate function where the user navigates to the workbook that needs to be updated.

I thought this code would open the second workbook in a separate instance of Excel so that I could disable the macros but that doesn't seem to be happening. I appear to be getting both files in the same instance although I can't actually see the second workbook at all.

Does anyone have any suggestions? I am using Excel 2007 if that makes any difference.

Thanks, in advance.

 

[SkipVought]

Hi,

Open the workbook but do not enable macros.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mattygriff]

Apologies, Skip - didn't make myself clear. The second workbook is opening and the macros are running automatically, presumably because I have enabled macros in the original workbook.

 

[SkipVought]

In order to STOP any macro that is running when you OPEN a workbook, and presumably, when you open workbook A it automatically runs a macro that opens workbook B, In orfer to prevent that from happening, you must not allow the workbook A macro to run. YES?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Before calling OpenFileForChanges, you may try this:
Application.AutomationSecurity = msoAutomationSecurityForceDisable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mattygriff]

PHV - thanks, will give it a try.

Skip - again, apologies if I haven't been clear but I thought that, as long as Workbook B was opened in a separate instance of Excel, I could set the macro security to a different level, i.e. disabled, while keeping the macros in Workbook A running. Is this not the case?

 

[SkipVought]

I thought you were doing some maintenance. So you wanted the macros that run on open disabled.

You could assign a switch in a cell that you can use to disable whatever you want for maintenance. Be creative!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mattygriff]

PHV - many thanks, that seems to work fine. Is there anything I need to add at the end of my code to revert the security settings back to the default or will that happen automatically when I close down Excel?

Skip - thanks for your replies but PHV's solution works great.

 

[mattygriff]

OK, the changes all work fine and I can close and save the amended workbook with no problem.

However, when I next try to open the amended workbook it is ALWAYS hidden - I have to click on View -> Unhide to get it back on screen.

I've searched around online and tried a few things but here's where I am at the moment.....

Sub OpenFileForChanges(strFileToOpen As String)

    Dim NewWB As Object    ' Variable to hold reference to new Excel instance

*************** CHANGES MADE HERE ***************
********** CODE NOT COPIED FOR CLARITY **********

    NewWB.Worksheets("Cover").Activate
        
    Application.Windows(NewWB).Visible = True
    
    NewWB.Close savechanges:=True
    
    blnChangesMade = MsgBox("Changes have been made and saved" & vbCrLf & _
                                "Thank you - this worksheet will now close.", vbOKOnly)
                                
    If blnChangesMade = vbOK Then
    
        ActiveWorkbook.Close False

    End If
    
End Sub

Is there anything I can do between making the various changes and saving the workbook to stop this happening?

Thanks again.

 

[PHV]

Replace this:
Application.Windows(NewWB).Visible = True
with this:
NewWB.Application.Visible = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mattygriff]

Thanks, PHV, but no success - it still needs to be manually unhidden the next time I try and open it.

 

[mattygriff]

Something I've just noticed - when I try and re-open the amended workbook, I get the dialog asking me whether or not I want to enable macros.

Usually in Excel 2007 when you open a new workbook without having Excel opened previously, the workbook just opens and you get an "Options" button up near the menu bar that you need to click to enable the macros.

Not sure this means anything but I just noticed and it seemed a bit strange!

 

[combo]

If there were no changes after last save, SaveChanges is ignored. Try:

With NewWB
    .Windows(1).Visible=True
    .Save
    .Close
End With

combo