output

[slimjen]

All, using access 2010. I have 3 queries I need to export to 3 different excel spreadsheets to one folder. So when I use the code



DoCmd.OpenQuery "qry1"
DoCmd.OutputTo acOutputQuery, "qry1", acFormatXLSX

How can I save the query to folder created at the time of the export? Ex. create the folder to C:\20110811NewFolderName export query without having the user to have to create the folder before saving
. Thanks

[June7]

Bing: access vba create folder if doesn't exist
http://www.techonthenet.com/access/f...file/mkdir.php

[slimjen]

ok. I tried this but I need to add the date in front of the name folder:

MkDir "C:\Directory\mainfolder\& Format(Date, "yyyymmdd")folder

This doesn't work??

[June7]

MkDir "C:\Directory\mainfolder\" & Format(Date, "yyyymmdd") & "folder"

[slimjen]

ok when I try to declare it; error states "end of statement expected" and highlights "C:\Directory\mainfolder\"

spath = MkDir "C:\Directory\mainfolder\" & Format(Date, "yyyymmdd") & "folder"

DoCmd.OutputTo acOutputQuery,  sPath "qryMidnite", acFormatXLS

[June7]

spath = "C:\Directory\mainfolder\" & Format(Date, "yyyymmdd") & "folder"
MkDir strPath

[slimjen]

I am getting this Error "Path/File access error

Dim sPath As String
sPath = "C:\Directory\mainfolder\" & Format(Date, "yyyymmdd") & "folder"
MkDir sPath
DoCmd.OutputTo acOutputQuery, sPath, "qryMidnite", acFormatXLS

I tried MkDir in place of sPath and with sPath. Still erroring

[June7]

MkDir works for me but I only tested at the C root. Maybe there is a permissions issue somewhere along the path.

Do a test of code creating folder at C root.

[slimjen]

o ok. It created the folder but didn't export the files. Something is wrong with my code to output the queries.

[June7]

The parameters given for the OutputTo arguments are out of order and incomplete.

DoCmd.OutputTo acOutputQuery, "query name", acFormatXLS, strPath & "\" & "file name.xls"

[slimjen]

It worked but really slow. I'll work on it. Thanks.

[slimjen]

how do I get spaces between the dates. ex. "yyyymmdd"= 20140812 I would like 2014 08 11

[June7]

Format(Date, "yyyy mm dd")

[slimjen]

I got this. Thanks but I do no get a space between the date and the folder name. 2014 08 12foldername

[June7]

Format(Date, "yyyy mm dd ")