Export to Excel w/password

[rhicks]

I am using the Transfer Spreadsheet method in Access to export the results of a query to an existing Excel sheetsheet. I sure would like to Password protect the Excel workbook to keep others from tampering with it. The problem is I can't figure out how to pass the password through the automation when I use the Transfer Spreadsheet Method. Life would be much better if someone could point me in the right direction.<br>
<br>
Thank in advance<br>
RDH<br>
<p>Ricky Hicks<br><a href=mailto: rdhicks@mindspring.com> rdhicks@mindspring.com</a><br><a href= > </a><br>

 

[DougP]

One way would be<br>
use a Input box before the Transfer....statement<br>
It's a little crude but will work.<br>
OR <br>
If your Excel spreadsheet is on and NT server, on an NTFS partition you can assign it permissions.<br>
<br>
<br>
Crude way:<br>
<br>
retval = InputBox(&quot;Enter Password&quot;, &quot;Password Required&quot;, &quot;&quot<br>
If retval = MyPassword Then<br>
docmd.TransferSpreadsheet blah blah blah<br>
Else<br>
'Exit Sub or function<br>
End If<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[rhicks]

Thanks for the response Doug, but the problem is not how to enter the password, but how to pass the password within the Docmd.TransferSpreadsheet method. Here is an example of the code line:<br>
<br>
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, &quot;qry_Surface_K1&quot;, &quot;C:\KeyChk24\K1-Srfce.xls&quot; (all on one line)<br>
<br>
This works as long as the .xls file doesn't have a password. If it had a password how would you add it to the code above? I've tried PWD=&quot;XXXX&quot; along with every other trick I know.<br>
<br>
Thank for all efforts<br>
Ricky <p>Ricky Hicks<br><a href=mailto: rdhicks@mindspring.com> rdhicks@mindspring.com</a><br><a href= > </a><br>

 

[DougP]

So what you are saying is that you want to open a password protected spreadsheet without the bother of stoping to key in a password.<br>
Or putting it another way if someone who normally does not know the password could Open Access, click your button and launch Excel which would pass the password to Excel and open a otherwise secure document. Not a good idea.<br>
Maybe thats why there is no such paramter in the first place.<br>
So Just launch Excel and It will ask for a password and if you know it your in. If not...<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[JimConrad]

You need to use Automation to have Excel do the work for you:<br>
<br>
Attached is a function which can be loaded into a module and called from any point in your code. You probably will also need a function to reverse the process, but I'll leave this to you! <br>
<br>
Public Function ExcelUnprotect(strFilename, strPassword) As Long<br>
<br>
On Error GoTo ExcelUnprotect_Error<br>
<br>
ExcelUnprotect = 0<br>
<br>
Dim xlApp As Excel.Application<br>
Dim MyWorkbook As Workbook<br>
<br>
Set xlApp = CreateObject(&quot;Excel.Application&quot<br>
Set MyWorkbook = xlApp.workbooks.Open(strFilename, , , , strPassword, , xlLocalSessionChanges)<br>
<br>
xlApp.SendKeys &quot;Y{Enter}&quot;<br>
MyWorkbook.SaveAs , , &quot;&quot;<br>
<br>
ExcelUnprotect_Exit:<br>
<br>
On Error Resume Next<br>
<br>
Set MyWorkbook = Nothing<br>
xlApp.Quit<br>
Set xlApp = Nothing<br>
<br>
Exit Function<br>
<br>
ExcelUnprotect_Error:<br>
<br>
ExcelUnprotect = Err.Number<br>
<br>
Resume ExcelUnprotect_Exit<br>
<br>
End Function<br>
<br>
<p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>

 

[rhicks]

Thanks for the replies. I finally broke down and called Microsoft. After an hour with all the top brains there, they all came to the conclusion that you can't pass the password as an argument using Docmd.Transfer SpreadSheet method. I thought maybe I would hit the Jackpot but ended up wasting one of my lifelines. I guess I am going to have to try another direction.<br>
<br>
&quot;Left scratching my head&quot;<br>
RDH<br>
<p>Ricky Hicks<br><a href=mailto: rdhicks@mindspring.com> rdhicks@mindspring.com</a><br><a href= > </a><br>