Excel CSV Formatting Macro

Question

I am trying to export multiple worksheets as an specific csv file with very specific formatting to feed into third party software (PJe Calc Cidadão).

PJe accepts files written in the following format:

"MES_ANO";"VALOR";"FGTS";"FGTS_REC.";"CONTRIBUICAO_SOCIAL";"CONTRIBUICAO_SOCIAL_REC."
"10/2012";"500,00";"S";"S";"S";"S"
"01/2013";"500,00";"S";"N";"S";"N"

I can achieve this formatting by concatenating formatted values in a single column of a worksheet and saving it as a CSV, but once I open the CSV outside excel it is formated as:

"""MES_ANO"";""VALOR"";""FGTS"";""FGTS_REC."";""CONTRIBUICAO_SOCIAL"";""CONTRIBUICAO_SOCIAL_REC."""
"""12/2015"";""1000,00"";""N"";""N"";""N"";""N"""
"""01/2016"";""1000,00"";""N"";""N"";""N"";""N"""

If I simply copy and paste the column in a txt file I can get the format that I want, but since I need to do that multiple times it's a bit tiring

Any advice?

Is it all sheets in the workbook or only specific ones ? Is it one CSV for all sheets or separate csv for each ? Are the 6 columns in "A:F" — CDP1802
– CDP1802, Commented Mar 6, 2020 at 15:59
An example of your original data would be helpful in suggesting a solution. — Ron Rosenfeld
– Ron Rosenfeld, Commented Mar 6, 2020 at 16:00
Separate csv for each worksheet, only sheets selected on a form (but that part I got done already) — Pedro Henrique Monforte
– Pedro Henrique Monforte, Commented Mar 9, 2020 at 15:12

CDP1802 · Accepted Answer · 2020-03-07 13:35:30Z

Assuming you want to export Columns A to F on all the sheets in the workbook to separate csv files with unicode encoding then try this ;


    Option Explicit

    Sub exportcsv()

        Const LAST_COL = 6
        Const DELIM = ";"
        Const QUOTE = """"

        Dim wb As Workbook, ws As Worksheet
        Dim iRow As Long, iLastRow As Long, s As String, c As Integer, count As Integer
        Dim oFSO As Object, oFS As Object
        Dim sPath As String, sCSVfile As String

        Set oFSO = CreateObject("Scripting.FileSystemObject")
        Set wb = ThisWorkbook
        sPath = wb.path & "\"

        For Each ws In wb.Sheets
            count = 0
            sCSVfile = "Sheet_" & ws.Index & ".csv"
            Set oFS = oFSO.CreateTextFile(sPath & sCSVfile, True, True) 'overwrite, Unicode

            iLastRow = ws.Cells(Rows.count, 1).End(xlUp).Row
            For iRow = 1 To iLastRow
                s = ""
                For c = 1 To LAST_COL
                    If c > 1 Then s = s & DELIM
                    s = s & QUOTE & ws.Cells(iRow, c) & QUOTE
                Next
                oFS.writeline s
                count = count + 1
            Next
            oFS.Close
            Debug.Print sCSVfile, count
        Next
        MsgBox "CSV files exported to " & sPath, vbInformation, "Finished"
    End Sub

Collectives™ on Stack Overflow

Excel CSV Formatting Macro

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related