Export File - Variable File Name

[robinsgn]

I'd like to build a Macro that exports a file, and names the file based
on the date of export (e.g. c:\some_file_2005_10_25.txt). I understand
the TransferText action, but I can't get the File Name field to work
correctly.

Is this possible? Is this possible using a Macro, not VBA?

Thanks,
Gary

 

[Ken Snell [MVP]]

In the Filename argument for the TransferText action, use an expression like
this:

="C:\some_file_" & Format(Date(), "yyyy\_mm\_dd") & ".txt"

 

[Gary]

Ken,
Great, thanks! Works like a charm!

A follow-up question, if I may. Is there a way to incorporate a
specific data field into the field name?

For example, if I have four sales regions in a table: West, East,
South, and North, could I create a Macro that creates a file name such
as "C:\sales_data_north" based upon the value of a specific field?

Thanks again,
Gary

 

[Ken Snell [MVP]]

Assuming that the field is fixed, use the DLookup function to retrieve it:

="C:\sales_data_" & DLookup("FieldName", "TableName") & "_" & Format(Date(),
"yyyy\_mm\_dd") & ".txt"

Similarly, you can read the value from a control on an open form:

="C:\sales_data_" & Forms!FormName!ControlName & "_" & Format(Date(),
"yyyy\_mm\_dd") & ".txt"

 

[Landrew via AccessMonster.com]

I am trying to do basically the same thing except using the OutputTo action.
The macro acts on a report "Lic Reports" and spits out multiple files, one
for each page of the report. I would like to get one of the fields from the
report "Entity Code" into the file name for easier identification. Is this
possible in a macro? If not how could it be done?

Assuming that the field is fixed, use the DLookup function to retrieve it:

="C:\sales_data_" & DLookup("FieldName", "TableName") & "_" & Format(Date(),
"yyyy\_mm\_dd") & ".txt"

Similarly, you can read the value from a control on an open form:

="C:\sales_data_" & Forms!FormName!ControlName & "_" & Format(Date(),
"yyyy\_mm\_dd") & ".txt"

Ken,
Great, thanks! Works like a charm!

[quoted text clipped - 29 lines]

 

[Ken Snell [MVP]]

What are you wanting... the value from one of the fields in the report's
recordsource query? the value in a control on the report? what event runs
the OutputTo macro action?

--

Ken Snell
<MS ACCESS MVP>

I am trying to do basically the same thing except using the OutputTo
action.
The macro acts on a report "Lic Reports" and spits out multiple files, one
for each page of the report. I would like to get one of the fields from
the
report "Entity Code" into the file name for easier identification. Is
this
possible in a macro? If not how could it be done?

Assuming that the field is fixed, use the DLookup function to retrieve it:

="C:\sales_data_" & DLookup("FieldName", "TableName") & "_" &
Format(Date(),
"yyyy\_mm\_dd") & ".txt"

Similarly, you can read the value from a control on an open form:

="C:\sales_data_" & Forms!FormName!ControlName & "_" & Format(Date(),
"yyyy\_mm\_dd") & ".txt"

Ken,
Great, thanks! Works like a charm!

[quoted text clipped - 29 lines]

Thanks,
Gary

 

[Landrew via AccessMonster.com]

Yes, I would like one of the fields from the report, or its source which is a
table, to be implanted into the filename when running the action.
Right now the code is in a separate module which is activated by the run
button in the module design window. The code in the module is:

Private Sub createReports()
Dim fileName As String
fileName = Reports![LicReport09-09-05]![EntityCode]

DoCmd.OutputTo acReport, "LicReport09-09-05", "HTML(*.html)", "C:\
Documents and Settings\\My Documents\License Test\ & strfileName & .html",
False, "", 0

End Sub

The result is that it exports the fies to the right location and in the right
format, but the file name comes out like "& strfileName page1.html." and "&
strfileName page2html." etc.
I don't know how, either in a macro, or in the vba design window to get
Access to understand that a field name needs to be part of the file name.

What are you wanting... the value from one of the fields in the report's
recordsource query? the value in a control on the report? what event runs
the OutputTo macro action?

I am trying to do basically the same thing except using the OutputTo
action.

[quoted text clipped - 20 lines]

 

[Ken Snell [MVP]]

