PDA

View Full Version : Password in Workbook open VBA code?



thomas.szwed
12-17-2007, 06:36 AM
Hi,

I have a master workbook which has confidential data on and is therefore password protected. I have created many other workbooks that all link to the master workbook to pull in specificied data. Trouble is to do that you have to enter in the master workbooks password. Is there anyway i can program that into the code so that when the user clicks on the file it just opens and updates without any password prompts?

Many Thanks

unmarkedhelicopter
12-17-2007, 07:55 AM
Yes, but your password is then exposed to anyone reading the code.

thomas.szwed
12-17-2007, 07:56 AM
That doesn't bother me as my user wont be divulging that far.......Could you tell me how?

Bob Phillips
12-17-2007, 08:03 AM
Then why bother having a password?

thomas.szwed
12-17-2007, 08:15 AM
So my users cant access the master workbook with all the data, just their specified workbook which brings back relevant data to them. They wont be scouring for the password, its no interest to them.

Do you know the code?

Thanks

unmarkedhelicopter
12-17-2007, 09:32 AM
workbooks.Open path\filename,,,,password

thomas.szwed
12-17-2007, 09:33 AM
Does it open up the other workbook though or just pull the data in?+ im not quite sure how this code fits in with the ,,,,,? could you give me a clearer example? Thanks

unmarkedhelicopter
12-17-2007, 10:31 AM
Does it look like it pulls data in ?
You PM'ed me to ask for input on an open thread (very annoying) to ask how to open a password protected workbook with vba.
Well you use the open command. Path is the path to the workbook and filename is the name of the workbook and password is the workbook password. There are MANY parameters that can be passed with the open method the comma's just put password in the correct place.
Have you EVER used the help system within the VBE ?

thomas.szwed
12-18-2007, 02:18 AM
No your right, it doesnt look like it is pulling the data in just opening another workbook. Which if you had bothered to read my question you would realise that i am looking for something which would update the links to the other spreadsheet and simultaneously bypassing the password input box...........Im sure someone else may be more qualified to help better than yourself.......:banghead:

Bob Phillips
12-18-2007, 02:51 AM
Thomas,

UMH may be grumpy old bugger, but there is substance in what he is saying. If you just take a look at the VBA help for workbook open you will see the followin (attached at the foot) which tells you all you need.

You may be happy to alienate UMH, but if not, saying things like ... someone more qualified than yourself ... is not exactly smart tactics. We are all giving our time freely here, but it is not unreasonable to expect the poster to do some basic research, especially someone who is involved in what seems to be a major VBA involvement. You cannot reasonably expect us to do it all for you.

Not trying to beat you up on this, just trying to ask for some give and take.

Anyway, the help

expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
expression Required. An expression that returns the Workbooks object.

FileName Required String. The file name of the workbook to be opened.

UpdateLinks Optional Variant. Specifies the way links in the file are updated. If this argument is omitted, the user is prompted to specify how links will be updated. Otherwise, this argument is one of the values listed in the following table.

Value Meaning
0 Doesn't update any references
1 Updates external references but not remote references
2 Updates remote references but not external references
3 Updates both remote and external references

If Microsoft Excel is opening a file in the WKS, WK1, or WK3 format and the UpdateLinks argument is 2, Microsoft Excel generates charts from the graphs attached to the file. If the argument is 0, no charts are created.

ReadOnly Optional Variant. True to open the workbook in read-only mode.

Format Optional Variant. If Microsoft Excel is opening a text file, this argument specifies the delimiter character, as shown in the following table. If this argument is omitted, the current delimiter is used.

Value Delimiter
1 Tabs
2 Commas
3 Spaces
4 Semicolons
5 Nothing
6 Custom character (see the Delimiter argument)

Password Optional Variant. A string that contains the password required to open a protected workbook. If this argument is omitted and the workbook requires a password, the user is prompted for the password.

WriteResPassword Optional Variant. A string that contains the password required to write to a write-reserved workbook. If this argument is omitted and the workbook requires a password, the user will be prompted for the password.

IgnoreReadOnlyRecommended Optional Variant. True to have Microsoft Excel not display the read-only recommended message (if the workbook was saved with the Read-Only Recommended option).

