Output File Naming

[MJ]

I know this has been asked many times, but in reviewing all of the responses
in the discussion area I have not found one that works yet.

When I use "OutputTo" with a generic name,
\\server\share$\directory\OutputFile.xls ,

it works BUT then someone needs to touch it and rename it to identify it
with the week it was run for and prevent overwriting it with subsequent runs.
This is why I want to date stamp it on delivery.

I want to include a date stamp ("mmdd") into the file name on output:
\\server\share$\directory\OutputFilemmdd.xls

Some of the responses suggest using "TransferSpreadsheet" over "OutputTo" in
a macro, but I have not gotten it to work in either case. As a file output
from a macro, I have tried both "OutputTo" and "TransferSpreadsheet" with the
same error results: "The expression you entered has a function name that
<DB>. can't find.".

Several responses (whether using "OutputTo" or "TransferSpreadsheet") have
suggested a variation of:
="\\server\share$\directory\OutputFile" & Format(Date(), "mmdd") &".xls"

Can someone help me resolve this? Thank you in advance.

 

[MaggieB]

Try the following:

rptDate = (Date)
Dim rptname As String
rptname = "\\server\share$\directory\OutputFile" & Format(Month(rptDate),
"00") & Format(Day(rptDate), "00") & Format(Year(rptDate), "00") & ".xls"

 

[Steve Schapel]

MJ,

The only problem I can see with your suggestion of:
="\\server\share$\directory\OutputFile" & Format(Date(), "mmdd") &".xls"
... . is the inclusion of a space within the Format function where there
shouldn't be one, and the failure to put a space after the final '&'. I
am not sure whether this is a copy/paste from your macro argument, or a
typo in your post. But anyway:
="\\server\share$\directory\OutputFile" & Format(Date(),"mmdd") & ".xls"

Having said that, I have very limited experience with using network
addresses. If this still doesn't work, is it feasible to map your
network share to a drive letter, and use that instead?

 

[MaggieB]

I copied and pasted from the module -- I export a variety of spreadsheets
with this code every day except that I actually have the path mapped instead
of the ="\\server\share$\directory\OutputFile".

Hope that helps.

MJ,

The only problem I can see with your suggestion of:
="\\server\share$\directory\OutputFile" & Format(Date(), "mmdd") &".xls"
.. . is the inclusion of a space within the Format function where there
shouldn't be one, and the failure to put a space after the final '&'. I
am not sure whether this is a copy/paste from your macro argument, or a
typo in your post. But anyway:
="\\server\share$\directory\OutputFile" & Format(Date(),"mmdd") & ".xls"

Having said that, I have very limited experience with using network
addresses. If this still doesn't work, is it feasible to map your
network share to a drive letter, and use that instead?

I know this has been asked many times, but in reviewing all of the responses
in the discussion area I have not found one that works yet.

[quoted text clipped - 20 lines]

Can someone help me resolve this? Thank you in advance.

 

[Steve Schapel]

Maggie,

My comments were not directed at you. I was responding to the
expression that MJ posted.

By the way, please note that MJ's question relates to a macro.

 

[MJ]

Maggie,

I agree with you and your suggestion would work IFF I were doing this in
VB/SQL behind the scenes, but I am asking if someone can help me find a way
to dynamically name the file through the macro process.

Thank you for your insight.

 

[MJ]

Steve,

I went back and checked on your observations:
a) making sure that there was NO space within the Format(Date(),"mmdd")
instruction WITHOUT any change in the error.
b) the lack of a space following the final "&" must have been a typo on my
original submit.

Unfortunately, making the change you suggested did not change the results.
Do you have any other insights/suggestions?

 

[Steve Schapel]

MJ,

How about my question about the feasibility of mapping the share to a
drive letter?

 

[MJ]

Steve,

That might well work if our organization had some kind design on drive
mappings. :-( Unfortunately, I have been tilting at that windmill for
sometime without any progress from our IT staff.

We have some mandatory "corp" mappings, but otherwise it is wide open and
that makes it bad from the standpoint that we cannot seem to come to a
consensus even within our department. This leaves us with using UNLs to make
sure that any user can get to the right place if they run a process.

Thank you for the input and question,

 

[Steve Schapel]

MJ,

Ok, I understand.

Well, as I said, I have no direct experience with this scenario. Nor do
I have access to a computer with network shares that I can test with.

What version of Access are you using? If Access 2007, we could use a
TemVar.

Otherwise, it may be that it will work using VBA rather than macro, so
you could try this, and I can help with this if you like.

Alternatively, you could export to the generic file name, and then
rename the file after export... but that also would need VBA rather than
macro.

 

[Steve Schapel]

Maggie,

Just as a metter of interest, this would be easier and more efficient:

Forget rptDate

Dim rptname As String
rptname = "\\server\share$\directory\OutputFile" & Format(Date,
"mmddyy") & ".xls"