Hello!
I tried different codes from different sites, but none of the solutions seems to work fine. I have a macro that cuts big data into smaller chunks and then saves them into seperate csv files, that are then manually sent to some server to read that automatically.
To be read properly a file needs to meet specific criteria: UTF8 coding, no BOM, without quotation marks as text qualifier.
My Excel is 2019.
The (almost) working piece of "save file" code is as follows:
Opening created files with Notepad+ shows 2 problems:
1. Some lines in some files start and end with Quotation marks (there are none in source data in Excel). I didn't figure out what determines which lines are influenced. Most possibly something goes wrong with Text Qualifier. It happens only when saving by VBA. Saving manually with "save as -> UTF-8" doesn't produce these wild quotation marks.
2. All files contain BOM. I need to manually convert all files with another program to get rid of it.
Is there any way to solve problem 1 and/or 2 with VBA? Now I get 2 extra steps for all my work only for converting it
I tried different codes from different sites, but none of the solutions seems to work fine. I have a macro that cuts big data into smaller chunks and then saves them into seperate csv files, that are then manually sent to some server to read that automatically.
To be read properly a file needs to meet specific criteria: UTF8 coding, no BOM, without quotation marks as text qualifier.
My Excel is 2019.
The (almost) working piece of "save file" code is as follows:
VBA Code:
Private Sub SaveFilesForServer()
Dim WS As Excel.Worksheet
Dim SaveToDirectory As String
Dim WB As Workbook
Set WB = ActiveWorkbook
ActiveWorkbook.WebOptions.Encoding = msoEncodingUTF8
Dim CurrentWorkbook As String
Dim CurrentFormat As Long
CurrentWorkbook = ThisWorkbook.FullName
'CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook
SaveToDirectory = "S:\AnaliticsFiles\"
For i = 1 To WB.Sheets.Count
WB.Sheets(i).Activate
WB.Sheets(i).SaveAs SaveToDirectory & WB.Sheets(i).Name, FileFormat:=xlCSVUTF8
Next
End SubOpening created files with Notepad+ shows 2 problems:
1. Some lines in some files start and end with Quotation marks (there are none in source data in Excel). I didn't figure out what determines which lines are influenced. Most possibly something goes wrong with Text Qualifier. It happens only when saving by VBA. Saving manually with "save as -> UTF-8" doesn't produce these wild quotation marks.
2. All files contain BOM. I need to manually convert all files with another program to get rid of it.
Is there any way to solve problem 1 and/or 2 with VBA? Now I get 2 extra steps for all my work only for converting it
