1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: exporting excel data into comma delimited ascii with "quotes"

Discussion in 'Business Applications' started by caryduck, Apr 25, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. caryduck

    caryduck Thread Starter

    Joined:
    Apr 25, 2007
    Messages:
    2
    I need to export data from an excel spreadsheet to be used by another program. This other program needs to the see the data in a .txt file that is comma delimited ascii. The values (or data in each cell) must have quotes around them to work in this other program, but I cannot figure out how to get quotes around the values when saving into .csv or other comma delimited formats. I only get data separated by commas.

    Any help?
     
  2. kiwiguy

    kiwiguy

    Joined:
    Aug 17, 2003
    Messages:
    17,584
    For some unhelpful advice, MS have the following:
    http://support.microsoft.com/kb/123183
    "Microsoft Excel does not have a menu command to automatically export data to a text file so that the text file is exported with both quotation marks and commas as delimiters.
    However, you can create this functionality in Excel by using a Microsoft Visual Basic for Applications (VBA) procedure."

    Hopefully a VBA expert will wander by...
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    Post a sample workbook and I'll write a macro to export to text with quotes and commas. How many rows of data are we talking about? Will the source workbook always have the same number of columns or will this vary?

    Regards,
    Rollin
     
  4. caryduck

    caryduck Thread Starter

    Joined:
    Apr 25, 2007
    Messages:
    2
    I have attached a sample excel sheet (only one participant) and what I need the .txt to look like. I am staging an event where I plan to put the partipant information into excel, save it eventually as a .txt, and then put that .txt file in the directory of the program that will see it.
    Might be to much info, but in the other program, by typing the bib # when the particpant finishes, that partipant's info shows up on the screen so that event announcer can read it to the crowd. There will certainly be less than 200 participants in the event as this is its first year.

    Thanks for the help.
     

    Attached Files:

  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    Here is code I wrote to export your Excel to text with quotes and delimited by commas. Check it out & let me know how it works for you. You only need to change the line Set fsoText = fso.CreateTextFile("C:\Test.txt", True) to reflect the true path of where you want the text file created. The code assumes that the first row of data will contain title headers that do not need to be written to the text file. If you need to include row 1 in the export just change the row number (2) to start in my code to whatever row you want to start from ( For i = 2 )

    Could you also tell me what does Column "I" (PR) represent? Currently when this value is written to the text file it is being written as a numeric value. I need to know what format to use when transferring to text (date, time, etc.)

    Code:
    Public Sub ExportText()
    
    Dim fso
    Dim aArray As Variant
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoText = fso.CreateTextFile("C:\Test.txt", True)
    
    vCol = Left(Columns(Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious).Column).Address(0, 0), 2 + (Cells.Find(What:="*", After:=[A1], _
    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column < 27))
    
    For i = 2 To Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    ReDim aArray(1 To 1, 1 To Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious).Column)
    
    aArray = Range("A" & i & ":" & vCol & i).Value
    
    For x = 1 To Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious).Column
    
    If vString = "" Then
    vString = Chr(34) & aArray(1, x) & Chr(34)
    Else
    vString = vString & " , " & Chr(34) & aArray(1, x) & Chr(34)
    End If
    
    Next x
    
    fsoText.WriteLine (vString)
    vString = ""
    
    Next i
    
    MsgBox ("TEXT EXPORT COMPLETE")
    fsoText.Close
    
    Set fso = nothing
    set aArray = nothing
    
    End Sub
    Regards,
    Rollin
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/566334