Export to CSV with a Variable Amount of Columns

[jabce85]

I have been searching for quite a while now for an answer to this problem with no success. This forum seems like the best place to find an answer. I'm looking for a macro to take the following input and export it to a csv file to look like what I have listed.

INPUT-----

5 5 5 5 5 5 5
5 5 5 5
5 5 5 5 5 5
5 5 5 5 5 5 5 5 5 5
5 5 5

CSV OUTPUT-------

5,5,5,5,5,5,5,
5,5,5,5,
5,5,5,5,5,5,
5,5,5,5,5,5,5,5,5,5,
5,5,5,

If at all possible, I need it to be able to keep the formating that is in the excel file when it exports to the CSV file as well.

 

[Misca]

How about

Sub SaveAsCSV()
    
Dim Path As String
Dim FName As String

Path = ThisWorkbook.Path & "\" 'Edit to match
FName = "MyCSVWorkbook " & Date & ".csv"  'Edit to match

    ActiveSheet.SaveAs Filename:=Path & FName, FileFormat:=xlCSV, _
        CreateBackup:=False
End Sub

 

[jabce85]

Nope, it's not this simple. This will return:

5,5,5,5,5,5,5,5,5
5,5,5,,,,,,
5,5,5,5,5,5,,,
5,,,,,,,,,

I'm pretty sure I will have to export line by line using a freefile, but I just can't come up with code to remove the excess commas.

 

[Misca]

I believe you have to fake a little:

Sub SaveAstxt()
    
Dim Path As String
Dim FName As String

Path = ThisWorkbook.Path & "\" 'Edit to match
FName = "MyTXTCSVWorkbook " & Date & ".csv"

ActiveWorkbook.SaveAs Filename:=Path & FName, FileFormat:=xlUnicodeText, CreateBackup:=False
End Sub

This saves the workbook as a txt file with the .csv extension.

 

[jabce85]

Sorry, this isn't what I'm looking for either; this is tab delimited. It's closer though.

 

[Weaver]

Try this:

Sub export2CSV()
    Dim dataSheet, fStr, fs, ts, outputStr
    Set fs = CreateObject("Scripting.FileSystemObject")
    fStr = ActiveWorkbook.Path & "\test1.csv"
    fs.CreateTextFile fStr
    Set dataSheet = Sheets("sheet1")
    Set destSheet = ActiveSheet
    Set f = fs.GetFile(fStr)
    Set ts = f.OpenAsTextStream(2, 0)
    For x = 1 To dataSheet.Cells(Rows.Count, 1).End(xlUp).Row
        outputStr = ""
        For y = 1 To dataSheet.Cells(x, Columns.Count).End(xlToLeft).Column
            outputStr = outputStr & dataSheet.Cells(x, y) & ","
        Next y
        ts.Write outputStr & vbCrLf
    Next x
    ts.Close
End Sub

It'll create test1.csv in the same folder as your original file fitting the pattern you're after. You might struggle with the formatting (unless it's text formatting) since csv files don't store cell formats.

If you've got number formats and the like in the cells (such as 17/06/2010 or £12.34), replace

outputStr = outputStr & dataSheet.Cells(x, y) & ","

with

outputStr = outputStr & dataSheet.Cells(x, y).text & ","

HTH

 

[Weaver]

PS I did try a solution around Miscas, which involved building comma separated strings based on each row into a single cell on another sheet and then saving this as .csv but that put quotes around each line.

 

[jabce85]

Weaver,

Your code is working to some extent; however, there are a couple problems.

1) As you said, the formatting is an issue that I'll have to look into.
2) I have some fields with no data or functions that aren't relevant in all circumstances that are being skipped over.

I forgot to mention I do know the specific number of columns per row; would there be a way to force it to export those specific number of columns per row?

 

[Weaver]

Where is it missing data from? From further to the right or further down?

What type of formats are you trying to store in the csv?

 

[jabce85]

I can handle the formatting problems, but see below for what the issue with the values not showing looks like.

OUTPUT NEEDED-

5,5,,,5,function value,5,
5,5,5,
5,,,,,5,function value, function value, function value,
5,5,5,5,5,5,,,5,


As I said, the functions don't always contain values, sometimes I need it to fill the space with a no value ( ,, ). The above is just an example and not an actual representation of my output.