VBA script adding "" to CSV file

Jaunkalns

New Member
Joined
Apr 26, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,964
Office Version
  1. 365
Platform
  1. Windows
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.
Rich (BB code):
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

New Member
Joined
Apr 26, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,964
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 26, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,964
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 26, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Well, I save it as a CSV file.
 

Georgiboy

Board Regular
Joined
Nov 7, 2008
Messages
202
Have you tried it with the standard CSV FileFormat:
VBA Code:
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

New Member
Joined
Apr 26, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Have you tried it with the standard CSV FileFormat:
VBA Code:
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
1651046231140.png


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

Georgiboy

Board Regular
Joined
Nov 7, 2008
Messages
202
How about saving it as a txt file instead?
VBA Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,806
Messages
5,855,755
Members
431,760
Latest member
IceAg08

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top