Origin Optional Variant. If the file is a text file, this argument indicates where it originated (so that code pages and Carriage Return/Line Feed (CR/LF) can be mapped correctly). Can be one of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. If this argument is omitted, the current operating system is used.

Delimiter Optional Variant. If the file is a text file and the Format argument is 6, this argument is a string that specifies the character to be used as the delimiter. For example, use Chr(9) for tabs, use "," for commas, use ";" for semicolons, or use a custom character. Only the first character of the string is used.

Editable Optional Variant. If the file is a Microsoft Excel 4.0 add-in, this argument is True to open the add-in so that it?s a visible window. If this argument is False or omitted, the add-in is opened as hidden, and it cannot be unhidden. This option doesn't apply to add-ins created in Microsoft Excel 5.0 or later. If the file is an Excel template, True to open the specified template for editing. False to open a new workbook based on the specified template. The default value is False.

Notify Optional Variant. If the file cannot be opened in read/write mode, this argument is True to add the file to the file notification list. Microsoft Excel will open the file as read-only, poll the file notification list, and then notify the user when the file becomes available. If this argument is False or omitted, no notification is requested, and any attempts to open an unavailable file will fail.

Converter Optional Variant. The index of the first file converter to try when opening the file. The specified file converter is tried first; if this converter doesn?t recognize the file, all other converters are tried. The converter index consists of the row numbers of the converters returned by the FileConverters property.

AddToMru Optional Variant. True to add this workbook to the list of recently used files. The default value is False.

Local Optional Variant. True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).

CorruptLoad Optional Variant. Can be one of the following constants: xlNormalLoad, xlRepairFile and xlExtractData. The Default behavior if no value is specified is usually normal but may be safe load or data recovery, if Excel has already attempted to open the file.The first attempt is normal. If Excel stops operating while

thomas.szwed
12-18-2007, 05:28 AM
After doing some research i got this far.....but i am now getting an "End of statement" compile error?? Can you see where im going wrong?


Private Sub Workbook_Open()

Application.Workbooks.Open ("\\ukyorw09\HR\IAC (file://\\ukyorw09\HR\IAC) New Starter Tracking\IAC\Application\Tracker.xls", True, False,,"password","password")

End Sub


Thanks

Bob Phillips
12-18-2007, 05:54 AM
Remove the brackets.

matthewspatrick
12-18-2007, 06:26 AM
Thomas,

Bob and UMH have given you good advice, despite the manners.

My only bit to offer is that, if the data in that master workbook truly are sensitive and confidential, then what you are trying to do is downright misguided. Excel offers no "security" worthy of the name. If you really do need to protect that data, then I suggest that Excel is not the right application for your project. vbmenu_register("postmenu_126985", true);

thomas.szwed
12-19-2007, 03:29 AM
I am well aware of Excels inadequacy to deal with my requirements but i am not allowed to use Access or anything like this because it is not standard in our company. Therefore i just have to use what ive got.

My problem still remains open. The answer i posted below doesnt work as i would of imagined, so please to close this off can someone help me out.

Detailed analysis -

I have a workbook called 'Dalston'. It reads data off my master workbook 'Master' where any of the records have Dalston as a field. BUT since i password protected my 'Master' workbook everytime i open the 'Dalston' workbook, when you click ot Update the Links it requires the password to read data from the 'Master' workbook. I tried using the code below to do this in the background, but this actually opens up the 'Master' workbook so it is visible! Is there a way to just update the data in my 'Dalston' workbrook from the 'Master workbook without the user being able to physically see it! I think there is because you only have to type in the password originally, and that never opened up the 'Master' workbook. Your help is much appreciated.


Private Sub Workbook_Open()

Dim sh As Object
For Each sh In ThisWorkbook.Sheets
sh.Protect Password:="dalston11", userinterfaceonly:=True
Next sh

Application.Workbooks.Open "\\ukyorw09\HR\IAC (file://\\ukyorw09\HR\IAC) New Starter Tracking\IAC\Application\Tracker.xls", True, False, , "password", "password"
Application.DisplayFormulaBar = False
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Drawing").Visible = False

Run "FormatSheet"

End Sub



That is the code i am currently using on workbook open.

