Save File with date and user specifies path and type

  • Thread starter Thread starter Terry B via AccessMonster.com
  • Start date Start date
T

Terry B via AccessMonster.com

I am trying to figure out a way to save a file to disk,
where the user specifies the path and the user gets to
specify the file type, as well as have the name combined
with the current date.The code below works like I want
only if I remove the "& Format(Date, "mm-dd-yy")" from the doc name.
I also tried putting the date into the DOCmd output file
parameter and I get exactly the same results.
In the DOCmd...
I dont specify a file type, so the user is prompted.
I dont specify a path so the user is prompted.
The date is tagged onto the end of the filename like I want in the
save file dialogue box, but I get an error saying I have mispelled
the report name or it dosnt exist when I click "Save".
I can see why, because the name is
"Final Contract", not "Final Contract 5-30-05"
Is there a way to get around this?
(Access 2003) Report is based on a Query of the current record and
lanuched from the current form. Code as of now:


Private Sub Save_Rpt_Click()
On Error GoTo Err_Save_Rpt_Click

Dim stDocName As String

stDocName = "Final Contract" & Format(Date, "mm-dd-yy")
If NewRecord Then
MsgBox "This record contains no data.", vbInformation, "Invalid
Action"
Exit Sub
Else
DoCmd.OutputTo acReport, stDocName, , , True

Exit_Save_Rpt_Click:
Exit Sub

Err_Save_Rpt_Click:
MsgBox Err.Description
Resume Exit_Save_Rpt_Click
End If
End Sub


Thanks Terry Best
 
I am trying to figure out a way to save a file to disk,
where the user specifies the path and the user gets to
specify the file type, as well as have the name combined
with the current date.The code below works like I want
only if I remove the "& Format(Date, "mm-dd-yy")" from the doc name.
Click to expand...
& Format(date(),"mm-dd-yy") no quotes before and after.
 
Chuck,
Sorry, I was "Quoting" my code from below the original message.
My code does not have quotes at beginning or end. (See Code)
I was just saying that if I remove the section between my quotes it will work.


Thanks
I am trying to figure out a way to save a file to disk,
where the user specifies the path and the user gets to
specify the file type, as well as have the name combined
with the current date.The code below works like I want
only if I remove the "& Format(Date, "mm-dd-yy")" from the doc name.
Click to expand...
& Format(date(),"mm-dd-yy") no quotes before and after.
I also tried putting the date into the DOCmd output file
parameter and I get exactly the same results.
Click to expand...
[quoted text clipped - 33 lines]
Thanks Terry Best
Click to expand...
Click to expand...
 
Chuck,
Sorry, I was "Quoting" my code from below the original message.
My code does not have quotes at beginning or end. (See Code)
I was just saying that if I remove the section between my quotes it will work.


Thanks
Click to expand...

Note the () after the date.
I also tried putting the date into the DOCmd output file
parameter and I get exactly the same results.
Click to expand...
[quoted text clipped - 33 lines]
Thanks Terry Best
Click to expand...
Click to expand...
Click to expand...
 
well, the issue is that you need to be able to build the filepath (including
complete filename with extension) - using the ObjectName (report name)
argument BUT without changing that argument itself, because Access needs to
use it to identify the report object to output.

to build a complete filepath, you need the filepath (ex:
C:\MyFolder\MySubFolder\), the filename (which you can get from the
ObjectName argument), and the correct file extension (ex: .xls, .rtf). you
can call the Browse for Folder window from code, so the user can navigate to
the directory/folder where the output should be saved. see
http://www.mvps.org/access/api/api0002.htm for VBA code to capture the
chosen filepath. i couldn't find a custom function that calls the File Type
dialog (though i didn't search the internet extensively). you can build your
own dialog to allow the user to choose the output format, and return the
correct asFormat constant for that argument, as well as the correct file
extension for the filepath argument. once you have gathered the components
of a complete filepath, then you can concatenate them in the OutputTo
command's OutputFile argument (including that date value you wanted).

one drawback to building your own dialog to return the OutputFormat, is that
i don't know what default version of Excel will be used when the return
value is acFormatXLS. but if that's not a problem for you, then no worries
there.

hth
 
Back
Top