[caryduck]

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?

[kiwiguy]

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...

[Rollin_Again]

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

[caryduck]

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.

[Rollin_Again]

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.)

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