export access to excel with password

[Doug F.]

I want to export data to a password protected spreadsheet.
I found some code and this is where I am so far.
Am I close or wrong direction? Thanks.

Dim oExcel As Object, oWb As Object
Set oExcel = CreateObject("Excel.Application")
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, Password:=strPassword)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "myQry",
strFile, -1
oWb.Close SaveChanges:=False
oExcel.Quit
Set oExcel = Nothing

 

[Ken Snell \(MVP\)]

Does it work?

 

[Doug F.]

If the xls exists with password then it seems to hang but then I see a prompt
dialog box for the password after I End task Access.

If the xls doesn't exist then I get Run Time Error 13 Type Mismatch.

Doug

 

[Ken Snell \(MVP\)]

I've not had an occasion to export to a password protected file, but likely
what you'll need to do in your VBA code is this:

1) Open the file with the password.
2) Remove the password from the file.
3) Save and close the file.
4) Do the export to the file.
5) Open the file.
6) Put the password back on the file.
7) Save and close the file.

 

[Doug F.]

Ken, I think I need the Excel Automation commands to remove & add a password
from & to a workbook. I've burned a few cycles but can't find this. How do I
find these? Thanks.

 

[Ken Snell \(MVP\)]

This is from EXCEL 2003 Help File:


Password Property
See Also Applies To Example
Returns or sets the password that must be supplied to open the specified
workbook. Read/write String.

Use strong passwords that combine upper- and lowercase letters, numbers, and
symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5.
Weak password: House27. Use a strong password that you can remember so that
you don't have to write it down.


expression.Password

expression Required. An expression that returns one of the objects in the
Applies To list.

Example
In this example, Microsoft Excel opens a workbook named Password.xls, sets a
password for it, and then closes the workbook. This example assumes a file
named "Password.xls" exists on the C:\ drive.

Sub UsePassword()

Dim wkbOne As Workbook

Set wkbOne = Application.Workbooks.Open("C:\Password.xls")

wkbOne.Password = InputBox ("Enter Password")
wkbOne.Close

End Sub
Note The Password property is readable and returns ">>**".

 

[Doug F.]

Hi Ken,
I couldn't find Excel Help for 'password property'?

Using: Dim wkBOne as Workbook
This line wouldn't compile, User-Name not defined.

So, this almost works, however, I still get a prompt to Save the change to
Excel
but doesn't the Close line do this?

Dim oExcel As Object, oWb As Object
Set oExcel = CreateObject("Excel.Application")
Set oWb = oExcel.Workbooks.Open(FileName:=strFile)
oWb.Password = strPassword
oWb.Close SaveChanges:=True
oExcel.Quit
Set oExcel = Nothing

Also, I added Excel 11.0 Objects as References, needed?

Thanks for your noodling,
Doug

 

[Ken Snell \(MVP\)]

comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
I couldn't find Excel Help for 'password property'?

I posted all the text from EXCEL's Help regarding this property.

Using: Dim wkBOne as Workbook
This line wouldn't compile, User-Name not defined.

The above line won't compile unless you have a Reference set to the EXCEL
VBA Library.

So, this almost works, however, I still get a prompt to Save the change to
Excel
but doesn't the Close line do this?

Dim oExcel As Object, oWb As Object
Set oExcel = CreateObject("Excel.Application")
Set oWb = oExcel.Workbooks.Open(FileName:=strFile)
oWb.Password = strPassword
oWb.Close SaveChanges:=True
oExcel.Quit
Set oExcel = Nothing

Above code appears correct to me for closing the file, and you shouldn't get
a confirmation box asking if you want to save the changes.

Does strFile contain the full path and the file name? If not, add the path
to the string variable too.

I do note that you need one more line so that you won't leave EXCEL running
in memory. Change the last three lines of code to these four lines:

oWb.Close SaveChanges:=True
Set oWb = Nothing
oExcel.Quit
Set oExcel = Nothing

Also, I added Excel 11.0 Objects as References, needed?

Not needed for the above code because you're using Late Binding. The
CreateObject function will handle the references for you.