Function or Sub?

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

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
 
hi Leslie,

Leslie said:
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: ="
Click to expand...
Is this the entire error message?
The Sub code is:

Option Compare Database
Click to expand...
Here is the most important

Option Explicit

missing.


mfG
--> stefan <--
 
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
 
hi Leslie,

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


mfG
--> stefan <--
 
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
 
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"
 
Try coding it this way:
Call ExportToPasswordedWorkbook "C:\Documents and Settings\Administrator\My
Documents\nhsp_Joiner.xls","P0y3D0c","Query1"
 
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
 
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
 
Leslie,

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