export access to excel with password

  • Thread starter Thread starter Doug F.
  • Start date Start date
D

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
 
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
 
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.
 
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.
 
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 ">>**".
 
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
 
comments inline...

--

Ken Snell
<MS ACCESS MVP>


Doug F. said:
Hi Ken,
I couldn't find Excel Help for 'password property'?
Click to expand...

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.
Click to expand...

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
Click to expand...

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?
Click to expand...

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