Transfer text?

P

PayeDoc

Hello All

I need to output the results of a query as an excel file (which then needs
to be emailed) - but the excel file must be password-protected. So far I'm
doing this fairly manually but I really do need to automate it. Obviously
saving the data to a spreasheet is simple enough, but I have been unable to
find a way of using code to save the spreadsheet with a password, so I'm now
looking at using macro actions TransferText or TransferSpreadsheet, but
still can't get it working.

The good news is that the password will always be the same (!) so could be
hard-coded, and there will always be just one record, and a fixed number of
fields (21 of them) and fixed field names, so the spreadsheet will always
have the same column headers and then just one data row. I have created a
'template' spreadsheet with the password set, and was hoping to be able to
'transfer' the query results into it, but either the whole dataset ends up
in cell A1, or I get one of a number of not-very-helpful messages (e.g.
"Could not decrypt file").

I've probably made heavy weather of this, but if someone could help me along
that would be great!!

Many thanks
Leslie Isaacs
 
P

PayeDoc

"Mr. B"

Many thanks for your quick response.
I'm in the other office now, but will be back in the morning and will
certainly try this method then: it looks promising!!

Thanks again
Les
 
R

ryguy7272

Ok, this isn't difficult. Do you have everything transferring to the
spreadhseet ok? If not, just set up a simple macro to do that. Record the
macro, save, convert to VBA, link to a Button on a Form. Now, run this code:

Make sure you set a reference to Excel, and then run this code in an Access
module:

Option Compare Database
Option Explicit ' Use this to make sure your variables are defined
' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub

Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Sub Rep()
Dim strFile As String
strFile = "C:\Documents and Settings\rshuell\Desktop\Ryan\Crosstab Query.xls"
‘Of course, this is just an example; put the actual path to your actual file
here…
' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True
' Opens up a Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)
' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.
Set xlWS = xlWB.ActiveSheet
' Set xlWS = xlWB("Sheet1")
With xlWS ' You are now working with the Named file and the named worksheet
' Your Excel code begins here…you can even record a macro to add security to
your sheet--super easy!!
End With

' Close and Cleanup
xlWB.SaveAs xlSaveFile
xlWB.Close
xlapp.Quit
Set xlapp = Nothing

End Sub

Here is some Excel VBA to add security to your sheet:
Sub ApplySecurity()
Worksheets("Sheet1").Protect Password:="1234"
End Sub

HTH,
Ryan--
 
P

PayeDoc

Hello "Mr. B"

OK - I'm almost there (I think!).

I pasted the code at the reference into a 'utilities' module that I have,
compiled it (was OK), then created a new button on a form and set the
onClick event to:

ExportToPasswordedWorkbook("C:\Documents and Settings\Administrator\My
Documents\nhsp_Joiner.xls","12345","qry NHSP leaver")

.... but this results in the compile error "Expected: ="

Does this mean that I have to specify some variable for the function's
output ... but then this isn't a 'function', it's a 'sub', which I thought I
understood to mean that it will simply perform an action (like setting an
object's property - or in this case exporting the query's output to the
spreadsheet) as opposed to computing a value that would need to be assigned
to a variable. Have I got this wrong?

As you can see my knowledge of these things is shaky: but I have a feeling
(hope?) that light will dawn following your next response!

Thanks again for your help.
Les
 
P

PayeDoc

Hello Ryan

Many thanks for your response. I think I may be almost there (as with Mr B's
suggestion!) - but not quite.

I have pasted your main code into my 'utilities' module (and put the 3
private declarations at the top), and then I added the VBA to add security
to the sheet, but it didn't compile. I then commented out the 'Sub
ApplySecurity()' and 'End Sub' lines from the VBA to add security part
(realised my gaff!), recompiled, but then got a "variable not defined" on
the line
xlWB.SaveAs xlSaveFile
I therefore replaced this with
xlWB.Save
- assuming that this would save the excel file with its existing name
(nhsp_Joiner.xls, as specified in the initial declaration of strFile). On
recompiling I got another "variable not defined" error, this time on
xlApp.Quit
.... so now I'm stuck!!

In case it helps, I have a reference set to Microsoft Excel 8.0 Object
library.
I would also add that I have used the line
Set xlWS = xlWB("Sheet1")
as suggested - it's fine for the data to go to 'Sheet1'.
Apart from resolving the compile error, I also need the entire workbook to
be passworded - not just "Sheet1": how do I amend the code to do that?

The code I now have - after making the above amendments - is below.
Final question: when you say "run this code in an Access module", does that
mean appending it to the code that currently adds the query's output to the
un-passworded excel file?

Sorry if my questions seem stupid - but I am learning!

Many thanks for your continued help.
Les

My code so far:
Sub Rep()
Dim strFile As String
strFile = "C:\Documents and Settings\Administrator\My
Documents\nhsp_Joiner.xls"
'Of course, this is just an example; put the actual path to your actual file
here.
' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True
' Opens up a Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)
' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.
'Set xlWS = xlWB.ActiveSheet
Set xlWS = xlWB("Sheet1")
With xlWS ' You are now working with the Named file and the named worksheet
' Your Excel code begins here.you can even record a macro to add security to
your sheet--super easy!!
'Sub ApplySecurity()
Worksheets("Sheet1").Protect password:="1234"
'End Sub
End With
' Close and Cleanup
'xlWB.SaveAs xlSaveFile
xlWB.Save
xlWB.Close
xlApp.Quit
Set xlApp = Nothing
End Sub
 
R

ryguy7272

The code looks good; not sure what's causing the error. If you send it to me
tonight I'll take a look.
Ryan--
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with Macro Security 1
Excel does not accept text format! 0
How to clear prior search terms from Navigation Pane 0
Setting language of a selected text in Office apps. 0
Merging from an Excel file with graphics 1
Autonumber field in Excel that behaves the same as Access 0
Re-title multiple MS Project .mpp files at once 0
Excel - Wich Function should I use? 1

Top