Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003

    Unanswered: Save query results as txt file

    Hi all,

    Any one know the code to save a query result, as a text file once a user has clicked a button?



  2. #2
    Join Date
    Oct 2003
    Boston, MA
    I forgot where I got this so apologies for no attribution.

    Put this line in your button's code, after the query is created:

    Call make_merge_txt(sqltxt)

    And save these two modules (make_merge_txt and add_quotes)

    Sub make_merge_txt(sqltxt As String)

    ' build our merge file, and write a "csv" file to disk

    Dim OneField As Field
    Dim strFields As String
    Dim strData As String
    Dim intFile As Integer
    Dim strOutFile As String ' csv file output name

    Set rs1 = db.OpenRecordset(sqltxt)

    ' build the merge file.
    ' build the first lines of fields

    strFields = ""
    For x = 0 To rs1.Fields.Count - 1
    If strFields <> "" Then strFields = strFields & ","
    strFields = strFields & add_quotes(rs1.Fields(x).name)
    Next x

    ' build the merge.txt file
    strOutFile = "name_of_your_txt_file"

    'delete the out file if there
    Kill strOutFile

    ' now open file...
    intFile = FreeFile()
    Open strOutFile For Output As intFile
    Print #intFile, strFields

    ' output all data
    Do While Not rs1.EOF

    strData = "" ' one line of data for csv file
    For x = 0 To rs1.Fields.Count - 1
    If strData <> "" Then strData = strData & ","
    strData = strData & add_quotes(rs1(rs1.Fields(x).name))
    Next x

    Print #intFile, strData


    Close intFile

    End Sub

    Function add_quotes(vText As Variant) As String
    'puts vtext into double quotes

    add_quotes = Chr$(34) & vText & Chr$(34)

    End Function

  3. #3
    Join Date
    Oct 2003

    Re: Save query results as txt file

    Originally posted by 130213
    Hi all,

    Any one know the code to save a query result, as a text file once a user has clicked a button?


    Here is an 11 line solution. Granted this looks very long, but it is only because I included a lot of remarks about each step, and the fact that I chose a large table to work with. Should have picked something smaller to show you and example with, but this is a real part of one of my databases. This solution creates a temporary table from a query, exports it as a comma delimited text file, then deltes the temp table. Again, start to finish, only 11 lines of code (without remarks or error handling).

    Private Sub CommandExportAgentInfo_Click()

    Dim spec, tbl, path, filename, s As String
    Dim db As Object

    'Create file export spec by doing a manual export, clicking the Advanced button, and after
    'getting all you options the way you like them, click the SaveAs button
    'and give your spec an easy to remember and relevant name

    Set db = CurrentDb
    'Need to utilize the Execute SQL
    spec = "tblAgent"
    'The spec I set up is to save a table as a comma delimited, with no quotes around strings
    tbl = "tblTempExport"
    'Pretty self explanitory, set up a variable to hold the name of the table you wish to export
    path = "C:\Temp\"
    'Again just a variable to hold the destination directory
    filename = "tblAgent.txt"
    'Or what ever you wish your filename to be
    'My suggestion is to have these come from text boxes on a form. That way the person
    'exporting the table can select their own destination and filename.

    s = "SELECT tblAgentInfo.intID, tblAgentInfo.AgentID, tblAgentInfo.AgentNameL, " & _
    "tblAgentInfo.AgentNameF, tblAgentInfo.SSN, tblAgentInfo.DOB, tblAgentInfo.StatusID, " & _
    "tblAgentInfo.HireDate, tblAgentInfo.TermDate, tblAgentInfo.SalesID, tblAgentInfo.CableData, " & _
    "tblAgentInfo.CableDataPassword, tblAgentInfo.KBLogin, tblAgentInfo.KBPassword, " & _
    "tblAgentInfo.NTLogin, tblAgentInfo.NTPassword, tblAgentInfo.ISPLogin, tblAgentInfo.ISPPassword, " & _
    "tblAgentInfo.intSupvID INTO " & tbl & " FROM tblAgentInfo;"
    'Build your query then switch to SQL view, copy from there, and paste into VBA
    'Also, using the & _ line continuation makes for an easier read

    db.Execute s
    'Found this little command here in DBForums. Using this you do not have to
    'use the docmd.setwarnings False because Execute does not generate the messages
    'like "you are about to create a table, you are about to insert 50 rows, etc...

    DoCmd.TransferText acExportDelim, spec, tbl, path & filename
    'The DoCmd.TransferText has several options for importing, exporting and linking
    'various file and types and you can do delimited or fixed.

    DoCmd.DeleteObject acTable, tbl
    'Delete the temporary table

    End Sub

    I hope this helps you out,
    Last edited by Rood67; 10-16-03 at 01:37.

  4. #4
    Join Date
    Oct 2003
    Provided Answers: 1
    Hello there,
    you can use the

    DoCmd.TransferText acExportDelim,,"SELECT * FROM AnyTableName", "pathExportFileName.txt", True

    where pathExportFileName.txt is the path&FileName with extension txt

    True means the first line should contain the fields names

    Good luck

  5. #5
    Join Date
    Oct 2003

    Re: Save query results as txt file

    Thankyou all for your contributions, I will try them out....


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts