Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

caryduck's Avatar
caryduck caryduck is offline
Junior Member with 2 posts.
THREAD STARTER
 
Join Date: Apr 2007
Experience: Intermediate
25-Apr-2007, 01:19 AM #1
Solved: exporting excel data into comma delimited ascii with "quotes"
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's Avatar
Member with 17,584 posts.
 
Join Date: Aug 2003
Location: New Zealand
25-Apr-2007, 02:35 AM #2
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's Avatar
Member with 4,693 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
25-Apr-2007, 08:54 AM #3
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's Avatar
caryduck caryduck is offline
Junior Member with 2 posts.
THREAD STARTER
 
Join Date: Apr 2007
Experience: Intermediate
26-Apr-2007, 01:24 AM #4
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
File Type: txt anouncer.txt (63 Bytes, 1365 views)
File Type: xls anouncer.xls (14.5 KB, 1089 views)

Last edited by caryduck; 26-Apr-2007 at 04:19 PM.. Reason: New attachement
Rollin_Again's Avatar
Member with 4,693 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
28-Apr-2007, 09:26 AM #5
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

Last edited by Rollin_Again; 28-Apr-2007 at 10:25 AM..
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