Appending parameter from parameter query to exported filename

[LauraB]

I have some code that runs a parameter query and then saves the results as an
xls file (see below). What also I'd like to do is capture the entered
parameter and append it to the filename. Can I programatically access the
user entered parameter?

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Names with
DateCreated", "C:\Documents\Database\testfile.xls", True

 

[Alex Dybenko]

Hi,
I think best way is to create a form to enter this parameter, so you get it,
append to filename and then pass it to your query

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Pendragon]

dim strParInfo as string, strFileName as string
strParInfo = InputBox("Enter your parameter information here.")
strFileName = "C:\Documents\Database\testfile" & strParInfo & ".xls"

Then substitute strFileName in your code where you have explicitly listed
the path and file name.

The input box assumes the user is inputting alphanumeric information - if
you think the user might get creative (like adding slashes or other no-no
characters) then you should expand the text prompt of the input box to
instruct the user to type only letters and numbers.

HTH

 

[LauraB]

Thank you Pendragon and Alex. I suspected that my nice one line of code
would have to turn ten. The input is a date, so yes I'll need to make sure I
strip out slashes and put it in a format that is understandable and filename
friendly.

Quick follow up question... once I have both the input parameter and the
string I want to append, can I still use the TransferSpreadsheet command?
How do I specify for the query variable how to take my input parameter?

 

[Pendragon]

I believe you cannot execute a query from code wherein parameters are passed.
What you need to do, then, is take Alex's advice and create a form which
takes input from the user (open text boxes or selections from combo boxes).
You can still use an input box to get the date, but as long as you're
creating a form you might as well have a field on the form for this. Set the
field properties to date.

In code, those user inputs are set to variables. Those variables are
appended to an SQL string which is executed in code. This puts the data into
your table. You can then use TransferSpreadsheet on the table as you have
done.

First, make your query generic without any parameters and then copy the SQL
of the query. This is the string statement for your VBA variable MySQL.

Basic example - you will need to edit with your form, field, query and table
names.

'strPar1 and strPar2 are fields on your form - your parameters for the query
dim strPar1 as string, strPar2 as string, MySQL as string, strUserDate as
string

strPar1 = forms!MyForm.Field1
strPar2 = forms!MyForm.Field2
strUserDate = forms!MyForm.txtDateField

MySQL = "SELECT qryField1, qryField2 {etc etc} INTO TableNameToBeExported
FROM TableName"

Now apply your parameters to the SQL statement.

MySQL = MySQL & " WHERE (qryField4) = " & strPar1 & " AND (qryField5) = " &
strPar2 & ";"

NOTES - a) important that you have a space before the WHERE and after the
quotation mark; b) you will have to add appropriate characters if strPar1
and/or strPar2 are text (meaning adding single quotes).

Now execute the SQL to make your table. If you want to suppress messages,
add the DoCmd.SetWarnings False before and DoCmd.SetWarnings True after the
RunSQL statement.

DoCmd.RunSQL MySQL

To create your path and file name from the date field (user input):

strFileName = "c:\mydocuments\MyFileName" & "-" &
format(Month(strUserDate),"mm") & format(Day(strUserDate),"dd") &
format(Year(strUserDate),"yy") & ".xls"

This should create a path and file name like
"c:\mydocuments\MyFileName-110208.xls".

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Names with
DateCreated", strFileName, True

If you run into errors or problems, post your revised code.

Good luck!