Export to a format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet with row 1 as my header allof the other rows i need
exported to a file in the format below. This is critical and any help is
greatly appreciated

This is my spreadsheet layout. But there could be up to 8000 rows:
c_last_name c_first_name c_middle_name c_userid
Ables Donna F donna.ables
Lovelace Eric
eric.lovelace

I need to export this to a file in this format. Each row needs to be
enclosed in ( ) and each field in that row needs to be in " " with a comma
seperating each field.
( "ables" ,"donna" , "f" ,"donna.ables" )
( "lovelace" ,"eric" , "" ,"eric.lovelace" )


Thanks a million
 
One way:


Public Sub OutputQuotedCSVwithParens()
Const QSTR As String = """"
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String

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

If you're unfamiliar with macros, see David McRitchie's "Getting Started
with Macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Oops - that start's in Row 1. Change

For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)


to

For Each myRecord In Range("A2:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
 
Back
Top