Create new csv files for some sheets of my workbook

[mark84]

Hi everybody,
I have a workbook with 10 sheets named Lom mar.xlsm.
I need a vba code to create a new .csv file for the Workbook sheets named "AX", "BY", "CZ", "DU" (a csv for "AX" sheet, a csv for "BY" sheet, a csv for "CZ" sheet and a csv for "DU" sheet).
All these new csv files should be saved in the same path of the workbook (C:\Users\xxxx\Desktop\Lom mar)
Every new csv should have the same name of the original sheet (AX.csv, BY.csv, .....) and for each csv I need to delete the first row of the original sheet (header).

Could you help me please?
Thanks you so much!

Regards

Marco

 

[Joe4]

You should never really use Excel to view the contents of a CSV file, as Excel performs its own automated conversions on the data as it opens the file (and you cannot control that).
If you truly want to see what the cotents of your CSV file, it is best to use a text editor like NotePad to view it.

Note that "CSV" stands for "comma separated value", so it is expected that commas are the delimiters between the fields, unless you are using different regional settings that use the semi-colon instead of the commas to delimit the fields.

If you are seeing everything all in one column, it tells me that most likely one of two things is going on:
1. Your Regional Settings are set to use the semi-colon as your delimter
2. You have text qualifiers of double-quotes at the very beginning and end of each row. Note that everything in between the text qualifiers is treated as literal text. So any commas found between them are treated as text and not delimiters.

Regarding the second item, this is easy to see if you open the CSV file in NotePad.
Can you open it in NotePad and paste the first few rows of data here, exactly as it appears?

 

[mark84]

I solved the problem.
I tried to change the last part of the vba code, where I've changed "," with ";" (the part in blue color).
Now it works perfect, but I need the last suggestion: how can I set the columns L and M of every csv in data format (gg.mm.aaaa)?

Thanks so much!

Sub EachSheetAsCSV()
Dim F%, Rw As Range
Dim V As Variant
V = Array("AX", "BY", "CZ", "DU")
For Each sh In ThisWorkbook.Worksheets
If Not IsError(Application.Match(sh.Name, V, 0)) Then
F = FreeFile
Open ThisWorkbook.Path & "\" & sh.Name & ".csv" For Output As #F
With Application
For Each Rw In sh.UsedRange.Rows
If Rw.Row > 1 Then Print #F, Join(.Index(Rw.Value2, 1, 0), ",")
Next
End With
Close #F
End If
Next sh
End Sub

 

[Joe4]

I solved the problem.
I tried to change the last part of the vba code, where I've changed "," with ";" (the part in blue color).
Now it works perfect, but I need the last suggestion: how can I set the columns L and M of every csv in data format (gg.mm.aaaa)?

Thanks so much!

Sub EachSheetAsCSV()
Dim F%, Rw As Range
Dim V As Variant
V = Array("AX", "BY", "CZ", "DU")
For Each sh In ThisWorkbook.Worksheets
If Not IsError(Application.Match(sh.Name, V, 0)) Then
F = FreeFile
Open ThisWorkbook.Path & "\" & sh.Name & ".csv" For Output As #F
With Application
For Each Rw In sh.UsedRange.Rows
If Rw.Row > 1 Then Print #F, Join(.Index(Rw.Value2, 1, 0), ",")
Next
End With
Close #F
End If
Next sh
End Sub

Hmmm, that really is a different question (and probably should be its own thread), and using the method kokosek proposes using "JOIN", I am not 100% certain.
I typically created CSV files just by saving the sheet as a CSV. If you do that, whatever format it is in on the sheet should transfer to the CSV file. I am not sure with this method.
However, you can try and see.

Make sure you have set the format on the column correctly, run the code, then open the CSV in NotePad and take a look and see if it looks correct in NotePad.

 

[KOKOSEK]

Dates are stored in csv as value (number), so there is no need to format them.
Format is apply when you displaying data in excel (or other program where data from csv goes to).

 

[mark84]

Thanks for the answers.
I'm with you when you write

Dates are stored in csv as value (number), so there is no need to format them

But I need to set columns L and M in data format because then I have to upload this csv file in a software that requires columns L and M in data format (gg/mm/aaaa).
Is it possible to edit the code or add a part of code to include this step?

Thanks in advance

 

[KOKOSEK]

So we have to change a bit a code.
Can't use JOIN as we have to check values in particular column (L & M).
Try this:

Sub EachSheetAsCSV()
Dim F%, Rw As Range, i As Integer
Dim V As Variant
V = Array("AX", "BY", "CZ", "DU")
For Each sh In ThisWorkbook.Worksheets
    If Not IsError(Application.Match(sh.Name, V, 0)) Then
        F = FreeFile
        Open ThisWorkbook.Path & "\" & sh.Name & ".csv" For Output As #F
        With Application
            For Each Rw In sh.UsedRange.Rows
                xVal = ""
                If Rw.Row > 1 Then
                    For i = 1 To Rw.Columns.Count
                        If i = 12 Or i = 13 Then                        ' L is column 12 and M is column M (if range starts in A, of course)
                            xVal = xVal & Format(.Index(Rw.Value2, 1, i), "dd/mm/yyyy") & ","
                        Else
                            xVal = xVal & .Index(Rw.Value2, 1, i) & ","
                        End If
                    Next i
                Print #F, Left(xVal, Len(xVal) - 1)
                End If
            Next Rw
        End With
        Close #F
    End If
Next sh
End Sub

 

[mark84]

You are really a genius
The problem is finally solved.
Thanks very much also to @Joe4
Have a nice day guys!

best regards
Marco

 

[KOKOSEK]

Happy to help.