How to export filter data on desktop on any computer

[accesstos]

Hi ijaz8883!
Happy to help!

Please pay attention to the variants that you use. You declare strFormName and you use strReportName(!).
Try to keep on the top of every code module these two lines of code:

Option Compare Database
Option Explicit

Especially the second one, will teach you to be consistent with your declarations.

Apropos your question, the retrieval of destination path from the user using a native dialog box, will give to your project a more professional and durable structure.
I return your sample database as suggestion with the relevant modifications in code.
New Experiment Database.zip


(Inspect the code of the Module1 and the new code of Click event of "saveexcel" button)

Let me know if you are satisfied again.

[ijaz8883]

It is also a good solution but the solution you have already provided was best
and I have made a mixture of your previously provided code and the code I already get from the Internet and I made the saveexcel file save on desktop
and I am very happy to do this
First I get your code it was perfect to export the data but I dont want to save the data on C/Temp I want to save the data on Current Desktop
Second code was just to save the file on desktop but was not properly working to export the correct data
I mixed them with each
Now its working perfectly the data exported properly as you provided the code and the file save on desktop properly which was not provided in this form
I dont know it will conflict in future but now at this time its working perfect
and Thanks to your VBBox for verification that your file has been saved on desktop
I am wonder that VIP and other experts fail to do this but you have experiment and give me perfect solution
Thanks a lot again

[ijaz8883]

I want one more help in this regard.
I want to open the file after exporting to desktop
Which code should be added more to open the file after export and saving on desktop?

[accesstos]

Second code was just to save the file on desktop but was not properly working to export the correct data

I have to note that the new code creates a separate Worksheet in the same Workbook (if already exists) for each different Name in search results. Maybe this hasn't come to your attention until now.

Which code should be added more to open the file after export and saving on desktop?

Change the code of export verification with this:

'Succesful export
If MsgBox("Your choice has been exported successfully " & vbCrLf _
          & "(as Worksheet '" & strQName & "') in Workbook '" & vFile & "'" _
          & vbCrLf & "on: '" & vDir & "'" & vbCrLf & vbCrLf _
          & "Do you want to open the workbook '" & vFile & "' ?" _
          , vbInformation + vbYesNo, _
          "Export Search Results") = vbYes Then
    'Open the file with its default Application
    Application.FollowHyperlink vDir & vFile, , True
End If

Thanks a lot again

My pleasure!

[CJ_London]

I am wonder that VIP and other experts fail to do this

the solution I provided is much the same as the one you are using - the difference is I was trying to teach you, not provide the solution. Numerous times I pointed out where you were going wrong, but for whatever reason I was not able to be clear enough.

[ijaz8883]

the solution I provided is much the same as the one you are using - the difference is I was trying to teach you, not provide the solution. Numerous times I pointed out where you were going wrong, but for whatever reason I was not able to be clear enough.

Thanks but be sure that I am quite unfamiliar of Access Commands and code I just change the code and use for my use
Thanks

[ijaz8883]

[QUOTE=accesstos;420808]

'Succesful export
If MsgBox("Your choice has been exported successfully " & vbCrLf _
          & "(as Worksheet '" & strQName & "') in Workbook '" & vFile & "'" _
          & vbCrLf & "on: '" & vDir & "'" & vbCrLf & vbCrLf _
          & "Do you want to open the workbook '" & vFile & "' ?" _
          , vbInformation + vbYesNo, _
          "Export Search Results") = vbYes Then
    'Open the file with its default Application
    Application.FollowHyperlink vDir & vFile, , True
End If

Thanks for your good code
But I am still facing the problem not exporting or openiing but its after opening the file error its says multiple error but file open successfully after these rubish errors
I have attached the file Experiment Database worked.zip
and I am using this code

Private Sub Export_to_excel_Click()
Dim qdf As dao.QueryDef
Dim strPathUser As String
Dim strFilePath As String
Dim strFormName As String

strFormName = "Search_Result.xls"
strPathUser = Environ$("USERPROFILE") & "\Desktop"
strFilePath = strPathUser & strFormName & ""


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, strFilePath, True
'There is no reason to delete the temporary querydef "QTemp" at this time.
'It will the next time that will run this procedure.

