VBA code to Export selection as .csv file (with specified seperator).

[NessPJ]

Hey Guys,

For a file i made i am looking for a piece of code which allows me to export a selection of cells to a .csv file directly.

I have a selection of cells, for example:
A10:AA5000 filled with VLOOKUP formulas.
I would like the selection to be made only for the cells that contain output (cells that do not contain output are shown as blank by the formula).
So for example only selection A10:AA3499 are filled with values (the rest below them do not show results from the formula).

I should also be able to specifiy the seperator used.

Right now i have a macro that copies the selection to a blank file and then saves it as a .csv this results in a csv file with the wrong seperator.
I would very much like to solve this by using some code rather then being dependant on the language settings of Windows/Office.

Thanks in advance for any help.

 

[kpark91]

I am a bit confused to why you would only want the code to run on cells that contain output
because this would mean the result could be out of sync with other data in terms of columns.

For example, suppose your data looks like this:

col1	col2	col3
1	a	b
2		c
3	d

<tbody>
</tbody>

when you export this as CSV as you've mentioned,it would look like

col1, col2, col3
1, a, b
2, c
3, d

Then when you've imported the CSV file again for re-evaluation, it would look something like:

col1	col2	col3
1	a	b
2	c
3	d

<tbody>
</tbody>

which is totally out its original column!
Is this okay?

 

[hippiehacker]

something like this?

Sub NessPJ()
Sheets(1).Range("A10:AA5000").Select
Selection.SpecialCells(xlCellTypeFormulas, xlNumbers).Select
Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    ActiveWorkbook.SaveAs Filename:= _
    "C:\temp\test.csv" _
    , FileFormat:=xlCSV, CreateBackup:=False
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

 

[NessPJ]

@kpark91:
You are right.
What i meant to say was, that the code should make a selection from starting point to the last row that contains input.
This is to avoid empty lines with only seperators in the bottom of the csv file.

@hippiehacker:
Thanks, but this looks like the code i made before i ran into my beforementioned issues.
The selection will be fixed on A10:AA5500 this way and there is no way to specifiy a seperator.

 

[hippiehacker]

with line

Selection.SpecialCells(xlCellTypeFormulas, xlNumbers).Select

you select only the cells that return a number from the formula

if you want to export as CSV it will always be comma as separator if you want to export with different
separator you have to do some different tasks and create your own csv export see example

http://www.****s-blog.com/archives/2004/11/09/roll-your-own-csv/
</pre>

 

[Andrew Poulsom]

Here's the equivalent of hippiehacker's link without the censorship of the author's christian name:

Daily Dose of Excel » Blog Archive » Roll Your Own CSV

 

[NessPJ]

with line

Selection.SpecialCells(xlCellTypeFormulas, xlNumbers).Select

you select only the cells that return a number from the formula

if you want to export as CSV it will always be comma as separator if you want to export with different
separator you have to do some different tasks and create your own csv export see example

http://www.****s-blog.com/archives/2004/11/09/roll-your-own-csv/

Thanks! But will this resolve the problem kpark91 mentioned regarding the cells in between the selection?
I would like to make a selection until an entire row without numbers from a formula is found.

And thanks for the link guys, i will check it out asap.

 

[NessPJ]

Hey guys,

I just tried to use the code from the link but its giving me compile errors straight away in the Excel VBA Editor:

Sub CreateCSV()
 
    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
    
    Const sDELIM As String = “,”
    
    'Required width of columns
   vaColPad = Array(0, 0, 6, 0, 4)
    i = LBound(vaColPad)
   
    'Open a text file to write
   sFname = “C: MyCsv.csv”
    lFnum = FreeFile
   
    Open sFname For Output As lFnum
   
    'Loop through the rows
   For Each rRow In Sheet1.UsedRange.Rows
        '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 & sDELIM
            Else
                sOutput = sOutput & rCell.Value & sDELIM
            End If
            i = i + 1
        Next rCell
        'remove the last comma
       sOutput = Left(sOutput, Len(sOutput) – Len(sDELIM))
       
        'write to the file and reinitialize the variables
       Print #lFnum, sOutput
        sOutput = “”
        i = LBound(vaColPad)
    Next rRow
   
    'Close the file
   Close lFnum
   
End Sub

 

[Andrew Poulsom]

You need to replace those ” with ".

 

[NessPJ]

Hi andrew,

Thanks again. I overlooked the funny looking quotes.
I turned a few lines of code from the VBA into comments so they are not executed.
I have the code working almost exactly the way i want it to now, except for the fact that after the second line, every line has an empty line in the output file.

So it looks like this:

Header1;header2;header3;header4;header5;header6;
Value1;value2;value3;value4;value5;value6;

Value1;value2;value3;value4;value5;value6;

Value1;value2;value3;value4;value5;value6;

Value1;value2;value3;value4;value5;value6;

While i want it to look like this:

Header1;header2;header3;header4;header5;header6;
Value1;value2;value3;value4;value5;value6;
Value1;value2;value3;value4;value5;value6;
Value1;value2;value3;value4;value5;value6;
Value1;value2;value3;value4;value5;value6;

Currently, the code looks like this:

Sub CreateCSV()
 
    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
    
    Const sDELIM As String = ";"
    
    'Required width of columns
   vaColPad = Array(0, 0, 6, 0, 4)
    i = LBound(vaColPad)
   
    'Open a text file to write
   sFname = "C:\Test.csv"
    lFnum = FreeFile
   
    Open sFname For Output As lFnum
   
    'Loop through the rows
   For Each rRow In Sheet4.UsedRange.Rows
        '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 & sDELIM
            'Else
                sOutput = sOutput & rCell.Value
                '& sDELIM (this value generates an extra delimiter at the end of every line).
            'End If
            'i = i + 1
        Next rCell
        'remove the last comma
       sOutput = Left(sOutput, Len(sOutput))
       
        'write to the file and reinitialize the variables
       Print #lFnum, sOutput
        sOutput = ""
        i = LBound(vaColPad)
    Next rRow
   
    'Close the file
   Close lFnum
   
End Sub