Exporting Access query to Excel file with passwords

  • Thread starter Thread starter jv
  • Start date Start date
J

jv

Hello,

I need to export the results of various queries to Excel. During the
export I would like to assign a read-only password and modify
password. I was using macros to export before and I am somewhat new
to VBA.
Please let me know of any additional needed information.

Thank you for the help in advance.
John
 
m:
I need to export the results of various queries to Excel. During
the export I would like to assign a read-only password and modify
password. I was using macros to export before and I am somewhat
new to VBA.
Click to expand...

You can't do it with TransferSpreadsheet (which is the only thing
you can use in a macro). You'll have to use VBA code to automate
Excel, open the file you've saved with Excel and apply the password.
You'll have to have some way of collecting the password from the
user as part of this process.

Let me say that Excel passwords are even weaker than Access database
passwords, so pretty much useless as anything other than "security
theater."
 
Hello,

I need to export the results of various queries to Excel. During the
export I would like to assign a read-only password and modify
password.  I was using macros to export before and I am somewhat new
to VBA.
Please let me know of any additional needed information.

Thank you for the help in advance.
John
Click to expand...

This is a shot in the dark. If you can record a macro in Excel to
apply the read-only and modify passwords, you can use the same VBA in
Access (with a reference to the Excel Object), except that you need to
make sure that the reference is like a fully qualified path rather
than like a relative path. E.g.,

The VBA Macro code to hide a row is:

Selection.EntireRow.Hidden = True

The VBA in Access required to perform the same operation becomes:

objXL.Selection.EntireRow.Hidden = True

where objXL was created with something like (for early binding):

Set objXL = CreateObject("Excel.Application")

and is followed by something like:

Set wbk = objXL.Workbooks.Open(strFileName)
objXL.Visible = True

where wbk is an Excel.Workbook object (when using Early Binding).

When I recorded a macro in A97 I got:

ActiveWorkbook.SaveAs FileName:= _
"C:\Documents and Settings\jimfortune\My Documents\Book2.xls",
FileFormat:= _
xlNormal, Password:="test1", WriteResPassword:="test2",
ReadOnlyRecommended _
:=False, CreateBackup:=False

so I suspect the equivalent VBA code in Access will look something
like (all one line):

objXL.ActiveWorkbook.SaveAs "C:\Documents and Settings\jimfortune\My
Documents\Book2.xls", -4143, "test1", "test2", False, False

Personally, I would do all this after transferring the data into the
spreadsheet :-).

James A. Fortune
(e-mail address removed)

I remember thinking once, "Someday I'll have a 286 of my own, and then
won't life be grand." :-)
 
Let me say that Excel passwords are even weaker than Access database
passwords, so pretty much useless as anything other than "security
theater."
Click to expand...

I'm wondering if the passwords in XLSX files would be much better as
those are really zip files. And the password might be AES or such?

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
 
I'm wondering if the passwords in XLSX files would be much better as
those are really zip files.   And the password might be AES or such?  

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
  updated seehttp://www.autofeupdater.com/
Click to expand...

I agree with David's "security theater" comment. That is, if you're
talking about just the password being encrypted. All Excel
spreadsheets are insecure for the much the same reason that all PDF
files are insecure (I'm not going to spell it out). If you're going
to encrypt the entire document, then it really doesn't matter what
version of Access/Excel/etc. you're trying to protect. Perhaps the
real point is that with zip files, for most people, encryption of the
entire file is easier and safer than encryption of files in general.

James A. Fortune
(e-mail address removed)

The best nuclear launching protocol can't defeat a technician armed
with a wire with alligator clips on each end. You need physical
security too. -- Len Brown, Oakland U. School of Engineering
Electronics Shop Manager
 
I'm wondering if the passwords in XLSX files would be much better
as those are really zip files. And the password might be AES or
such?
Click to expand...

I would assume that the Excel 2007 passwords got the same encryption
upgrade as did the Access 2007 database password, but I have no real
basis for that assumption other than the idea that it would be
logical to take advantage of the better encryption across the whole
Office suite.
 
Back
Top