Export query (1 Viewer)

slimjen1 · Oct 1, 2019

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

Code:

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 · Oct 1, 2019

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

Gasman · Oct 1, 2019

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 · Oct 1, 2019

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 · Oct 1, 2019

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 · Oct 1, 2019

Code:

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

slimjen1 · Oct 1, 2019

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 · Oct 1, 2019

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

Code:

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

Code:

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 · Oct 1, 2019

arnelgp said:

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

Code:

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

Code:

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 · Oct 1, 2019

slimjen1 said:
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?

Code:

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

slimjen1 · Oct 2, 2019

Ok...Made the correction....

Code:

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 · Oct 2, 2019

slimjen1 said:
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 · Oct 2, 2019

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

crislim1113 · Jan 29, 2021

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

Thanks

Export query (1 Viewer)

slimjen1

Registered User.

slimjen1

Registered User.

Gasman

Enthusiastic Amateur

slimjen1

Registered User.

Gasman

Enthusiastic Amateur

arnelgp

..forever waiting... waiting for jellybean!

slimjen1

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

slimjen1

Registered User.

Gasman

Enthusiastic Amateur

slimjen1

Registered User.

Gasman

Enthusiastic Amateur

slimjen1

Registered User.

crislim1113

New member

Similar threads

Users who are viewing this thread