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.

 

[Andrew Poulsom]

Please post a sample of your data.

 

[NessPJ]

Hi Andrew,

I uploaded the file here: Zippyshare.com - NDT Import v2.xlsm

The Excel file consists of 4 sheets. The 4th sheet called 'Export' is used by the code to generate the csv file.

 

[Andrew Poulsom]

I had to change sFname slightly and Sheet4 to Sheet5. Try:

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:\TEMP\Test.csv"
    lFnum = FreeFile
   
    Open sFname For Output As lFnum
   
    'Loop through the rows
    For Each rRow In Sheet5.UsedRange.Rows
        'Loop through the cells in the rows
        If Len(rRow.Cells(1, 1).Value) > 0 Then
            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) - 1)
       
            'write to the file and reinitialize the variables
            Print #lFnum, sOutput
            sOutput = ""
            'i = LBound(vaColPad)
        End If
    Next rRow
   
    'Close the file
    Close lFnum
   
End Sub

 

[NessPJ]

Hi Andrew,

Thanks for all the help. Sheet 5 in the file was actually a mere text dump (an example of how the output should become after we were finished processing masterdata in the previous 4 sheets).
Do you think you can get rid of the empty lines by using Sheet4 as input?

 

[Andrew Poulsom]

There wasn't a Sheet4 in your sample workbook.

 

[NessPJ]

Hello andrew,

You were absolutely right. I mistook the logical count of the sheets for the Sheet# definition inside the VB editor.
The code is working fine now (and my previous sheet referral, was simply pointing to the wrong sheet in the first place).
Thanks a lot for your help!

 

[CynthiaKelly_]

I have a problem that is very similar. I have Excel2010 workbooks for each month of the year. Each workbook contains a worksheet for each day of the month. Each worksheet is formatted the same and has a column with the date on it. Ultimately, I need to create either a single file that contains all the data for the month, or multiple files (one for each day). I need to export a range of cells from each worksheet to tab-delimited or comma-delimited file(s). In my mind, there should be a way to loop through all the "daily" worksheets and export the range (probably another row/column loop structure). Please HELP!

 

[Andrew Poulsom]

Look here:

Merge data from all workbooks in a folder in a txt file

 

[CynthiaKelly_]

Thank you so very much!! I'm reviewing the code in the link to try to wrap my head around it--I may have some questions! SO, HeadsUp!

 

[rpaulson]

Cynthia.

This code will output all data on all sheets to a text file.

Sub outputtotextfile()
wline = ""
For Each ws In Worksheets

With ws
For r = 1 To .Range("A65536").End(xlUp).Row
    lcol = .Cells(r, 256).End(xlToLeft).Column
    wline = wline & Join(Application.Transpose(Application.Transpose(.Range("A" & r).Resize(, lcol))), ",") & vbNewLine
Next r
End With
    
Next ws


    Open "C:\test2.csv" For Output As #1 'Replaces existing file
    Print #1, wline
    Close #1

End Sub

Hth,
Ross