Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypassing password protection in Visual Basic
I'm programming a tool for work that is comprised of several different
spreadsheets. There are about 15 individual spreadsheets on a networked drive and a master spreadsheet that takes and compiles data from the other 15. The issue I'm running into is that each of the 15 individual spreadsheets is password protected, so that only the owner of that spreadsheet can access it. I've created a macro on the master spreadsheet that will retreive the data from each of the individual spreadsheets, but each time it attempts to pull data, it asks for the password. Is there a way to bypass the password in Visual Basic so that the master spreadsheet can compile the data from each of the individual spreadsheets without it asking for a password? Any help or suggestions would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypassing password protection in Visual Basic
Unprotect the sheet in VBA before working on it, the protect it afterwards
HTH Bob "Matt" wrote in message ... I'm programming a tool for work that is comprised of several different spreadsheets. There are about 15 individual spreadsheets on a networked drive and a master spreadsheet that takes and compiles data from the other 15. The issue I'm running into is that each of the 15 individual spreadsheets is password protected, so that only the owner of that spreadsheet can access it. I've created a macro on the master spreadsheet that will retreive the data from each of the individual spreadsheets, but each time it attempts to pull data, it asks for the password. Is there a way to bypass the password in Visual Basic so that the master spreadsheet can compile the data from each of the individual spreadsheets without it asking for a password? Any help or suggestions would be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypassing password protection in Visual Basic
To clarify, I'm talking about the password to open the worksheet. I'm NOT
talking about the password used to protect formatting (Tools - Protection). If you're referring to the same password, I'm not sure how to disable it in VB as you described. If there's a way to do it, that's what I need help with... or any alternative suggestions be great as well. "Bob Phillips" wrote: Unprotect the sheet in VBA before working on it, the protect it afterwards HTH Bob "Matt" wrote in message ... I'm programming a tool for work that is comprised of several different spreadsheets. There are about 15 individual spreadsheets on a networked drive and a master spreadsheet that takes and compiles data from the other 15. The issue I'm running into is that each of the 15 individual spreadsheets is password protected, so that only the owner of that spreadsheet can access it. I've created a macro on the master spreadsheet that will retreive the data from each of the individual spreadsheets, but each time it attempts to pull data, it asks for the password. Is there a way to bypass the password in Visual Basic so that the master spreadsheet can compile the data from each of the individual spreadsheets without it asking for a password? Any help or suggestions would be appreciated. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypassing password protection in Visual Basic
Hi Matt,
Perhaps we need to clarify what you refer to as a worksheet. A worksheet is one of the tabs (worksheets) within a workbook containing a collection of worksheets. A workbook is actually the file that is saved and you open. I suspect that you mean a workbook; not a worksheet. If the above is correct, do you have the passwords for the individual workbooks? If not, you need to get them. The following example code opens workbooks with passwords. Sub OpenWithPassword() Dim strpath As String Dim strPathNfile As String Dim wb As Workbook 'Options for setting path strpath = ThisWorkbook.Path & "\" 'strpath = "C:\OssieMac\Documents\Excel\" 'Variable with full path and filename strPathNfile = strpath & "Test workbook with password.xls" 'Open the workbook with password Workbooks.Open Filename:=strPathNfile, _ Password:="mypassword" 'Open the workbook including WriteResPassword 'Workbooks.Open Filename:=strPathNfile, _ Password:="mypassword", WriteResPassword:="mypassword" Set wb = ActiveWorkbook End Sub Following from a post by Tom Ogilvy MVP Note that there are two types of password arguments: 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. -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypassing password protection in Visual Basic
Matt -
I don't have an answer to your questions, but I'm following this thread to learn about the issues. It would help me understand if you would avoid the word "spreadsheets." An Excel workbook file contains one or more worksheets. When you write "15 individual spreadsheets," it's not clear to me whether you are referring to 15 workbook files or to one workbook file containing 15 worksheets. In Excel pre-2007 the Tools menu has a Protection item with two or more sub-items: Protect Sheet and Protect Workbook, each with an optional password. - Mike http://www.MikeMiddleton.com "Matt" wrote in message ... To clarify, I'm talking about the password to open the worksheet. I'm NOT talking about the password used to protect formatting (Tools - Protection). If you're referring to the same password, I'm not sure how to disable it in VB as you described. If there's a way to do it, that's what I need help with... or any alternative suggestions be great as well. "Bob Phillips" wrote: Unprotect the sheet in VBA before working on it, the protect it afterwards HTH Bob "Matt" wrote in message ... I'm programming a tool for work that is comprised of several different spreadsheets. There are about 15 individual spreadsheets on a networked drive and a master spreadsheet that takes and compiles data from the other 15. The issue I'm running into is that each of the 15 individual spreadsheets is password protected, so that only the owner of that spreadsheet can access it. I've created a macro on the master spreadsheet that will retreive the data from each of the individual spreadsheets, but each time it attempts to pull data, it asks for the password. Is there a way to bypass the password in Visual Basic so that the master spreadsheet can compile the data from each of the individual spreadsheets without it asking for a password? Any help or suggestions would be appreciated. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypassing password protection in Visual Basic
Exactly how are you getting the data from the files ?
Tim "Matt" wrote in message ... I'm programming a tool for work that is comprised of several different spreadsheets. There are about 15 individual spreadsheets on a networked drive and a master spreadsheet that takes and compiles data from the other 15. The issue I'm running into is that each of the 15 individual spreadsheets is password protected, so that only the owner of that spreadsheet can access it. I've created a macro on the master spreadsheet that will retreive the data from each of the individual spreadsheets, but each time it attempts to pull data, it asks for the password. Is there a way to bypass the password in Visual Basic so that the master spreadsheet can compile the data from each of the individual spreadsheets without it asking for a password? Any help or suggestions would be appreciated. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypassing password protection in Visual Basic
Yes, I mean workbooks. Thank you.
Each of the individual workbooks belongs to an employee that would use their own password to prevent anyone else from accessing them on a public networked drive. Because the employees are at liberty to create their own passwords and change them, the workbook that compiles the data would need to be able to access data from these other workbooks without having the password. "OssieMac" wrote: Hi Matt, Perhaps we need to clarify what you refer to as a worksheet. A worksheet is one of the tabs (worksheets) within a workbook containing a collection of worksheets. A workbook is actually the file that is saved and you open. I suspect that you mean a workbook; not a worksheet. If the above is correct, do you have the passwords for the individual workbooks? If not, you need to get them. The following example code opens workbooks with passwords. Sub OpenWithPassword() Dim strpath As String Dim strPathNfile As String Dim wb As Workbook 'Options for setting path strpath = ThisWorkbook.Path & "\" 'strpath = "C:\OssieMac\Documents\Excel\" 'Variable with full path and filename strPathNfile = strpath & "Test workbook with password.xls" 'Open the workbook with password Workbooks.Open Filename:=strPathNfile, _ Password:="mypassword" 'Open the workbook including WriteResPassword 'Workbooks.Open Filename:=strPathNfile, _ Password:="mypassword", WriteResPassword:="mypassword" Set wb = ActiveWorkbook End Sub Following from a post by Tom Ogilvy MVP Note that there are two types of password arguments: 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. -- Regards, OssieMac |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypassing password protection in Visual Basic
Yes, I meant workbooks - there are 15 (or so) individual files that need to
be accessed by an independent workbook - more of a supervisory tool used for compliling the data in the other workbooks. "Mike Middleton" wrote: Matt - I don't have an answer to your questions, but I'm following this thread to learn about the issues. It would help me understand if you would avoid the word "spreadsheets." An Excel workbook file contains one or more worksheets. When you write "15 individual spreadsheets," it's not clear to me whether you are referring to 15 workbook files or to one workbook file containing 15 worksheets. In Excel pre-2007 the Tools menu has a Protection item with two or more sub-items: Protect Sheet and Protect Workbook, each with an optional password. - Mike http://www.MikeMiddleton.com "Matt" wrote in message ... To clarify, I'm talking about the password to open the worksheet. I'm NOT talking about the password used to protect formatting (Tools - Protection). If you're referring to the same password, I'm not sure how to disable it in VB as you described. If there's a way to do it, that's what I need help with... or any alternative suggestions be great as well. "Bob Phillips" wrote: Unprotect the sheet in VBA before working on it, the protect it afterwards HTH Bob "Matt" wrote in message ... I'm programming a tool for work that is comprised of several different spreadsheets. There are about 15 individual spreadsheets on a networked drive and a master spreadsheet that takes and compiles data from the other 15. The issue I'm running into is that each of the 15 individual spreadsheets is password protected, so that only the owner of that spreadsheet can access it. I've created a macro on the master spreadsheet that will retreive the data from each of the individual spreadsheets, but each time it attempts to pull data, it asks for the password. Is there a way to bypass the password in Visual Basic so that the master spreadsheet can compile the data from each of the individual spreadsheets without it asking for a password? Any help or suggestions would be appreciated. . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypassing password protection in Visual Basic
I'm using a set of macros that open the file using the code:
Workbooks.Open Filename:="[FileName.xls]" And then copying over data from the file to the master workbook. When the individual workbooks that I'm attempting to gather data from are password protected, it forces whoever uses the master workbook to have all of the passwords. "Tim Williams" wrote: Exactly how are you getting the data from the files ? Tim "Matt" wrote in message ... I'm programming a tool for work that is comprised of several different spreadsheets. There are about 15 individual spreadsheets on a networked drive and a master spreadsheet that takes and compiles data from the other 15. The issue I'm running into is that each of the 15 individual spreadsheets is password protected, so that only the owner of that spreadsheet can access it. I've created a macro on the master spreadsheet that will retreive the data from each of the individual spreadsheets, but each time it attempts to pull data, it asks for the password. Is there a way to bypass the password in Visual Basic so that the master spreadsheet can compile the data from each of the individual spreadsheets without it asking for a password? Any help or suggestions would be appreciated. . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bypassing password protection in Visual Basic
Hi Matt,
If it were that easy to bypass the password then there is little point in having them. Having said that I know that hackers can get past them without too much trouble. However, the way to achieve your aim is to have your network administrator create a directory for which you have full read/write permissions to it and any sub directories/files. The administrator then creates individual directories for each of the users to which they have inidvidual access and cannot see each others files. There should then be no need for them to have passwords on the excel file because they should only be able to see their own file. This type of system has been extensively used for a long time. -- Regards, OssieMac "Matt" wrote: Yes, I mean workbooks. Thank you. Each of the individual workbooks belongs to an employee that would use their own password to prevent anyone else from accessing them on a public networked drive. Because the employees are at liberty to create their own passwords and change them, the workbook that compiles the data would need to be able to access data from these other workbooks without having the password. "OssieMac" wrote: Hi Matt, Perhaps we need to clarify what you refer to as a worksheet. A worksheet is one of the tabs (worksheets) within a workbook containing a collection of worksheets. A workbook is actually the file that is saved and you open. I suspect that you mean a workbook; not a worksheet. If the above is correct, do you have the passwords for the individual workbooks? If not, you need to get them. The following example code opens workbooks with passwords. Sub OpenWithPassword() Dim strpath As String Dim strPathNfile As String Dim wb As Workbook 'Options for setting path strpath = ThisWorkbook.Path & "\" 'strpath = "C:\OssieMac\Documents\Excel\" 'Variable with full path and filename strPathNfile = strpath & "Test workbook with password.xls" 'Open the workbook with password Workbooks.Open Filename:=strPathNfile, _ Password:="mypassword" 'Open the workbook including WriteResPassword 'Workbooks.Open Filename:=strPathNfile, _ Password:="mypassword", WriteResPassword:="mypassword" Set wb = ActiveWorkbook End Sub Following from a post by Tom Ogilvy MVP Note that there are two types of password arguments: 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. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Protection and Delete Modules | Excel Discussion (Misc queries) | |||
Password in Visual basic editor | Excel Worksheet Functions | |||
Visual Basic Macro(VBA) for a password protected file? | Excel Programming | |||
Error Trap for bypassing Password Protection | Excel Programming | |||
Password in Visual Basic - Help! | Excel Programming |