How to export filter data on desktop on any computer

[CJ_London]

at the moment you are exporting 'Transalation2' - suggest you need to export 'tmpQry'

[ijaz8883]

Ok lets suppose I have created a query named QryTranslation
Now its the code
Private Sub saveFile_Click()
vDir = "c:\temp"
MakeDir vDir
vFile = vDir & "Myfile.xls"
Dim qdef As QueryDef
Set qdef = CurrentDb.CreateQueryDef("QryTranslation", Me.RecordSource)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "QryTranslation", vFile, True, tabname
CurrentDb.QueryDefs.Delete qdef.Name
End Sub
Public Sub MakeDir(ByVal pvDir)
Dim FSO
On Error Resume Next
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CreateFolder pvDir 'like MkDir pvDir
Set FSO = Nothing
End Sub

But now its giving an error

Now let me know what my code should be?

[CJ_London]

I’ve already told you, reread the posts

[ijaz8883]

at the moment you are exporting 'Transalation2' - suggest you need to export 'tmpQry'
"tmpQry" query name or exact tmpQry???????
I don't understand I am not expert in access statements!

[CJ_London]

Compare your code with what I provided - take note of all the differences, then add in the missing code

[ijaz8883]

Compare your code with what I provided - take note of all the differences, then add in the missing code

Now look at this error

[CJ_London]

looks to me like you still are not doing what I said.

1. you create a query called 'tmpQry' - correct
2. you export a query called 'qsDataExport' - incorrect
3. you delete a query called 'tmpQry' - correct

Next time you have a problem, help me to help you - I'm assuming the error is per 2 above

a) copy paste the code, don't screenshot it
b) by all means screenshot the error message, but please advise which line is causing the issue - click debug and the line will be highlighted

[ijaz8883]

Ok lets see
I have One Table named "Translation2"
I have One Query named "tmpQry"
I have a Search form where data list is "tmpQry"
Now its clear?
My code to export the search data is:-
Private Sub saveFile2_Click()
Dim qdef As QueryDef
Set qdef = CurrentDb.CreateQueryDef("tmpQry", Me.RecordSource)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tmpQry", vFile, True, tabname
CurrentDb.QueryDefs.Delete qdef.Name
End Sub
The line where an error appeared via yellow highlighted:-
Set qdef = CurrentDb.CreateQueryDef("tmpQry", Me.RecordSource)
The error box says
Run-time '3012'
Object 'tempQry' already exists
Thats it!!!

[CJ_London]

because you didn’t delete tmpqry per the initial code snippet it still exists - so you need to manually delete it I don’t know what you mean by datalist - if you mean the record source to you form that is wrong, recordsource should be your table

[ijaz8883]

Ok Thanks a lot
I understand that you are unable to resolve the problem it was a simple search form where I want to export the filter data into excel file and you all never bother to try my provided database to correct it.
Thanks to you all. I am still unsatisfied or unable to resolve my problem
Now I shall try on other form to resolve my problem
Good Day

[CJ_London]

Sorry you feel like that - however recommend on the other forum you provide a link to this thread so others do not provide the same suggestions

[accesstos]

Least said, soonest mended.

ijaz8883,
You have to delete the Querydef before create it.

Try this code for the "saveexcel" button Click event and let us know if it works fine or not.

Private Sub saveexcel_Click()
    Dim qdf As dao.QueryDef
    Dim vDir As String
    Dim vFile As String

    vDir = "c:\temp\"
    MakeDir vDir
    vFile = "Myfile.xls"

    On Error Resume Next
    'Try to delete temporary querydef if exist
    CurrentDb.QueryDefs.Delete "QTemp"

    On Error GoTo ExitHere
    'Append the temporary querydef in QueryDefs collection
    Set qdf = CurrentDb.CreateQueryDef("QTemp", Me.RecordSource)

    'Create the Excel file
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, qdf.Name, vDir & vFile, True
    'There is no reason to delete the temporary querydef "QTemp" at this time.
    'It will the next time that will run this procedure.
    
    MsgBox "File '" & vFile & "' created successfully" _
           & vbCrLf & "in directory '" & vDir & "'" _
           , vbInformation
ExitHere:
    If Err Then MsgBox "Error: " & Err & vbCrLf & Err.Description, vbExclamation
    'Release the qdf variable
    Set qdf = Nothing
End Sub

...and, don't forget declare your variables. ;-)

Regards,
John

[CJ_London]

@accesstos - per post #23 'I have a Search form where data list is "tmpQry"' and mentioned in post #24, your delete query will not work because it is open in the form

[ijaz8883]

Least said, soonest mended.

ijaz8883,
You have to delete the Querydef before create it.

Try this code for the "saveexcel" button Click event and let us know if it works fine or not.

Private Sub saveexcel_Click()
    Dim qdf As dao.QueryDef
    Dim vDir As String
    Dim vFile As String

    vDir = "c:\temp\"
    MakeDir vDir
    vFile = "Myfile.xls"

    On Error Resume Next
    'Try to delete temporary querydef if exist
    CurrentDb.QueryDefs.Delete "QTemp"

    On Error GoTo ExitHere
    'Append the temporary querydef in QueryDefs collection
    Set qdf = CurrentDb.CreateQueryDef("QTemp", Me.RecordSource)

    'Create the Excel file
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, qdf.Name, vDir & vFile, True
    'There is no reason to delete the temporary querydef "QTemp" at this time.
    'It will the next time that will run this procedure.
    
    MsgBox "File '" & vFile & "' created successfully" _
           & vbCrLf & "in directory '" & vDir & "'" _
           , vbInformation
ExitHere:
    If Err Then MsgBox "Error: " & Err & vbCrLf & Err.Description, vbExclamation
    'Release the qdf variable
    Set qdf = Nothing
End Sub

...and, don't forget declare your variables. ;-)

Regards,
John

Grate !!! Its work perfect as I desired.
Thanks a lot
Now I am satisfied

[ijaz8883]

Let me know can I use this statement in this code to create the file on desktop there would no need to create any temp folder/director

Dim strFormName As String
Dim strPathUser As String
Dim strFilePath As String
'set variables
strFormName = "SearchResult"
strPathUser = Environ$("USERPROFILE") & "\Desktop"
strFilePath = strPathUser & strReportName & ".xls"

This code will save the file on desktop but the code I have it does not export the file as your code but now I want to change the file save on desktop instead of C/Temp? is there any response?