Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection saved as CSV with string delimiter
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection saved as CSV with string delimiter
On Tue, 25 Mar 2008 19:37:00 -0700 (PDT), Vlad
wrote: 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/arch...-your-own-csv/ You could use the code there with some modifications. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection saved as CSV with string delimiter
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection saved as CSV with string delimiter
On Wed, 26 Mar 2008 08:48:15 -0700 (PDT), Vlad
wrote: 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. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection saved as CSV with string delimiter
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Parse a string; Delimiter is any Operator | Excel Programming | |||
Sum / count data from text string with delimiter | Excel Worksheet Functions | |||
Storing selection & using the saved selection adress later | Excel Programming | |||
find and remove a string of a cell value with comma as delimiter | Excel Discussion (Misc queries) | |||
running a macro saved as a string | Excel Programming |