quotation marks before and after each input

  • Thread starter Thread starter iamalex
  • Start date Start date
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
 
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.
 
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
 
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.
 
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
 
Back
Top