Automatically enable the macros when a excel file is opened

[Guest]

Hi,
I have an excel workbook with a macro. My requirement is to schedule to run
the macro in this workbook. I am using a batch command to open the excel book
and would be scheduled in the windows scheduled tasks.

When this excel sheet is opened it pops up the message to "disable" or
"enable" macros. Is there a way that I can automatically run the macro
without this message. I would not be able to change the security level of
macro to "Low" which is the commond option suggested by Microsoft and else
where.

Your inputs will be appreciated and greatly helpful.

Thanks,
Sankar

 

[NickHK]

You can sign your VBA code (maybe with a certificate made with Office's
SelfCert.exe) and your users would then have trust it.
After that your code can run without that dialog.

Otherwise, you cannot change a user's Security setting in code, thankfully.

NickHK

 

[Guest]

Thanks Nick. Our environment does not allow the certificate to be created
even self . Probably I should try using some other commands to do enter
tab on the "Enable" Macros button.
Sankar

 

[NickHK]

You will not able to... because macros have been enabled yet.
If you can't sign it and macro security is not set to low, then you will be
able to open and run code in your workbook.
That's why there is security; to prevent unwanted code from running.
And as far as Excel see it, your code is not authourised by the user.

NickHK

 

[John Coleman]

VBScript might be able to help you. Perhaps the entire functionality of
the macro can be transfered to a script which automates Excel and is
scheduled to run automatically. Alternatively - a script can sidestep
the security settings. For example, I created a workbook Test.xls with
this macro:

Sub Disabled()
MsgBox "I guess VBScript can run me"
End Sub

Then I wrote this script (in same directory - otherwise the file path
would need to be absolute of course):

'RunMacro.vbs

Dim xlApp, xlWB

Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.WorkBooks.Open("Test.xls")
xlApp.Run "Disabled"
xlWB.Close
xlApp.Quit
Set xlWB = Nothing
Set xlApp = Nothing

When this script is run - a message box with "I guess VBScript can run
me"
appears no matter what the security setting is. Some people might think
that this is a security hole. I think that it is by design and it makes
sense - if you have the authority to run a VBScript script then you
already have the authority to run code that is able to do just about
anything you want to - it is not like you are giving VBScript any
ability to do damage that it doesn't already possess. Allowing VBScript
to run macros in other applications seems needed if it is to be a
useful glue language.

Having said that, I'll hedge and say that I have not launched Excel
macros from scripts very often and there may be some limitations that I
am not aware of - but it gives you something to start with. Also, if
you were to schedule something like the above to run automatically, you
would probably want to put some error-trapping in (e.g.what if an
instance of Excel is already running).

HTH

-John Coleman

 

[Guest]

Thanks every one for the inputs. Current what I have done is create a self
certificate for the excel in the machine where it allows. Transfer the file
to where it should run and reinstall the self certificate there. The windows
task scheduler runs this excel(macro) currently it is running fine. I shall
try the VB script idea now.

Thanks Again.
regards,
Sankar