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 <[email protected]> > To: MS EXCEL AND VBA MACROS <[email protected]> > 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 [email protected] 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 @ [email protected] -~----------~----~----~----~------~----~------~--~---
