Solved

In Excel97 how disable macros in opened workbook programmatically

Posted on 2002-03-19
5
261 Views
Last Modified: 2008-02-01
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
Comment
Question by:macbone2
  • 3
  • 2
5 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6880162
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
 
LVL 2

Author Comment

by:macbone2
ID: 6880403
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
 
LVL 44

Expert Comment

by:bruintje
ID: 6880421
glad it worked! > to accept my answer you can use the accept comment as answer button......
0
 
LVL 2

Author Comment

by:macbone2
ID: 6881285
I already posted the 100 points Brian

Thanks

Geoff
0
 
LVL 44

Accepted Solution

by:
bruintje earned 100 total points
ID: 6881735
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

864 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now