Exporting File with filename variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query set up to output a flat file for an ftp upload. I need to
save a local copy. I have a couple questions I haven't figured out yet...

1) The filename needs to be in this format ABCDE.#yyjjjhhmm The ABCDE is
constant followed by the . and the # the rest is variable and is yy=year
jjj=julian hhmm is time. Is there a way to have a box come up to enter the
filename? The field in the macro list it as a required argument. I am
trying to avoid renaming a file manually, if I can.

2) The file is not supposed to have the txt extension. How can I make this
happen without manual renaming?

Thanks for any assistance.
 
Convert your macro to a VB module. Right-click on the macro name and choose
Save As...and choose Module in the As box. Then modify the VB function as
follows:

Dim Pathname As String
Dim Filename As String
Pathname = "C:\"
Filename = InputBox("Enter name of export File")
DoCmd.OutputTo acQuery, "qryMyQuery", "MS-DOSText(*.txt)", Pathname &
Filename, False, ""

Of course, you would use the actual name of your query instead of
"qryMyQuery" and set the path to where you actually want it rather than
"C:\". If you don't type the .txt in the inputbox, it will create the file
without it, but it will still be a text file.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
In a macro, you can use an expression for the filename argument:

="C:\MyFolderName\" & InputBox("Enter filename:") & ".txt"
 
Thank you Roger and Ken. Both solutions work. I greatly appreciate the
assistance! Have a great weekend.
 
The below works very well. I am stuck and I believe I left out info for you.
I have export specs the query needs to use. Is there a way to make the code
use the specs? The data from this file is being uploaded via an ETS program.
The receiving program will be looking for this without column headings and
as fixed width (following my specs).

I tried Ken's idea below but always get an error that the object is read
only. If I set an absolute path the macr runs fine. As soon as I tried the
idea he gave I get the error. I thought his might be the quickie qork around
to use my export specs.

I appreciate the help.
 
In that case, use the TransferText method, rather than OutputTo

DoCmd.TransferText acExportFixed, "qryMyQuery Export Specification",
"qryMyQuery", Pathname & Filename, False, ""

Of course, if your export is Delimited, you'd use: acExportDelim


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Thank you Roger! I realized after your second post that the vb coding
follows the macro requirements so I see where to go from now on.

I also searched a bit and found my other problem with the run time error.
The file format I export to has a nonstandard extension. I found the
knowledge base articles. Since the file format is thus: GWHDEP.#yyjjjhhmm
(year, julian, hours, minutes) and is as such variable coding would be
difficult at best. So, I will export as a .txt and the operator can rename
the file (remove extension) before the ftp upload.

Thanks for your help. I see now, too, that my problems with Ken's solution
was purely the file extension issue.
 
Back
Top