Export Query As Text File Question

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Wayne
    #1

    Export Query As Text File Question

    I'm trying to automate the export of a query to a text file using
    code. If I export the query manually I get the exact result that I
    want i.e. If I select the query then choose File/Export from the menu
    bar, choose "Text" as the save format, and then select "fixed width"
    as the export format, I end up with a fixed width text file with no
    column headers.

    I've tried to automate the process using:

    DoCmd.OutputTo acOutputQuery, "qry_Test", acFormatTXT, "Test.txt",
    True

    This gives me the following unwanted issues. The column headers are
    exported, the fixed width columns are separated by a vertical line,
    and the rows are separated by dashes. Also the resulting text file
    appears on the screen. I just want it to save and not appear. What do
    I need to do to automate the process and end up with a text file that
    is formatted the same as when I use the manual method? i.e no headers,
    no separaters and a silent save to the text file.
    Tags: None
    • banem2@gmail.com
      #2
      Re: Export Query As Text File Question

      On Jul 2, 8:44 am, Wayne <cqdigi...@volc anomail.comwrot e:
      I'm trying to automate the export of a query to a text file using
      code.  If I export the query manually I get the exact result that I
      want i.e. If I select the query then choose File/Export from the menu
      bar, choose "Text" as the save format, and then select "fixed width"
      as the export format, I end up with a fixed width text file with no
      column headers.
      >
      I've tried to automate the process using:
      >
      DoCmd.OutputTo acOutputQuery, "qry_Test", acFormatTXT, "Test.txt",
      True
      >
      This gives me the following unwanted issues.  The column headers are
      exported, the fixed width columns are separated by a vertical line,
      and the rows are separated by dashes.  Also the resulting text file
      appears on the screen.  I just want it to save and not appear. What do
      I need to do to automate the process and end up with a text file that
      is formatted the same as when I use the manual method? i.e no headers,
      no separaters and a silent save to the text file.
      Hi,

      You can use following function (modified function of Joe Fellon, MVP)
      to export query in fixed width format without headers. Call the
      function with: ExportData ("qry_temp") . In my sample I have used table
      with 3 fields.

      Function ExportData(strE xportFile As String)
      Dim rs As Recordset
      Dim strData As String
      Dim intFileNum As Integer

      'get file handle and open for output
      intFileNum = FreeFile()

      'opens the disk file
      Open strExportFile For Output As #intFileNum

      'open the recordset
      Set rs = CurrentDb.OpenR ecordset("qry_T est", dbOpenSnapshot)
      'the numbered comments show the fixed width positions
      With rs
      Do Until .EOF
      'this will pad the end of the field with spaces
      strData = ![text1] & Space(20 - Len(![text1])) '1-20
      strData = strData & ![text2] & Space(20 - Len(![text2]))
      '21-40
      strData = strData & ![text3] & Space(20 - Len(![text3]))
      '41-60

      'write out to file
      Print #intFileNum, strData
      .MoveNext
      Loop
      End With

      Close #intFileNum
      rs.Close
      Set rs = Nothing
      End Function

      Regards,
      Branislav Mihaljev
      Microsoft Access MVP

        Comment

        • Salad
          #3
          Re: Export Query As Text File Question

          Wayne wrote:
          I'm trying to automate the export of a query to a text file using
          code. If I export the query manually I get the exact result that I
          want i.e. If I select the query then choose File/Export from the menu
          bar, choose "Text" as the save format, and then select "fixed width"
          as the export format, I end up with a fixed width text file with no
          column headers.
          >
          I've tried to automate the process using:
          >
          DoCmd.OutputTo acOutputQuery, "qry_Test", acFormatTXT, "Test.txt",
          True
          >
          This gives me the following unwanted issues. The column headers are
          exported, the fixed width columns are separated by a vertical line,
          and the rows are separated by dashes. Also the resulting text file
          appears on the screen. I just want it to save and not appear. What do
          I need to do to automate the process and end up with a text file that
          is formatted the same as when I use the manual method? i.e no headers,
          no separaters and a silent save to the text file.
          Instead of OutputTo you might want to consider TransferText. You can
          specify whether or not it has column headers and the type of delimiters.

          First, click on your query and select from the menu File/SaveAs and
          select external file and select Txt as the type. You will then be able
          to set the specification for the output. Be sure to click the Advanced
          button and save the specification. Once you have your specification
          designed you can use it in TransferText.

          Lambada
          http://www.youtube.com/watch?v=5AfTl5Vg73A

            Comment

            • Wayne
              #4
              Re: Export Query As Text File Question

              Thanks to both Branislav and Salad for your replies. For my
              particular situation at the moment, I'm going to use Salad's solution
              because for me it's simpler.

                Comment

                Previous template Next
                Working...