You're not concatenating the value of the field into the string. Try this:

DoCmd.OutputTo acReport, "LicReport09-09-05", _
"HTML(*.html)", _
"C:\Documents and Settings\\My Documents\License Test\" & _
strfileName & ".html", False, "", 0

--

Ken Snell
<MS ACCESS MVP>

Yes, I would like one of the fields from the report, or its source which
is a
table, to be implanted into the filename when running the action.
Right now the code is in a separate module which is activated by the run
button in the module design window. The code in the module is:

Private Sub createReports()
Dim fileName As String
fileName = Reports![LicReport09-09-05]![EntityCode]

DoCmd.OutputTo acReport, "LicReport09-09-05", "HTML(*.html)", "C:\
Documents and Settings\\My Documents\License Test\ & strfileName & .html",
False, "", 0

End Sub

The result is that it exports the fies to the right location and in the
right
format, but the file name comes out like "& strfileName page1.html." and
"&
strfileName page2html." etc.
I don't know how, either in a macro, or in the vba design window to get
Access to understand that a field name needs to be part of the file name.

What are you wanting... the value from one of the fields in the report's
recordsource query? the value in a control on the report? what event runs
the OutputTo macro action?

I am trying to do basically the same thing except using the OutputTo
action.

[quoted text clipped - 20 lines]

Thanks,
Gary

 

[Landrew via AccessMonster.com]

This code results in file names that go page1.html, page2.html, etc. In
other words it cuts out our attempt to influence the file name at all.

You're not concatenating the value of the field into the string. Try this:

DoCmd.OutputTo acReport, "LicReport09-09-05", _
"HTML(*.html)", _
"C:\Documents and Settings\\My Documents\License Test\" & _
strfileName & ".html", False, "", 0

Yes, I would like one of the fields from the report, or its source which
is a

[quoted text clipped - 29 lines]

 

[Ken Snell [MVP]]

Note that you cannot read the value of a control on a report; this differs
from the ability to read the value of a control on a form.

Where are you running this code? In which object? In which event of that
object?
You'll probably need to change the way you get the value that you want to
put in the filename.

--

Ken Snell
<MS ACCESS MVP>

This code results in file names that go page1.html, page2.html, etc. In
other words it cuts out our attempt to influence the file name at all.

You're not concatenating the value of the field into the string. Try this:

DoCmd.OutputTo acReport, "LicReport09-09-05", _
"HTML(*.html)", _
"C:\Documents and Settings\\My Documents\License Test\" & _
strfileName & ".html", False, "", 0

Yes, I would like one of the fields from the report, or its source which
is a

[quoted text clipped - 29 lines]

Thanks,
Gary

 

[Landrew via AccessMonster.com]

Thanks for the tip. I've been running this code from a module. I used the
Tools/macro/ convert macro to visual basic menu to generate the code. I just
click the execute button in the module to get it to run. After much
tinkering it still runs and exports the reports just the way they need to be.
However, because there are so many of them, there needs to be a way to
identify who they belong to.

Note that you cannot read the value of a control on a report; this differs
from the ability to read the value of a control on a form.

Where are you running this code? In which object? In which event of that
object?
You'll probably need to change the way you get the value that you want to
put in the filename.

This code results in file names that go page1.html, page2.html, etc. In
other words it cuts out our attempt to influence the file name at all.

[quoted text clipped - 11 lines]

 

[davidjohnson98]

ok, after reading the above, I tried...and failed. "\\dce\corpdata\Finance\VendorInvoices\temp\" & forms!FEDEX_Bill_forPDF_VendorBill![Shipment Tracking Number] & ".pdf" in output file in export with formatting in a macro gets: "dude you're a dumb @#$" (Microsoft Access can't save the output data to the file you've selected). I have rights (can add / delete / modify) and it is an empty folder so I am not creating a duplicate. Thank you for being a great resource.

 

[davidjohnson98]

I also tried: "\\dce\corpdata\Finance\Vendor Invoices\temp\" & forms![FEDEX_Bill_forPDF_VendorBill]![Shipment Tracking Number] & ".pdf"

if I hard code the details \\dce\corpdata\Finance\Vendor Invoices\temp\123456.pdf it works. I tried on my local drive C:\ ... etc. and it won't save there either.