Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

VB code to export table to txt file

Discussion in 'Information Technology' started by Anakin Moonwalker, Nov 17, 2005.

  1. Anakin Moonwalker

    Anakin Moonwalker
    Expand Collapse
    Guest

    I'm using this code to export a table to a txt file, but the values of the
    table become enclosed in quotation marks, which I don't want to happen:

    DoCmd.TransferText acExportDelim, , "TableA", strSaveFileName, False

    Does anybody here have a better code to export a table to a txt file (where
    the values do not get enclosed in quotes, but are delimited by commas).
     
  2. Loading...

    Similar Threads Forum Date
    Introducing The Spn Code Of Forum Etiquette Announcements Feb 20, 2016
    UK Sikh 'code of silence' on sexual grooming Breaking News Sep 2, 2013
    Can a Dress Code Bar Ethnic and Religious Attire Based on Customer Preferences in the US? Questions and Answers May 3, 2011
    Pacific Code Exempts Religious Drivers (Fiji) Breaking News Feb 16, 2011
    Controversial Gurdwaras 'Breaking the Sikh Code' on Meat and Alcohol Hard Talk Dec 2, 2010

  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Anakin,

    Go through the export process manually, via the File|Export menu. When
    you get to the last screen of the Export Wizard, click the Advanced
    button, and save your setup as an Export Specification. Then, put the
    name of this Specification in your code, in the SpecificationName
    argument of the TransferText method.

    --
    Steve Schapel, Microsoft Access MVP

    Anakin Moonwalker wrote:
    > I'm using this code to export a table to a txt file, but the values of the
    > table become enclosed in quotation marks, which I don't want to happen:
    >
    > DoCmd.TransferText acExportDelim, , "TableA", strSaveFileName, False
    >
    > Does anybody here have a better code to export a table to a txt file (where
    > the values do not get enclosed in quotes, but are delimited by commas).
    >
     
  4. Anakin Moonwalker

    Anakin Moonwalker
    Expand Collapse
    Guest

    I found the answer here:
    http://www.tech-archive.net/Archive...lic.access.externaldata/2005-05/msg00585.html


    Public Sub ExportDelim(strTable As String, strExportFile As String,
    strDelimiter As String, Optional blnHeader As Boolean)

    'strTable is the table or query name
    'strExportFile is the full path and name of file to export to
    'strDelimiter is the field deliminator to use like Chr(9) for tab or
    Chr(44) for comma or ??

    Dim fld As Field
    Dim varData As Variant
    Dim rs As Recordset
    Dim intFileNum As Integer

    'set recordset on table or query
    Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)

    'get file handle and open for output
    intFileNum = FreeFile()
    Open strExportFile For Output As #intFileNum

    If blnHeader Then
    'output the header row if requested
    varData = ""
    For Each fld In rs.Fields 'traverse the fields collection
    varData = varData & fld.Name & strDelimiter
    Next

    'remove extra last strDelimiter
    varData = Left(varData, Len(varData) - 1)

    'write out the header row
    Print #intFileNum, varData
    End If

    'now your data
    Do While Not rs.EOF
    varData = ""
    'concatenate the data row
    For Each fld In rs.Fields
    varData = varData & fld.Value & strDelimiter
    Next

    'remove extra last strDelimiter
    varData = Left(varData, Len(varData) - 1)

    'write out data row
    Print #intFileNum, varData

    rs.MoveNext
    Loop

    Close #intFileNum
    rs.Close
    Set rs = Nothing
    End Sub
     
  5. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Anakin,

    Fair enough. But I would say the suggestion I gave before would be a
    lot simpler.

    --
    Steve Schapel, Microsoft Access MVP


    Anakin Moonwalker wrote:
    > I found the answer here:
    > http://www.tech-archive.net/Archive...lic.access.externaldata/2005-05/msg00585.html
    >
    >
    > Public Sub ExportDelim(strTable As String, strExportFile As String,
    > strDelimiter As String, Optional blnHeader As Boolean)
    >
    > 'strTable is the table or query name
    > 'strExportFile is the full path and name of file to export to
    > 'strDelimiter is the field deliminator to use like Chr(9) for tab or
    > Chr(44) for comma or ??
    >
    > Dim fld As Field
    > Dim varData As Variant
    > Dim rs As Recordset
    > Dim intFileNum As Integer
    >
    > 'set recordset on table or query
    > Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)
    >
    > 'get file handle and open for output
    > intFileNum = FreeFile()
    > Open strExportFile For Output As #intFileNum
    >
    > If blnHeader Then
    > 'output the header row if requested
    > varData = ""
    > For Each fld In rs.Fields 'traverse the fields collection
    > varData = varData & fld.Name & strDelimiter
    > Next
    >
    > 'remove extra last strDelimiter
    > varData = Left(varData, Len(varData) - 1)
    >
    > 'write out the header row
    > Print #intFileNum, varData
    > End If
    >
    > 'now your data
    > Do While Not rs.EOF
    > varData = ""
    > 'concatenate the data row
    > For Each fld In rs.Fields
    > varData = varData & fld.Value & strDelimiter
    > Next
    >
    > 'remove extra last strDelimiter
    > varData = Left(varData, Len(varData) - 1)
    >
    > 'write out data row
    > Print #intFileNum, varData
    >
    > rs.MoveNext
    > Loop
    >
    > Close #intFileNum
    > rs.Close
    > Set rs = Nothing
    > End Sub
    >
    >
     
  6. (PeteCresswell)

    (PeteCresswell)
    Expand Collapse
    Guest

    Per Steve Schapel:
    >Then, put the
    >name of this Specification in your code, in the SpecificationName
    >argument of the TransferText method.


    But be sure to document the heck out of the resulting specification bc they're
    pretty much hidden once you've created one. Where it will come back and bite
    you is if/when the app becomes corrupted and you build a new version by
    exporting all objects. You'll tend to forget about the specs.
    --
    PeteCresswell
     
  7. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    (PeteCresswell) wrote:
    > exporting all objects. You'll tend to forget about the specs.


    In my experience, you only forget this once :)


    --
    Steve Schapel, Microsoft Access MVP
     

Share This Page