Programming Excel with VBA and .NET

Open as Read-Only or with Passwords

The Open method is actually quite complex. If you type Workbooks.Open in the Code window, Visual Basic displays a dizzying array of possible arguments (Figure 8-1).

Figure 8-1. The Open method can be complex

Thankfully, only Filename is required! Most of these are pretty special-purpose (you can read about them later); the most important ones are ReadOnly, Password, and Format. Opening a file as read-only is handy if a workbook is stored at a network location and might be open by another user—in that case you can open the file only as read-only:

Set wb = Workbooks.Open("//wombat1/public/copy of files.xls", , True)

If you try to open a workbook that has a password in code, Excel will prompt the user for that password. You can avoid this by putting the password in code:

Set wb = Workbooks.Open(ThisWorkbook.Path & "/security.xls", , , , "Excel2003")

Of course, that’s a spectacularly bad idea if you are at all concerned about security: never write passwords, usernames, email addresses, or other sensitive data in code. The only reason to use this approach is if your passwords are merely intended to prevent accidental access—the analogy would be closing your front door rather than locking it, locking it and setting the alarm, or locking it, setting the alarm, and releasing ravening hounds; you get the idea.

Finally, the Format argument lets you open text files ...