vba to handle macro prompt and password??

[eHanSolo]

Hi there,

Can anyone help with the following:

I want to be able to open an excel workbook with out any user intervention. So i've scheduled a task to open myWorkBook.xls. In here i've put in a sub workbook_open() which it will call other bits and pieces and then close down the workbook when done.

problem is i don't know how to overcome the Macro enable/disable prompt and also the Workbook password promt... any ideas at all??

many thanks!

e

 

[Fenrirshowl]

Amend the path and the password as required:

Workbooks.Open Filename:= _
        "D:\Documents and Settings\User1\My Documents\TekTip\Test.xls", password:="pw"

No macro or password prompt given.

Fen

 

[eHanSolo]

hi there,

Here's the code i have in ThisWorkBook. I still get the macro and password prompt... and then an error saying that this workbook is already open... Ideas?


Sub workbook_open()

Workbooks.Open Filename:="X:\myPath\myWorkBook.xls", Password:="myPassord"

End Sub

 

[Fenrirshowl]

I think I misunderstood - you have a single workbook you are trying to open manually (by double clicking or whatever), for which you don't want any warnings appearing? (The previous post assumed you were using code to open up a second workbook)

Got to: Tools menu\macros...\security
and set it to low - it's not recommended but will stop the "contains macro" warning appearing.

I'm not aware of how you can bypass the password protection if this has been set up from the "Save as.." option. You could incorporate it into the workbook_open macro and let it be bypassed under certain conditions (such as the workbook being opened from a specifically named computer/machine).

 

[eHanSolo]

Hey thanks for that... after some trial and error i got it to work (using another workbook).

I'm trying to call a public sub from the workbook i just opened and i want to run that sub on the workbook i've just opened as well. Any ideas?

e

 

[PHV]

You may consider the Application.Run method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[eHanSolo]

cool! thx.