Export query

[slimjen1]

All..I'm trying to export a query with date:

Private Sub cmdExport Dim qryname as String Dim theFilepath as String  qryname= "qryExport" theFilepath = "C:\Users\" & Environ("UserName") & "\Desktop\" theFilePath = theFilePath & qryname &  "_" & Format(Date, "yyyy-mm-dd") & ".xls" Docmd.OutputTo acOutput, qryname, acFormatXLS, theFilePath, True

This works fine but I would like the time on the export file in case the users need to export multiple files...when I add Format(Time, "HH:MM:SS") & ".xls" it doesn't work unless I take the colons out...help pls

 

[slimjen1]

I apologize for the way the code presented but doing this from my cell phone

 

[Gasman]

If you try and name a file manually with colons windows tells you it cannot be done.?

Use hyphen or as I would prefer -151702

 

[slimjen1]

Thanks for responding...I tried using hyphens but the user says it can't tell the time from the date...I was trying to put the file in a folder to eliminate the use of time by adding "\Desktop\Exports" but I get an error "microsolf access can't save the output data to the file you've selected ". I used to be able to use the code and Access would create the folder "Exports " on the desktop to save the excel file...did MS Access change it?

 

[Gasman]

Date has 8 digits, time has 6, perhaps just 4 is you are not concerned about the seconds??

I would just use something like

myfile-20191001-155706.ext

 

[arnelgp]

theFilepath = "C:\Users\" & Environ("UserName") & "\Desktop\" 
theFilePath = theFilePath & qryname &  "_" & Format(Now, "yyyy\-mm\-dd hh\-nn\ss am/pm") & ".xls"

 

[slimjen1]

Thanks...sorry about the code tags...thanks for all the suggestions..arnelgp; used your suggestion...worked ok...I'll take it. Anyone has any ideas why a folder can't be created as I indicated in post #4?

 

[arnelgp]

check first if the folder exists, then create if it does not:

Dim strFolder As String
strFolder = Environ("UserProfile") & "\Desktop\Exports"
If Dir(strFolder, vbDirectory) = "" 
    ' does not exists, create it
    Call fnkCreateFolder(strFolder)
End If

Private Function fnkCreateFolder(s As String)
Dim v As Variant
Dim i As Integer
Dim d As String
v = Split(s, "\")
On Error Resume Next
For i = 0 To UBound(v)
    d = d & v(i)
    MkDir d
    d = d & "\"
Next
End Function

 

[slimjen1]

check first if the folder exists, then create if it does not:

Dim strFolder As String
strFolder = Environ("UserProfile") & "\Desktop\Exports"
If Dir(strFolder, vbDirectory) = "" 
    ' does not exists, create it
    Call fnkCreateFolder(strFolder)
End If

Private Function fnkCreateFolder(s As String)
Dim v As Variant
Dim i As Integer
Dim d As String
v = Split(s, "\")
On Error Resume Next
For i = 0 To UBound(v)
    d = d & v(i)
    MkDir d
    d = d & "\"
Next
End Function

I'm getting an error: sub or function not defined
Pointing to Call fnkCreateFolder (strFolder)
I created the public function under modules...why is it erroring out?

 

[Gasman]

Thanks...sorry about the code tags...thanks for all the suggestions..arnelgp; used your suggestion...worked ok...I'll take it. Anyone has any ideas why a folder can't be created as I indicated in post #4?

It appears it has to exist, so as arnelgp states test for it.

However I took your code and it does not even compile?

'DoCmd.OutputTo [COLOR="Red"]acOutput[/COLOR], qryname, acFormatXLS, theFilepath, True
DoCmd.OutputTo acOutputQuery, qryname, acFormatXLS, theFilepath, True

 

[slimjen1]

Ok...Made the correction....

DoCmd.OutputTo acOutputQuery, qryname, acFormatXLS, theFilepath, True

Compiles now. Thanks

So, I still can't get arnelgp code to work. I have created the function and when I'm getting an error: sub or function not defined
Pointing to Call fnkCreateFolder (strFolder)

This code suppose to call the function to see if the folder exist; if not; creates it correct? Is the error telling me I have to define the Function??

 

[Gasman]

I'm getting an error: sub or function not defined
Pointing to Call fnkCreateFolder (strFolder)
I created the public function under modules...why is it erroring out?

Because it is declared as Private.?

arnelgp probably intended for you to use it in your form.
If you are placing it in a module, change Private to Public, or remove it altogether.

 

[slimjen1]

Perfect...thanks so much for all your help!

 

[crislim1113]

Hi good day, is it possible to export multiple queries to one excel workbook but different sheets with this acoutputquery code?

Thanks