To make things clearer, here are some scenarios. Try them by creating
the relevant files and saving as xls and as xlsx versions.
Dim wkb As Excel.Workbook
' This will open normally - also works for "NoPasswords.xlsx"
set wkb = Excel.Workbooks.Open("NoPasswords.xls")
' No problem opening this either - Excel ignores the password
argument
' as the file doesn't require any password - works for
"NoPasswords.xlsx"
set wkb = Excel.Workbooks.Open("NoPasswords.xls", , , , "password")
' This will prompt the user for a password for both versions of the
file. This
' is by design and is why I supply a password argument (see next
scenario)
set wkb = Excel.Workbooks.Open("FileOpenPassword.xls")
' This will fail with error 1004: "The password you supplied is not
correct". This is
' the correct behaviour for both xls and xlsx files. I don't know
what the password
' might be but this allows my code to keep running and just alert
someone to deal
' with the file manually
set wkb = Excel.Workbooks.Open("FileOpenPassword.xls", , , ,
"password")
' This is the case that this post is all about - for the following
situation the xls
' format workbook opens normally as you would expect. With no
FileOpen password
' the password argument is just ignored as it isn't required
set wkb = Excel.Workbooks.Open
("WorkbookProtectionPassword.xls", , , , "password")
' This is the case that this post is all about - for the following
situation the xlsx
' format workbook fails to open with error 1004: "The password you
supplied is not correct"
' which is incorrect behaviour and occurs even if the protection
password is "password"
set wkb = Excel.Workbooks.Open
("WorkbookProtectionPassword.xlsx", , , , "password")
I hope that makes things clearer for people.
Regards,
Wazza
On Jan 28, 1:32 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> Curios.
> Because I saved some files in .xls format and .xlsx format with and without
> passwords.
> When I opened them with my macro, it didn't pop up a panel,
> but DID issue the "Could not open" message.
>
> I'll have to try some more and find out why it's working differently than
> yours.
>
> P
>
> ----- Original Message ----
> > From: Wazza <bainswo...@gmail.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Monday, January 26, 2009 8:48:59 PM
> > Subject: $$Excel-Macros$$ Re: Different behaviour between opening xls and
> > xlsx files
>
> > Paul,
>
> > If you attempt your approach without the 'Password:="password"'
> > parameter AND if the file has an open password then Excel will throw
> > up a dialog box asking for the password, no matter that you have
> > specified Application.DisplayAlerts = False. That is the express
> > reason why I include the password parameter explicitly because if
> > there isn't an open password on the file Excel ignores it and if there
> > is an open password on the file then you will get a 1004 error and can
> > deal with it.
>
> > The situation that appears incorrect to me is that with an xlsx format
> > file, Excel throws an exception if there is a password parameter AND
> > if the file has a workbook protection password (to stop changes to
> > structure and windows). This should NOT happen and doesn't happen
> > with the xls format file.
>
> > Regards,
> > Wazza
>
> > On Jan 23, 1:16 am, Paul Schreiner wrote:
> > > I tested this:
> > > Sub Test()
> > > Application.DisplayAlerts = False
> > > On Error Resume Next
> > > Err.Clear
> > > Set wkb = Excel.Workbooks.Open(Filename:="C:\temp\test.xlsx" _
> > > , ReadOnly:=True, Password:="password")
> > > If (Err.Number > 0) Then
> > > MsgBox "Could not open"
> > > End If
> > > On Error GoTo 0
> > > Application.DisplayAlerts = True
> > > End Sub
>
> > > It seemed to work. You could fire off an email macro instead of the
> > > msgbox.
> > > Curious, though...
> > > I tried it with only:
> > > Set wkb = Excel.Workbooks.Open(Filename:="C:\temp\test.xlsx")
> > > and it worked too!!!
>
> > > Not sure what this means...
>
> > > Paul
>
> > > ----- Original Message ----
> > > > From: Wazza
> > > > To: MS EXCEL AND VBA MACROS
> > > > Sent: Wednesday, January 21, 2009 11:49:13 PM
> > > > Subject: $$Excel-Macros$$ Different behaviour between opening xls and
> > > > xlsx
> > files
>
> > > > I have an application that opens Excel files and loads data into an
> > > > Access database. This app runs unattended and so I have to take
> > > > various precautions to ensure that Excel doesn't produce any prompts
> > > > that require human input.
>
> > > > One such situation is where a user decides to password protect the
> > > > file. If I try to open this in code without the password, Excel will
> > > > pop up a dialog box prompting for the password and everything will
> > > > stop until somebody goes to the server and answers it. To avoid this,
> > > > I supply a password like so:
>
> > > > set wkb = Excel.Workbooks.Open(FileName:= " test.xls ",
> > > > Password:="password")
>
> > > > If the file isn't password protected, Excel just ignores that
> > > > parameter. If it is password protected (and the password isn't
> > > > "password") then I get a 1004 error and can email somebody to fix it
> > > > and then skip to the next file.
>
> > > > This works fine for .xls files but there is a difference with .xlsx
> > > > files. If the file has a workbook protection password set (which it
> > > > does), then the .xlsx format file returns the 1004 error (no matter
> > > > the value of the password, including "password") whereas the
> > > > equivalent file saved in legacy .xls format does not fail. So to
> > > > handle .xlsx files I will need to remove the password parameter and
> > > > risk the task hanging...
>
> > > > Anyone have an idea about this or have I discovered an Excel bug?
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
If you find any spam message in the group, please send an email to Ayush @
jainayus...@gmail.com
-~----------~----~----~----~------~----~------~--~---
