VBA script adding "" to CSV file

[Jaunkalns]

Hello,

I've created (pieced it together from solutions found on this forum) a looped VBA code that currently makes a copy of all the non excluded sheets in the workbook, exports each sheet as a separate workbook, and saves them as a CSV file using the sheet name as the file name.

However, the end result adds quotation marks ("text,number") to the text.

There are no problems if I do these steps manually (no quotation marks).
I found that replacing "WS.Copy" with "WS.Move" also creates a file without the quotations marks, however, it also crashes while running the FileName step (Run-time error '2417221080 (800401a8) | Automation error)

I am no expert, so I would appreciate any tips on how to make this work or if there are better ways of doing this.

Sub Export()
'
' Export Macro
'
Dim WB As Workbook
Dim WS As Worksheet
Dim FolderPath As String
Dim FileName As String


FolderPath = "K:\censored_path_name"


 For Each WS In Worksheets
    If WS.Name <> "Sheet name 1" And WS.Name <> "Sheet name 2" And WS.Name <> "Sheet name 3" And WS.Name <> "Sheet name 4" And WS.Name <> "Sheet name 5" Then
   
         WS.Copy
       
         Set WB = ActiveWorkbook
         FileName = WS.Name
        
         WB.SaveAs FileName:=FolderPath & "\" & FileName & ".csv", FileFormat:=xlCSVUTF8, CreateBackup:=False
         Application.DisplayAlerts = False
         WB.Close
         Application.DisplayAlerts = True
    End If
     Next WS

End Sub

 

[Jaunkalns]

I would prefer not to change the file type.
Maybe there is a better or alternative way how to approach this?

 

[Georgiboy]

Here is another approach:

Sub Print_Test()
    Dim FSO As Object
    Dim TextFile As Object
    Dim record As String
    Dim rCell As Range
    Dim CSVFilePath As String
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    ' Create and close a temp CSV file
    CSVFilePath = "C:\Users\jbloggs\Desktop\test\test.csv"
    Set TextFile = FSO.CreateTextFile(CSVFilePath)
    TextFile.Close
    
    ' Open the created CSV to write to
    Open CSVFilePath For Output As #1
    
    ' Write the values line by line to the CSV
    For Each rCell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Cells
        Print #1, rCell.Value
    Next rCell
    
    ' Close the created CSV file
    Close #1
End Sub

 

[Joe4]

Is your data in the Excel file all stored in one column, or multiple columns?

 

[Jaunkalns]

Is your data in the Excel file all stored in one column, or multiple columns?

Same column.

 

[Georgiboy]

Did you try post 12, that was created based on one column?

 

[Joe4]

Same column.

OK, that is a very important detail that was not abundantly clear.

That is expected behavior, as when Excel creates CSV files, it treats each column as a separate field, and puts the commas in between each file.
However, if there is a comma in the cell, it treats it as one field, and needs the text qualifiers of double-quotes around it.

If your intention is to take the one field in Excel and and create multiple fields in your CSV, then you have a few options:

1. Use "Text to Columns" on your column in Excel to split that one field up among multiple columns, choosing the comma as your delimiter.
Then, you can save as a CSV and it should exhibit the behavior you desire.

2. Opt for a special VBA approach which writes out the CSV file line-by-line, like the one Georgiboy posted.

 

[Jaunkalns]

Did you try post 12, that was created based on one column?

I tried and it looks like it works - creates a CSV file that does not add "".
However, how can I now piece it all together? (export all non-excluded sheets and save them using their sheet names)

 

[Georgiboy]

Maybe (untested):

Sub Print_Test()
    Dim FSO As Object
    Dim TextFile As Object
    Dim rCell As Range
    Dim CSVFilePath As String
    Dim ws As Worksheet
    
    CSVFilePath = "C:\Users\jbloggs\Desktop\test\"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    For Each ws In ThisWorkbook.Worksheets
        If Left(ws.Name, 10) <> "Sheet name" Then
            Set TextFile = FSO.CreateTextFile(CSVFilePath & ws.Name & ".csv")
            TextFile.Close
            Open CSVFilePath & ws.Name & ".csv" For Output As #1
            For Each rCell In ws.Range("A2:A" & ws.Range("A" & Rows.Count).End(xlUp).Row).Cells
                Print #1, rCell.Value
            Next rCell
            Close #1
        End If
    Next ws
End Sub

 

[Jaunkalns]

It works.
Fantastic.
Thank you!