Selection saved as CSV with string delimiter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vlad - 26 Mar 2008 03:37 GMT

Has anyone got a routine that will save the selected text as csv with
a string delimiter.

I know how to do it without a string delimiter but is there a way with
a string delimiter?

TIA

Reply to this Message

Dick Kusleika - 26 Mar 2008 04:11 GMT

>Has anyone got a routine that will save the selected text as csv with
>a string delimiter.
>
>I know how to do it without a string delimiter but is there a way with
>a string delimiter?

http://www.dailydoseofexcel.com/archives/2004/11/09/roll-your-own-csv/

You could use the code there with some modifications.

Signature

Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Reply to this Message

Vlad - 26 Mar 2008 16:48 GMT

Thanks Dick for the suggestion.

I had already tried to modify the piece of code from your site but I
end up with all the information in the first column - essentially the
data is in 1d not 2d.

Here is what I had tried - any suggestions?

Sub test_RNG2CSV_Daily()
Dim sFilename As String

sFilename = "C:\Documents\Personal\CV\db\Out_1.csv"
RNG2CSV_Daily sFilename

End Sub
Sub RNG2CSV_Daily(sFilename As String)
Dim rCell As Range
Dim rRow As Range
Dim vaColPad As Variant
Dim i As Long
Dim sOutput As String
Dim sFname As String, lFnum As Long

'Required width of columns
vaColPad = Array(0, 0, 0, 0, 4)
i = LBound(vaColPad)

'Open a text file to write
sFname = "C:\Documents\Personal\CV\db\Out_2.csv"
lFnum = FreeFile

Open sFname For Output As lFnum
'Dim rRange As Range: Set rRange = Worksheets("CV List -
v1.0").UsedRange.Rows
Dim rRange As Range: Set rRange = Selection
'Loop through the rows
' For Each rRow In Worksheets("Sheet3").UsedRange.Rows
For Each rRow In rRange
'Loop through the cells in the rows

For Each rCell In rRow.Cells
'If the cell value is less than required, then pad
'it with zeros, else just use the cell value
If Len(rCell.Value) < vaColPad(i) Then
sOutput = sOutput & Application.Rept(0, _
vaColPad(i) -
Len(rCell.Value)) & rCell.Value & ","
Else
sOutput = sOutput & rCell.Value & ","
End If
i = i + 1
Next rCell
'remove the last comma
sOutput = Left(sOutput, Len(sOutput) - 1)

'write to the file and reinitialize the variables
Print #lFnum, sOutput
sOutput = ""
i = LBound(vaColPad)
Next rRow

'Close the file
Close lFnum
End Sub

Reply to this Message

Dick Kusleika - 26 Mar 2008 19:58 GMT

>Thanks Dick for the suggestion.
>
>I had already tried to modify the piece of code from your site but I
>end up with all the information in the first column - essentially the
>data is in 1d not 2d.

Give a short sample of what the data in the range will be, what you want the
delimeter to be, and what you want the output file to look like.

Signature

Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Reply to this Message

Vlad - 27 Mar 2008 02:19 GMT

> Give a short sample of what the data in the range will be, what you want the
> delimeter to be, and what you want the output file to look like.
> --
> Dick Kusleika
> Microsoft MVP-Excelhttp://www.dailydoseofexcel.com

I was wanting to use a comma as the delimiter and a double quote as
the string delimiter.

I've actually managed to apply the same principles as your my original
code and it works but it seems quite bloated and I'm sure there's a
more elegant way to do it. I'm more confused by why my previous
suggestion wouldn't work with the following data:-

Business Relationship Manager,    Portfolio Manager,    Project Manager,
Process Manager
Demand Manager,    Clarity StudioTM,    Resource Planner,    Project Manager
IT Financial Manager,    Demand Manager,    Schedule Connect,    Resource
Planner
IT Portfolio Manager,    Financial Manager,    Service Connect,    Schedule
Connect
Open Workbench,    Process Manager,    Portfolio Manager,    Service Connect
Process Manager,    Project Manager,    Clarity StudioTM,    Portfolio Manager
Proejct Manager,    Resource Planner,    Demand Manager,    Clarity StudioTM
Project Financial Manager,    Schedule Connect,    Financial Manager,    Demand
Manager
Project Portfolio Manager,    Service Connect,    Process Manager,    Financial
Manager
Resource Manager,    Portfolio Manager,    Project Manager,    Process Manager

If you try this data with my original version all the entries end up
in one column. With my version it ends up as

"Business Relationship Manager","Portfolio Manager","Project
Manager","Process Manager"
"Demand Manager","Clarity StudioTM","Resource Planner","Project
Manager"
"IT Financial Manager","Demand Manager","Schedule Connect","Resource
Planner"
"IT Portfolio Manager","Financial Manager","Service Connect","Schedule
Connect"
"Open Workbench","Process Manager","Portfolio Manager","Service
Connect"
"Process Manager","Project Manager","Clarity StudioTM","Portfolio
Manager"
"Proejct Manager","Resource Planner","Demand Manager","Clarity
StudioTM"
"Project Financial Manager","Schedule Connect","Financial
Manager","Demand Manager"
"Project Portfolio Manager","Service Connect","Process
Manager","Financial Manager"
"Resource Manager","Portfolio Manager","Project Manager","Process
Manager"

Here is my final solution - the comments should explain how it works:-

Sub RNG2CSV(sWorkSheet As String, rng As Range, sFilename As String)
'sWorkSheet : Name of worksheet containing range as string
'rng : Range as range object of range to export
'sFilename : Full path to the CSV file to be exported
Dim StringDelimiter As String: StringDelimiter = """"
Dim sOutput As String
Dim sFname As String, lFnum As Long
Dim lRowF As Long: lRowF = rng.Row
Dim lRowL As Long: lRowL = lRowF + rng.Rows.Count - 1
Dim lColF As Long: lColF = rng.Column
Dim lColL As Long: lColL = lColF + rng.Columns.Count
Dim r As Long, c As Long

'Open a text file to write
sFname = sFilename
lFnum = FreeFile
Open sFname For Output As lFnum

'Loop through the rows
Dim ws As Worksheet: Set ws = Worksheets(sWorkSheet)
With ws

For r = lRowF To lRowL

'Loop through the cells in the rows
For c = lColF To lColL
If Len(ws.Cells(r, c)) = 0 Then
sOutput = sOutput & ","
Else
sOutput = sOutput & "," & StringDelimiter &
ws.Cells(r, c) & StringDelimiter
End If
Next c

'remove the last comma
sOutput = Left(sOutput, Len(sOutput) - 1)
sOutput = Right(sOutput, Len(sOutput) - 1)

'write to the file and reinitialize the variables
Print #lFnum, sOutput
sOutput = ""
' i = LBound(vaColPad)
Next r
End With

'Close the file
Close lFnum
End Sub

Reply to this Message