Automate Query 2

[dscoiho]

Ok I am a novice at this but would like some guidance. I am using Access 2000. I am looking run a query that would pull from a list like (ABC, DEF, A11 etc..) seperately save to a new folder for today's date, as the code queried into excel. So the saved item would be C:\060706\ABC.xls. That is my general idea just need point in the direction of how to approach the solution.

Thanks

 

[mp9]

1. Create the query or queries that give your desired results.

2. Create a macro with TransferSpreadsheet actions to output your queries. End the macro with a Quit command.

3. Use something like the Handy Access Launcher (

http://www22.brinkster.com/accessory/resources/001.shtml)

to run the macro at scheduled tiems/intervals.

 

[PizMac]

to create your variable folder name you need some VBA code as in

dim fs as object, myfolder as string, myopfile as string
myfolder="C:\" & date() - you may need to fiddle with this
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FolderExists(Myfolder) = True Then
' folder exists
else
fs.CreateFolder(foldername)
endif
myopfile=myfolder & "abc.xls"
' you may want to add some code to change the file name

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qname", myopfile, True

good luck
Judith