If MsgBox("Your choice has been exported successfully " & vbCrLf _
, vbInformation + vbYesNo, _
"Export Search Results") = vbYes Then
'Open the file with its default Application
Application.FollowHyperlink strFormName, strFilePath, True
End If
ExitHere:
If Err Then MsgBox "Error: " & Err & vbCrLf & Err.Description, vbExclamation
'Release the qdf variable
Set qdf = Nothing
End Sub

It seems that I am missing in path in this line or something else
" Application.FollowHyperlink strFormName, strFilePath, True"
Please help there

[accesstos]

Try it as is:

Private Sub Export_to_excel_Click()
    Dim qdf As dao.QueryDef
    Dim strPathUser As String
    Dim strFilePath As String
    Dim strFormName As String

    strFormName = "Search_Result.xls"                               'The name of the Workbook
    strPathUser = Environ$("USERPROFILE") & "\Desktop"       'The path of user desktop
    strFilePath = strPathUser & "\" & strFormName                'The absolute file path of the Workbook

    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, strFilePath, True
    'There is no reason to delete the temporary querydef "QTemp" at this time.
    'It will the next time that will run this procedure.

    If MsgBox("Your choice has been exported successfully " & vbCrLf _
              & "(as Worksheet '" & qdf.Name & "') in Workbook '" &   strFormName   & "'" _
              & vbCrLf & "on: '" & strPathUser & "'" & vbCrLf & vbCrLf _
              & "Do you want to open the workbook '" &   strFormName   & "' ?" _
              , vbInformation + vbYesNo, _
              "Export Search Results") = vbYes Then
        Application.FollowHyperlink strFilePath, , True
    End If
ExitHere:
    If Err Then MsgBox "Error: " & Err & vbCrLf & Err.Description, vbExclamation
    'Release the qdf variable
    Set qdf = Nothing
End Sub

[ijaz8883]

Oh Grate Thanks a lot Its perfect
My problem solved
Thanks a lot Mr. accesstos

[ijaz8883]

Hi Mr accesstos

I am excited to use your codes of exporting data on every where and also on desktop
I have also tried to export data in pdf as your method and its work grate
But I am facing a little problem
When I copy and paste the module in any other new access db or any other db where I want to use this "Function GetPath() As String" & "Dim FD As FileDialog" module its give error and highlight the module "Function GetPath() As String" & "Dim FD As FileDialog" lines while I copy all the files of other db and pasted in your provided db that work grate but in other db its make problem!!
I don't understand what the matter is !! I think the module is not properly functioning on other db I don't know why????? I have also made new macro in other db and pasted the same code but same error.
can you help me how to fix this error so the module work on other dbs' too as this db

[ijaz8883]

sample.zip
Look at this db there it having exactly same table, form and module but its give error.

[accesstos]

You have to add a reference to the Microsoft Office 16.0 Object Library in each VBA project you use the FileDialog object.

Function GetPath() As String
    'A reference to the Microsoft Office 1?.0 Object Library is required.
    'https://docs.microsoft.com/en-us/office/vba/language/how-to/check-or-add-an-object-library-reference
    Dim FD As FileDialog
    
    Set FD = FileDialog(msoFileDialogFolderPicker)
    With FD
        .Title = "Select a folder"
        .AllowMultiSelect = False
        .ButtonName = "OK"
        If .Show Then
            GetPath = .SelectedItems(1)
        End If
    End With
    Set FD = Nothing
End Function

[ijaz8883]

You have to add a reference to the Microsoft Office 16.0 Object Library in each VBA project you use the FileDialog object.

Function GetPath() As String
    'A reference to the Microsoft Office 1?.0 Object Library is required.
    'https://docs.microsoft.com/en-us/office/vba/language/how-to/check-or-add-an-object-library-reference
    Dim FD As FileDialog
    
    Set FD = FileDialog(msoFileDialogFolderPicker)
    With FD
        .Title = "Select a folder"
        .AllowMultiSelect = False
        .ButtonName = "OK"
        If .Show Then
            GetPath = .SelectedItems(1)
        End If
    End With
    Set FD = Nothing
End Function

Thanks a lot its work Grate
Problem solved permanently