export to Excel where name of file is derived from value in databa

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

Guest

I run a routine daily that exports a table into Excel. I would like the Excel
file name to be: rehab_mm-dd-yy.xls where mm-dd-yy is derived from the value
of one field in the table being exported. I don't think I can do that using
transferspreadsheet in a macro, but can I do that using code ? Any
suggestions on what that code would look like ?
 
Yes you can.

Use an expression for the File Name argument in the macro:

="rehab_" & Format(DLookup("FieldName", "TableName"), "mm-dd-yy") &
".xls"
 
That's awesome ! Thanks Ken !!!

Ken Snell said:
Yes you can.

Use an expression for the File Name argument in the macro:

="rehab_" & Format(DLookup("FieldName", "TableName"), "mm-dd-yy") &
".xls"
Click to expand...
 
Now that I have had a chance to try this....it's not working. I must be
doing something not quite right. Here's what I have as the expression in the
File Name argurment:

="F:\Charge Details\" & "rehab_" & Format(DLookUp("[MaxOfcurr_date]","audits
needed"),"mm-dd-yy") & ".xls"

And here's the file name being created:

F:\Charge Details\rehab_.xls

Any ideas ?

Thanks !!
 
What that means is that the DLookup function is returning a Null value to
the expression. Check your spelling of the field and the table/query names.
DLookUp("[MaxOfcurr_date]","audits needed")

What happens if you try the DLookup expression in the Immediate Window by
itself? Does it work there?

--

Ken Snell
<MS ACCESS MVP>

Eric @ SEASH said:
Now that I have had a chance to try this....it's not working. I must be
doing something not quite right. Here's what I have as the expression in
the
File Name argurment:

="F:\Charge Details\" & "rehab_" &
Format(DLookUp("[MaxOfcurr_date]","audits
needed"),"mm-dd-yy") & ".xls"

And here's the file name being created:

F:\Charge Details\rehab_.xls

Any ideas ?

Thanks !!

Eric @ SEASH said:
That's awesome ! Thanks Ken !!!
Click to expand...
Click to expand...
 
The immediate window came back "Null"....I suppose it would have helped the
cause if I made sure I had valid test data first ! It turns out it was
working, but there were not audits needed....when I forced the issue on one
patient, it still worked and named the files as we expcted them to be.

Thanks Ken, for all your assistance !

Ken Snell said:
What that means is that the DLookup function is returning a Null value to
the expression. Check your spelling of the field and the table/query names.
DLookUp("[MaxOfcurr_date]","audits needed")

What happens if you try the DLookup expression in the Immediate Window by
itself? Does it work there?

--

Ken Snell
<MS ACCESS MVP>

Eric @ SEASH said:
Now that I have had a chance to try this....it's not working. I must be
doing something not quite right. Here's what I have as the expression in
the
File Name argurment:

="F:\Charge Details\" & "rehab_" &
Format(DLookUp("[MaxOfcurr_date]","audits
needed"),"mm-dd-yy") & ".xls"

And here's the file name being created:

F:\Charge Details\rehab_.xls

Any ideas ?

Thanks !!

Eric @ SEASH said:
That's awesome ! Thanks Ken !!!

:

Yes you can.

Use an expression for the File Name argument in the macro:

="rehab_" & Format(DLookup("FieldName", "TableName"), "mm-dd-yy") &
".xls"


--

Ken Snell
<MS ACCESS MVP>

"Eric @ SEASH, Evansville"
<[email protected]>
wrote in message
I run a routine daily that exports a table into Excel. I would like
the
Excel
file name to be: rehab_mm-dd-yy.xls where mm-dd-yy is derived from
the
value
of one field in the table being exported. I don't think I can do
that
using
transferspreadsheet in a macro, but can I do that using code ? Any
suggestions on what that code would look like ?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top