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

 

[Joe4]

Welcome to the Board!

A few things:

1. NEVER, NEVER use reserved words like "FileName" as the name of your variables, proecedures or functions!
Doing so can cause errors and expected results, as Excel cannot tell which one you are trying to refer to. You are actually using BOTH in your code, i.e.

Dim FileName As String
...
WB.SaveAs FileName:=FolderPath & "\" & FileName & ".csv", FileFormat:=xlCSVUTF8, CreateBackup:=False

So you will need to change the name of your "FileName" variable.

2. Can you post a sample of what the data you are saving looks like?
Do you have any commas anywhere in your data?
I do not get the quotes around the values when I test, but it could be dependent upon your data.

 

[Jaunkalns]

1. Changed the variable, but still get the same error
2. Yes, I do. These are order files that are automatically "read" by a tracking platform.
Here is an example

order

client.regnr:,14131773

branch:,3018683

remarks:,

order.nr:,3018683250422

supplier.code:,

order.date:,20220425

delivery.date:,20220427

compiler.firstname:,

compiler.lastname:,

compiler.email:,name@email.com

rows

ean_code,prod_code,prod_name,unit,quantity

1111111,321654,Name,pieces,50

 

[Joe4]

OK, note that when creating CSV files, if you also have commas in your data, you will see double-quotes surrounding that data. This is fully expected behavior.
This is absolutely necessary. Some way to distinguish commas that are your field separators in your CSV file from literal commas that are in your data is necessary.
The way that Excel (and most computer programs) treat this is to surround the data containing the commas in double-quotes (to denote text).

 

[Jaunkalns]

Hmm, I understand, however, when I do this step manually -> right click on the sheet -> Move or Copy -> new book -> etc. -> etc there are no quotation marks.
Do they appear only when using VBA to save them as CSV files?

 

[Joe4]

Hmm, I understand, however, when I do this step manually -> right click on the sheet -> Move or Copy -> new book -> etc. -> etc there are no quotation marks.
Do they appear only when using VBA to save them as CSV files?

That would be creating another Excel file, not a CSV file, correct?

 

[Jaunkalns]

Well, I save it as a CSV file.

 

[Georgiboy]

Have you tried it with the standard CSV FileFormat:

Sub Export()
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim FolderPath As String
    Dim fName As String
    
    FolderPath = "C:\Users\jbloggs\Desktop\test\"

    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
             fName = WS.Name
             Application.DisplayAlerts = False
             WB.SaveAs FileName:=FolderPath & fName & ".csv", FileFormat:=xlCSV, CreateBackup:=False
             WB.Close
             Application.DisplayAlerts = True
        End If
    Next WS
End Sub

I am having trouble reproducing your "" error

 

[Jaunkalns]

Have you tried it with the standard CSV FileFormat:

Sub Export()
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim FolderPath As String
    Dim fName As String
   
    FolderPath = "C:\Users\jbloggs\Desktop\test\"

    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
             fName = WS.Name
             Application.DisplayAlerts = False
             WB.SaveAs FileName:=FolderPath & fName & ".csv", FileFormat:=xlCSV, CreateBackup:=False
             WB.Close
             Application.DisplayAlerts = True
        End If
    Next WS
End Sub

I am having trouble reproducing your "" error

The "" are only visible if you open the file using Notepad

 

[Georgiboy]

How about saving it as a txt file instead?

Sub Export()
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim FolderPath As String
    Dim fName As String
    
    FolderPath = "C:\Users\jbloggs\Desktop\test\"

    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
             fName = WS.Name
             Application.DisplayAlerts = False
             WB.SaveAs Filename:=FolderPath & fName & ".txt", FileFormat:=xlTextWindows, CreateBackup:=False
             WB.Close
             Application.DisplayAlerts = True
        End If
    Next WS
End Sub