Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103

    How to export filter data on desktop on any computer

    Hi
    I have a search form where I have made an export excel file button but that's not working properly


    1. Its not showing the exact data its shows extra column in export.
    2. The file only exported on my desktop but if I use this db on other pc with other user name its not work. I have searched a file on internet that work properly but locked so I am unable to trace the code of that's db.
    Thanks


    Experiment Database.zip

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    don't export to desktop..everyones PC is different,
    instead, export to "C:\temp"
    the code makes the folder in case the user doent have it.

    Code:
    sub saveFile()
    vDir = "c:\temp\"
    MakeDir vDir
    vFile = vDir & "Myfile.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsData2Export", vFile, True,tabname
    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

  3. #3
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    Quote Originally Posted by ranman256 View Post
    don't export to desktop..everyones PC is different,
    instead, export to "C:\temp"
    the code makes the folder in case the user doent have it.

    Code:
    sub saveFile()
    vDir = "c:\temp\"
    MakeDir vDir
    vFile = vDir & "Myfile.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsData2Export", vFile, True,tabname
    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
    Sorry its not working
    I have tried to copied the whole code into vb and also by change the button name as saveFile and also pasting data in saveFile click event but nothing happen. Let me know what to do more?
    Private Sub saveFile_Click()
    vDir = "c:\temp"
    MakeDir vDir
    vFile = vDir & "Myfile.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsData2Export", vFile, True, tabname
    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



    It does not work


    I have tried another code
    One thing more to say that I just want to export the data showing on form not all showing in the query

  4. #4
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    I don't know if it works properly in all cases, but, give it a try:
    Code:
    vFile = environ("USERPROFILE") & "\desktop\Myfile.xls"

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It does not work
    you need to be more explicit about what 'does not work'? you get an error in the code? the new folder is not created? the .xls is not created? something else?

    One thing more to say that I just want to export the data showing on form not all showing in the query
    again, be more explicit - do you mean just what you can see on the screen i.e you have scrolled to a particular section of a continuous form? or is it a single form? is the form filtered? sorted? Is the form a datasheet/continuous form? is it a subform?

  6. #6
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    It does work. I use it all the time.

  7. #7
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    Experiment Database update.zip
    Now look at this its showing full data of table instead of filtered data
    Last edited by ijaz8883; 01-17-2019 at 01:21 AM. Reason: Update

  8. #8
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    Quote Originally Posted by Ajax View Post
    you need to be more explicit about what 'does not work'? you get an error in the code? the new folder is not created? the .xls is not created? something else?

    again, be more explicit - do you mean just what you can see on the screen i.e you have scrolled to a particular section of a continuous form? or is it a single form? is the form filtered? sorted? Is the form a datasheet/continuous form? is it a subform?
    Yes you are right I am sorry
    After necessary trying again and again and changing the qsexport2excel name found working but not proper as I required
    Its exporting the data of full table not filtered data which is simply exported in macro code but I want to export filtered data which is exporting properly in PDF button but not in excel format.
    Thanks in advance

  9. #9
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    I have change the qsData2Export as my table names used for search but its showing the whole data of table not only the data of search form

    Private Sub saveexcel_Click()
    vDir = "c:\temp"
    MakeDir vDir
    vFile = vDir & "Myfile.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Translation2", vFile, True, tabname
    End Sub
    Its created a temp folder and also create the file Myfile but not showing filtered data. Its showing full data of translation2 table not filtered data
    Please check I want to export the filtered data not full table please.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Please check I want to export the filtered data not full table please.
    in that case provide more information as requested

  11. #11
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    I have attached the database now let me know what information required.
    just click o search form and export the search result.
    I don't understand whats more needed?

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    based on what you have, you need to create a temporary query based on your form recordsource

    something like

    Code:
    Dim qdef As QueryDef
    Set qdef = CurrentDb.CreateQueryDef("tmpQry", Me.RecordSource)
    
    'code to export query
    
    CurrentDb.QueryDefs.Delete qdef.Name

  13. #13
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    Quote Originally Posted by Ajax View Post
    based on what you have, you need to create a temporary query based on your form recordsource

    something like

    Code:
    Dim qdef As QueryDef
    Set qdef = CurrentDb.CreateQueryDef("tmpQry", Me.RecordSource)
    
    
    CurrentDb.QueryDefs.Delete qdef.Name
    'code to export query

    I am using this code in saveexcel button on the Search_All form with the table of Translation2

    Sorry still exporting the whole data of query not searched result.

    Private Sub saveexcel_Click()
    Dim qdef As QueryDef
    Set qdef = CurrentDb.CreateQueryDef("tmpQry", Me.RecordSource)
    vDir = "c:\temp"
    MakeDir vDir
    vFile = vDir & "Myfile.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Translation2", 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
    Lets see If I export the data through macro builder then I just found these three columns extra which are required otherwise its filter the data accurately. But in VB Code I don't understand what the matter Why am i unable to export the data in excel in accordance as I required.
    Click image for larger version. 

Name:	problem.jpg 
Views:	35 
Size:	85.3 KB 
ID:	37009

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not surprised since you are still exporting the original source, not tmpQry

  15. #15
    ijaz8883 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    103
    So let me know What I have to do for the exporting of excel file??

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-25-2018, 09:35 AM
  2. Importing Data from Home Computer to Work Computer
    By wam18jr in forum Import/Export Data
    Replies: 5
    Last Post: 11-06-2017, 06:59 PM
  3. Replies: 4
    Last Post: 11-29-2012, 12:45 AM
  4. Replies: 2
    Last Post: 09-01-2011, 01:23 PM
  5. Replies: 2
    Last Post: 09-29-2009, 10:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums