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

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

In Excel97 how disable macros in opened workbook programmatically

I have an Excel97 workbook with several macros in it. The macros are designed to read in xls files from around 12 folders. Each Excel workbook is then processed to delete all but one specific sheet, delete all buttons, combo boxes etc from this sheet, and remove all VBA code. The workbooks are then saved again in another folder. The whole thing is a house keeping exercise deigned to save space on the server. The problem I have is that all the workbooks have macros which run when the workbook is opened. In some cases this can cause an error, so the process keeps being interrupted. I need to be able to disable the macros in the opening workbook before they get to run. Unfortunately there doesn't seem a way of doing this. I tried a number of combinations based around
code such as :-

    Workbooks.Open FileName:=FileNameInput, ReadOnly:=True, _
        disablemacros:=True
 but disablemacros is an invalid argument

HELP

 
0
macbone2
Asked:
macbone2
  • 3
  • 2
1 Solution
 
bruintjeCommented:
Hi macbone2,

you could start it like

Application.EnableEvents = False
    Workbooks.Open _
        FileName:=(Directory & MyFile)
    ' Do your processing
Application.EnableEvents = True

HTH:O)Bruintje
0
 
macbone2Author Commented:
Thanks Bruintje. It works a treat. Should have known it myself. Award yourself 100 points.
I'm posting another question tomorrow about removing some stubborn VBA macros programmatically. I've done it before, but there are 1 or 2 in each workbook which refuse to go. Never had the problem before.


Thanks again

Geoff McKeown (macbone2)
0
 
bruintjeCommented:
glad it worked! > to accept my answer you can use the accept comment as answer button......
0
 
macbone2Author Commented:
I already posted the 100 points Brian

Thanks

Geoff
0
 
bruintjeCommented:
Hi Geoff, that's correct only i can't accept an answer for you ;)

if you look in the comment box where you found the solution there should be an <accept answer> label in the right corner, if you click there you can grade my comment into an answer and close this question as a PAQ (Previously Asked Question)

Only the member who asked the question can accept a comment as answer

I'll answer this and you can accept it ;) that's shorter so you can accpet my answer and grade it

<for PAQ readers answer is given above>

:O)Bruintje
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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