export to file with auto geneerated filename

[iainm]

My boss is trying to write a macro to export a query to a word document. He can cope with that just fine, but he wants to automatically generate the filename, based on today's date and a few other data items - I'm not entirely clear where those other data items are coming from, I think what he wants is a new form to come up. First off, is there a way to insert the date into a "save file to" text string, like date$ or date(), then is there a way we can make it do some slightly more fancy stuff.

 

[LittleSmudge]

The file name that you save to is simply a text string at the point that you do the save so you can build the text string up to contain anything you like.

( Well you can if you do it in VB code - I'm not so sure if you can do anything as fancy in Macros. There again I don't know anyone who can remember far enough back in history to still be using macros in Access ! )


In code
Set up string variables
Dim strServerPath As String
Dim strFileName As String
Dim strYear As String
Dim strMonth As String
Dim strDays As String

strServerPath = "\\ServerName\FolderName\SubFolderName\"

strYear = Format(Date(),"yyyy&quot
strMonth = Format(Date(),"mm&quot
strDays = Format(Date(),"dd&quot

strServerPath = strServerPath & strYear & "\"
strFileName = "WordExport" & strMonth & strDays

' Then go and do the File Save functionality


"WordExport" can also be made up of other text as you need
The code above also sub sorts the files into distinct folders by year.
You'll need code to check that the folders actually exist as each new year starts if you want to go this route.
Otherwise just insert strYear in the strFileName build up before strMonth

( BTW by using the date order Year,Month,Day the files will automatically sort in the correct chronological order when you view them in WinExplorer of the Word Open dialog box. )



'ope-that-'elps.







G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.