unmarkedhelicopter
12-19-2007, 04:10 AM
From this I gather you either work in Dalston or York in HR and you work for Nestl?, why don't you just get someone in group IT to develop your application for you ? (do you have money in this years allocation left ?)
Alternatively speak to Ian Binns if you're at Dalston or one of the engineering team either at CSC, one of the factories or the RECO.
It's not true what you say about Access either, it just is not commonly deployed (you have to buy a licence (do you have money in this years allocation left ?)). You could use an MBD (Access database) from Excel without ever using Access. Speak to G Forrest.
If you're not impressed now you never will be.

thomas.szwed
12-19-2007, 04:15 AM
Access is a non standard tool for us in Nestle therefore we are not allowed to make use of it. That is why im having to use Excel. I would of easily done this with a database and web app in .aspx if i had my own way but manager only let me use Excel. I am in Group IS i just dont have any VBA knowledge!!! I know there is an answer to my problem its just finding someone helpful.....if you dont know then just say........save the shpiel!

unmarkedhelicopter
12-19-2007, 04:34 AM
You aren't listening are you ?
You want to use a database then use access but with an Excel front end.
You don't need an Access licence and you get 99% of the functionality.
There are LOTS of threads on using databases form access and I hesitate to point you to mine (cos you clealy couldn't be bothered) so literally pick up the phone and call John Gallagher, he won't necessarily know (but you can never tell with John) but he will know someone who does (know something about VBA and/or using an MDB from Excel), but then Group IS never did want to learn anything meaningful from MIS did they. Got any more "shpiel" ?

Oh ! and another thing "divulge" means to reveal not to "delve" or go searching.

thomas.szwed
12-19-2007, 04:38 AM
But thats just a waste of time, abit like your overall comments. Why dont you just give over and tell me what im doing wrong with my VBA. Im not going to start changing my project at the 11th hour to what your suggesting. MIS are having a mer, they take our advice and i can clearly see you like nothing other than your own way....XLD where are you?

unmarkedhelicopter
12-19-2007, 05:22 AM
Because I like watching you squirm ...
There are MANY people in your organisation who are paid and as part of their work could help you. Instead you choose to come to a forum to ask for help because you are embarassed. You ask of volunteers to assist you in your work for which you are paid and you give them "attitude". You are rude in ignoring forum ettiquette and DEMAND answers. I have given you many names of the people you could just pick up a phone and talk to; but no you wish to perpetuate (means to continue) this. You said "I am well aware of Excels inadequacy to deal with my requirements but i am not allowed to use Access or anything like this because it is not standard in our company. Therefore i just have to use what ive got." and later when shown that you could still use a database you changed your story. Well I'm bored so keep going by all means.
Meanwhile try this :-
Private Sub Workbook_Open()
Dim sh As Object
Application.Displayalerts = false
Application.Screenupdating = false
For Each sh In ThisWorkbook.Sheets
sh.Protect Password:="dalston11", userinterfaceonly:=True
Next sh
Application.Workbooks.Open "\\ukyorw09\HR\IAC New Starter Tracking\IAC\Application\Tracker.xls", True, False, , "pastille", "pastille"
Application.wait now() + timeserial(0,0,5)
Workbooks("\\ukyorw09\HR\IAC New Starter Tracking\IAC\Application\Tracker.xls").close SaveChanges:=False
Application.Displayalerts = True
Application.Screenupdating = True
Application.DisplayFormulaBar = False
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Drawing").Visible = False

Run "FormatSheet"

End Sub

You do, of course, realise that you now "owe me one" ???

thomas.szwed
12-19-2007, 07:41 AM
I would owe you one......if it worked! It still comes up with the password box for 'Tracker'. Here is the order of events.

1. Open 'dalston'
2. Update links box appears, click update.....
3. Asks for trackers password....
4. Then Error 9 Subscript out or range on tracker.close bit in the code....

So its not updating the links or entering the tracker password automatically....? Any ideas?

I cant go to anyone else because no-one knows. G Forrest works on the budget side of things not technical......Im not embarrased.....im a student! I just want to deliver before Xmas!!! Ta

unmarkedhelicopter
12-19-2007, 07:56 AM
These people know people and if you are a student you should be less adversarial and more open to suggestions, G Forrest was not the only name I gave you.
If I were you (well "I'd" solve the problem ... but ... ) I'd knock two sample workbooks together that both reside on C:\, with your code, protections and the link formula in place, strip everything else out that does not create your needed situation, then zip the files and post them.
Then people could download them and go straight to it without having to go through all this and have to assemble it themselves, help others to help you (it's a good tip for life).
With the problem simplified like this you may even solve it yourself

thomas.szwed
12-19-2007, 08:55 AM
Here you go then........see for yourself. Change the paths to whereever you save them and as i described before.....

It sill prompts u to press update links and enter password of master sheet in. Then ends in error 9.

Thanks!!!!!!!!!!!!!!!!!!!!!!!!!

I have simplified the workbook open command down to just thia


Private Sub Workbook_Open()

Application.Workbooks.Open "C:\Documents and Settings\UKSzwedTh\Desktop\New Folder\Master.xls", True, False, , "password", "password"

Application.Wait Now() + TimeSerial(0, 0, 5)

Workbooks("C:\Documents and Settings\UKSzwedTh\Desktop\New Folder\Master.xls").Close SaveChanges:=False

End Sub


So here the master is the master sheet and reader is the one thats read data from it

unmarkedhelicopter
12-19-2007, 10:37 AM
Your problem is that you say you want to keep your Master data secure and put it in a password protected file. A normal file can be silently interogated and values returned without informing the user.
Another way is to have a hidden sheet with that data.
I assume you need to update this master centrally and thus can not go with any of the above.
The ONLY way I see of doing this (without using a central mdb file :) ) is to open your dependant sheet (wait till fully open and calculated) then open your master sheet (silently, but it still will show on the taskbar !), wait for sheet to calculate, update the data and then close the Master,
I've put 2 seconds in for both these timers, you 'may' want to extend/reduce or eliminate them depending on how busy your books are.

The workbook opens, writes garbage to some cells then updates the cells from master (the master cells have been re-written to change on each loading to show the effect), you can eliminate the garbage and hone the cells as you need them, unless you want me to write it ALL for you ?

unmarkedhelicopter
12-19-2007, 11:12 AM
If you just write protected the sheet that would be easier (to defeat too of course). :)

