Results 1 to 10 of 10
  1. #1
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    32

    Export Query to Multiple Text Files

    Greetings,



    I have a Access 2016 database where I'm trying to export a query named selWIP into multiple text files based on the PHSOTO field. In the example database attached, there are 5 distinct PHSOTO numbers. This should give me 5 text files. Each text file containing the PHSOTO, PHSHNM and CHCASN fields for their respective PHSOTO.

    I found the vba code in a Google search and put it in a module. But every time I run it I get a Run-time error 424 Object required error. I thought I modified it correctly, but I guess not. Could someone please let me know what I'm doing wrong?

    Thanks.TEST EXPORT.accdb

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,759
    Just run a query each time with the required number as criteria. Export that query to text or csv file.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    32
    Quote Originally Posted by Welshgasman View Post
    Just run a query each time with the required number as criteria. Export that query to text or csv file.
    Thanks Welshgasman for your reply. But that's what I'm trying not to do. Using your suggestion, I would have to run this query 75 to 100 times daily. I want to run this query once and have vba that will export a text file for each PHSOTO.

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,304
    make a list box that has all the phsoto #'s.
    code will then scan the list, pull data for that #, and export:

    Code:
    
    For i = 0 To lstBox.ListCount - 1
       vItm= lstBox.ItemData(i)  'get next item in list data
       lstBox = vItm     'set listbox to the item
     
         'now get values from field columns
       vName = lstBox.column(0)  'in vb, columns start with zero
    
          'do stuff with it here
        vFile = "c:\temp\Report_" & vName & ".pdf"
    
       docmd.TransferText acExportDelim ,"specName","qsQuery",vFile, true
    
    
    Next

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,759
    Well then use the query as a recordset, and logic as when to create a new file. A loop within a loop, with the inner loop being that field having the same value and the outer loop being until EOF.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    32
    Hi ranman256. I like your suggestion. I don't know much about list boxes. Never used them. The PHSOTO's are dynamic. Can list boxes handle that??

    Welshgasman, thanks for your suggestion. That's a bit over my head. That's why I found this code on my Google search hoping to get it to work. Do you have any links to examples you can share?

    Thank you both.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,759
    OK, I see two problems immediately with your code
    1. You are using the incorrect name for the recordset where you set the value of PHSOTO
    2. The ProductID is also not in your query sql.?

    Notice I put Option Explicit at the top. That should be in every module you have.

    Code:
    Option Compare Database
    Option Explicit
    
    
    Function ExportProducts() As Long
    'Purpose: Export a file for each product.
    Dim db As DAO.Database 'This database
    Dim rsProduct As DAO.Recordset 'Each product
    Dim strSql As String 'SQL statement
    Dim strFile As String 'Export file name
    Dim lngCount As Long 'Number of products
    Const strcPath = "C:\EmbDatabases\" 'Path to export to.
    Const strcQuery4Export = "selWIP" 'Name of query
    Const strcStub = "SELECT PHSOTO, PHSHNM, CHCASN " & _
    "FROM selWIP WHERE (PHSOTO = "
    Const strcTail = ") ORDER BY PHSOTO;"
    
    
    'Initialize
    Set db = CurrentDb()
    strSql = "SELECT DISTINCT PHSOTO FROM selWIP " & _
    "WHERE PHSOTO Is Not Null;"
    Set rsProduct = db.OpenRecordset(strSql)
    
    
    'Loop through distinct products, exporting each.
    Do While Not rsProduct.EOF
    strSql = strcStub & rsProduct!PHSOTO & strcTail
    db.QueryDefs(strcQuery4Export).SQL = strSql
    strFile = strcPath & rsProduct!ProductID & ".txt"
    DoCmd.TransferText acExportDelim, , strcQuery4Export, strFile
    lngCount = lngCount + 1
    rsProduct.MoveNext
    Loop
    
    
    'Clean up
    rsProduct.Close
    Set rsProduct = Nothing
    Set db = Nothing
    
    
    'Return count of products exported.
    ExportProducts = lngCount
    End Function
    Also before you try any sql strings, use Debug.Print to see it is correct, then comment out when you do get it correct.
    That should also show you your missing data fields.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,759
    I would also say that the code you are attempting to use, is doing what I initially suggested?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,104
    This function will work.

    Code:
    Option Compare Database
    Option Explicit
    
    
    Function ExportProducts() As Long
        'Purpose: Export a file for each product.
        Dim db As DAO.Database              'This database
        Dim rsName As DAO.Recordset
        Dim rsProduct As DAO.Recordset      'Each product
        Dim strSql As String                'SQL statement
        Dim strFile As String               'Export file name
        Dim lngCount As Long                'Number of products
        Dim sqlName As String
        Const strcPath = "C:\EmbDatabases\" 'Path to export to.
        Const strcQuery4Export = "selWIP"   'Name of query
        Const strcTail = ") ORDER BY PHSOTO;"
        Const strcStub = "SELECT PHSOTO, PHSHNM, CHCASN " & _
        "FROM tblWIP WHERE (PHSOTO = "
        'Initialize
        Set db = CurrentDb()
        
        sqlName = "SELECT DISTINCT PHSOTO,PHSHNM FROM tblWIP " & _
         " WHERE PHSOTO Is Not Null;"
        Set rsName = db.OpenRecordset(sqlName)
        rsName.MoveLast: rsName.MoveFirst
    
    
        'Loop through distinct products, exporting each.
        Do While Not rsName.EOF
            
        strSql = strcStub & rsName!PHSOTO & strcTail
        Debug.Print strSql
        db.QueryDefs(strcQuery4Export).SQL = strSql
        strFile = strcPath & rsName!phshnm & ".txt"
        DoCmd.TransferText acExportDelim, , strcQuery4Export, strFile
        lngCount = lngCount + 1
        rsName.MoveNext
        Loop
        
        'Clean up
        rsName.Close
        Set rsName = Nothing
        Set db = Nothing
        
        'Return count of products exported.
        ExportProducts = lngCount
        End Function

  10. #10
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    32
    Hi davegri. You suggestion worked great! Thank you.

    And thank you all for your help with my project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 06-24-2015, 08:22 AM
  2. Access Query - Export to multiple Excel files
    By WhosUsingMyName in forum Access
    Replies: 3
    Last Post: 05-22-2015, 03:53 PM
  3. Export one query to multiple Excel files
    By bliffer in forum Import/Export Data
    Replies: 3
    Last Post: 01-29-2014, 02:37 PM
  4. Replies: 4
    Last Post: 12-05-2013, 09:32 AM
  5. Replies: 11
    Last Post: 12-20-2012, 12:30 PM

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