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