thomas.szwed
12-20-2007, 06:59 AM
This is the biggest pile of crap i think i have ever seen..........I cant change my project so late UMH - i just need code to open up the workbook so it can update the links automatically. I simply dont have the time to start doing whatever it is you are suggesting. Could you not just run my recent post and look at the problem from there?

XLD can you help at all with this???

Thanksss

rory
12-20-2007, 07:27 AM
Change the stratup prompt option in the linked workbook (not the Master) under Edit->Links to not prompt and not update. Then just use something like this:
Private Sub Workbook_Open()
Dim wbkSource As Workbook
Application.ScreenUpdating = False
Set wbkSource = Workbooks.Open(Filename:="C:\Master.xls", Password:="blah")
wbkSource.Close False
Application.ScreenUpdating = True
End Sub

unmarkedhelicopter
12-20-2007, 09:49 AM
This is the biggest pile of crap i think i have ever seen..........I cant change my project so late UMH - i just need code to open up the workbook so it can update the links automatically. I simply dont have the time to start doing whatever it is you are suggesting. Could you not just run my recent post and look at the problem from there?

XLD can you help at all with this???

Thanksss
Yep it's crap, but then you specified where to start from and where to get to. I wouln't have this as part of ANY solution if I had the choice. You blame anybody but yourself. Virtually EVERYONE who has commented on this thread has said "I wouldn't do that" but you are stuck with a crap solution cos you started with a crap premise.

I think I've got you pinned now and I reckon I'll pay you a visit next time I'm in York :devil2:

unmarkedhelicopter
12-20-2007, 09:50 AM
Change the stratup prompt option in the linked workbook (not the Master) under Edit->Links to not prompt and not update. Then just use something like this:
Private Sub Workbook_Open()
Dim wbkSource As Workbook
Application.ScreenUpdating = False
Set wbkSource = Workbooks.Open(Filename:="C:\Master.xls", Password:="blah")
wbkSource.Close False
Application.ScreenUpdating = True
End Sub
nice :)