Function or Sub?

[Leslie Isaacs]

Hello all

This is actually a continuation of a previous thread (subject "Transfer
text?") that I started on 12 Oct, but which has 'gone cold'. Essentially, I
need to export the results of a query to a password-protected excel
spreadsheet. One suggested solution was to use the
'ExportToPasswordedWorkbook' code below, so now I have that code in a
module, and I have

ExportToPasswordedWorkbook("C:\Documents and Settings\Administrator\My
Documents\nhsp_Joiner.xls","P0y3D0c","Query1")

as the OnClick event of a button, but when I click the button 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?

I should add that I have a reference set to Microsoft Excel 9 Object
Library.

Hope someone can help.
Many thanks
Leslie Isaacs

The Sub code is:

Option Compare Database

Public Sub ExportToPasswordedWorkbook(strFile As String, _
strPassword As String, strQryName As String)

Dim oExcel As Excel.Application
Dim oWb As Excel.Workbook

Set oExcel = CreateObject("Excel.Application")

'Open protected workbook and remove password
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
password:=strPassword)
oWb.password = ""
oWb.Close SaveChanges:=True

'Export
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, strQryName, strFile, -1

'Restore password
Set oWb = oExcel.Workbooks.Open(FileName:=strFile)
oWb.password = strPassword
oWb.Close SaveChanges:=True
oExcel.Quit
Set oExcel = Nothing

End Sub

 

[Stefan Hoffmann]

hi Leslie,

ExportToPasswordedWorkbook("C:\Documents and Settings\Administrator\My
Documents\nhsp_Joiner.xls","P0y3D0c","Query1")

as the OnClick event of a button, but when I click the button this results
in the compile error "Expected: ="

Is this the entire error message?

The Sub code is:

Option Compare Database

Here is the most important

Option Explicit

missing.


mfG
--> stefan <--

 

[Leslie Isaacs]

Hello Stefan

Many thanks for your reply.
I have added the Option Explicit line to the module, but still get the same
error!!

Can you see anything else wrong?

Thanks again
Les

 

[Stefan Hoffmann]

hi Leslie,

This question remains unanswered. At which point does the debugger stops?


mfG
--> stefan <--

 

[PayeDoc]

Sorry Stephan - didn't see that question!

The code stops on the line:
ExportToPasswordedWorkbook("C:\Documents and Settings\Administrator\My
Documents\nhsp_Joiner.xls","P0y3D0c","Query1")

with the error message:
compile error "Expected: ="

Hope that helps.
Thanks again
Les

 

[AG]

You should be using

Call ExportToPasswordedWorkbook("C:\Documents and Settings\Administrator\My
Documents\nhsp_Joiner.xls","P0y3D0c","Query1")

or just

ExportToPasswordedWorkbook "C:\Documents and Settings\Administrator\My
Documents\nhsp_Joiner.xls","P0y3D0c","Query1"

 

[Klatuu]

Try coding it this way:
Call ExportToPasswordedWorkbook "C:\Documents and Settings\Administrator\My
Documents\nhsp_Joiner.xls","P0y3D0c","Query1"

 

[Leslie Isaacs]

Hello Dave

OK - we're getting somewhere: adding "Call" to the code as you and "AG"
suggested solves the compile error.

The problem now is that the user is asked for the password when they fire
the code! I had thought that as the password is hard-coded as an argument,
this wouldn't happen.

Any thoughts?

Thanks as ever
Les

 

[Leslie Isaacs]

Hello "AG"

Many thanks for your suggestions.

I think your 2nd suggestion is identcal to what I had been trying ... but
your 1st suggestion - also suggested by "Klatuu" fixes the compile error:
but now there is another problem - please see my reply to "Klatuu".

Thanks again for your help.
Les

 

[AG]

Leslie,

Either would work.
The difference between my second suggestion and what you already had is the
parens.