Specify File Path in a Macro

[garyholc]

I have a form which has the file folder location contained in a file called Document Location. On the form is a button which runs a macro to export a report in Word format.

All works well, except the Macro prompts the user for the save location for the file, and defaults to the name of the report.

Is there any way to :

1) grab the document location from the Form
2) change the filename of the file being saved to represent say the record number on the form

The save the file in this location under this name without prompting the user? Or is it better not to use a macro but write some VB code to export the report in Word format?

 

[gregtriplett]

Have you had any luck figuring this out? I'm trying to do a similar thing. In my case I'm trying to export a record to a log file such that each time I do this export it gets appended to the log file. Do you have any thoughts on this?

 

[boblarson]

Have you had any luck figuring this out? I'm trying to do a similar thing. In my case I'm trying to export a record to a log file such that each time I do this export it gets appended to the log file. Do you have any thoughts on this?

If you are just adding to a text file you can use something like:

Dim strPath As String
Dim strData As String

strPath = "YourPathAndFileNameToTextFile.txt"

Open strPath For Append As #1

strData = "Whatever and however you get it here"

Print #1, strData

Close #1

You can also use

Write #1, strData

if you don't want quotes around the string values.

 

[gregtriplett]

Wow, thanks for the quick response. Unfortunately I'm not much of a VBA guy yet - I'd sure like to be however - so I'm not sure I fully understand the statement: strData = "Whatever and however you get it here". Rather than trying to train me in VBA, although if you have the time and energy I'm all ears, do you know of some way to use the Access Macro function choices to somehow incrementally update a table or spreadsheet or text file (last resort) with on-going (not overwrite) copies of individual records each time they are updated by the database user?

 

[garyholc]

Rather than use a macro I wrote it in code to save the file to a specified path.

The code I used was:


DoCmd.OutputTo acOutputReport, "R_Catalogue_Quote", acFormatRTF, Forms!f_catalogue_quote![DocLoc] & "\Catalogue Quote IMAC" & Forms!f_main_form2![RFC_Number] & ".rtf", True


R_Catalogue_Quote is the name of the report

acFormatRTF is the export format (RTF Word)

Forms!f_catalogue_Quote![docloc] is a label which shows the path of the files (for example you could type "C:\Temp" in here)

"&\Catalogue Quote IMAC" is then the start name of the file

& Forms!f_main_Form2![rfc_number] is a number shown on a previous screen which is added to the filename and then with the .rtf extension on the end.

 

[gregtriplett]

Thanks very much. This looks like it might be adaptable to my needs. In my case I need to automatically increment the file name (number) each time I do the export so I basically create a running log. Thanks again...