quotation marks before and after each input

I

iamalex

I have an excel document with about 6 columns and various
rows under each column.
I need to change it to csv comma delimited, which is no problem, but I
need to add quotations around each and every item for final text
output.

any help would be appreciated.

alex
 
J

JE McGimpsey

note that, as Tom points out, the double quotes will only be placed
around Text strings. Dates will get #'s around them and numbers will
have only the comma delimiters.

If "each and every item" includes dates or numbers, this won't work.
 
I

iamalex

First off , have not worked with macros.
Made a macro, and placed the code you referenced in in- in vb an
saved. Set CTRL+Shift+Q

Hit the CTRL+SHIFT+Q in excel doc, nothing there. and saved as
csv comma delimited, nothing there.

I am using excel 2003 for ref.

Am I doing something wrong.

Thanks
 
I

iamalex

JE,

I did get it to work and thanks again.

Can you tell me how to add the double quotes on the end column.
If I have a blank column in the the middle it will put the double
quotes, but my last column is currently blank and I need the double
quotes there.

Also, If I have a dollar sign, such as $45.00, it adds and extra
space after it and before the quotes such as $45.00 "

Can you help me on these???

Thanks.
 
J

JE McGimpsey

This modification will output a fixed field text file with quotes around
each item. Adjust NUMFIELDS as necessary. The space after $45.00 comes
from your formatting (e.g., Currency formats as $0.00_(, to allow
positive and negative numbers to line up). Using RTrim removes any
trailing spaces:

Public Sub OutputQuotedCSV()
Const QSTR As String = """"
Const NUMFIELDS As Long = 10
Dim myRecord As Range
Dim myField As Range
Dim sOut As String

Open "File1.txt" For Output As #1
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In myRecord.Resize(, NUMFIELDS)
sOut = sOut & "," & QSTR & _
Replace(RTrim(myField.Text), QSTR, QSTR & QSTR) & QSTR
Next myField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #1
End Sub
 
Top