Re: How do I use a parameter in a file name (path)?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I used these instructions to output a query to an .xls file, using the
InputBox function just for the filename, but Access returned error message
"can't save the output data to the file you selected" with 3 messages "file
may be open", "if you are using a template" and "if the file isn't open ...
disk space". I didn't have the file open (and did not choose to launch Excel
on the OutputTo, didn't use a template, and have more than enough disk space.
The macro opens the query, ouputs the query and closes. I've rearranged these
elements and have chosed different options, but always run up against this
error message. I am running Access 2000 SP-3, v. 9.0.8961 on Windows XP Pro.
--
KKennedy


"Ken Snell MVP" wrote:

="C:\Documents and Settings\Administrator\My Documents\TPA\" &
InputBox("Enter folder name:") & "\NEW\" &
InputBox("Enter filename:") & ".xlsx"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




"BJP" <BJP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3635AD8A-FCDA-4867-9661-6E99DBDE2AF0@xxxxxxxxxxxxxxxx
Thanks Ken, but I just want a prompt to enter the folder name and the file
name. C:\Documents and Settings\Administrator\My
Documents\TPA\OVNT\NEW\UPGFw090406.xlsx
where OVNT is replaced by something like 'Enter name of folder' and where
UPGFw090406 is replaced by something like 'Enter file name'. Now I run
the
macro many times and for each time I have to edit the macro changing the
folder name and the file name. So instead of having to edit the macro
each
time for the folder name and file name, I would just have to type in the
folder name and the file name.

Thanks again,
BJP
ben@xxxxxxxxxxxxxxx


"Ken Snell MVP" wrote:

If the "parameter" value is found in a textbox or combobox or listbox on
a
form that is open when the macro runs:

="C:\Documents and Settings\Administrator\My Documents\TPA\" &
Forms!NameOfForm!NameOfControl.Value & "\NEW\" &
Forms!NameOfForm!NameOfControl.Value & ".xlsx"

Note that you must use the = and " characters in the expression for the
path
argument.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"BJP" <BJP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FD36C555-1FC2-436E-AFA4-3F19A4D40256@xxxxxxxxxxxxxxxx
I have a Access macro containing a Tranferspreadsheet step. How do I
use a
parameter for the folder name and file name. Example:
C:\Documents and Settings\Administrator\My
Documents\TPA\parameter\NEW\parameter.xlsx

Thank you.






.

Relevant Pages