Notes tests, text files, manipulation of text files in Excel and with Excel VBA CSV stuff

[DocAElstein]

One important note at the outset of these notes is that my Excel is default German. I expect therefore issues likely to arise in anything to do with use of a comma , because often the comma , in English for certain things in Excel is replaced with the semi colon ; in German Excel.

Wot’s in a .csv file
In many Excel versions, you have the choice of three different Save As options related to .csv extension files https://excel.tips.net/T002519_Comma...C_and_Mac.html

So lets have a quick look at what the differences are…
In each of the 3 cases I will
make a virgin default template, save it
make a virgin default template, put a value in first cell, save it
make a virgin default template, put a value in first 2 cells, save it
make a virgin default template, put a value in first 2 cells, and in the cell A2, save it
( Initially, when I put values in, I wont hit the Enter after: Initially I want to avoid purposely doing something that may introduce a carriage return or line feed. I will then look further at that issue later )
After this I will investigate the made files , ( using , for example, my Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String) ) ' https://excelfox.com/forum/showthrea...ts-of-a-string ' https://excelfox.com/forum/showthrea...ll=1#post11015
https://excelfox.com/forum/showthrea...ll=1#post13699

Here are the results ( It is a summary of what my function tells me is in those made files ) :

CSV (Comma delimited)Empty.csv		vbCr & vbLf
CSV (Macintosh)Empty.csv		vbCr & vbLf
CSV (MS-DOS)Empty.csv		vbCr & vbLf
CSV (Comma delimited)A1.csv	cellA1	"cellA1" & vbCr & vbLf
CSV (Macintosh)A1.csv	cellA1	"cellA1" & vbCr & vbLf
CSV (MS-DOS)A1.csv	cellA1	"cellA1" & vbCr & vbLf
CSV (Comma delimited)A1B1.csv	cellA1;cellB1	"cellA1" & ";" & "cellB1" & vbCr & vbLf
CSV (Macintosh)A1B1.csv	cellA1;cellB1	"cellA1" & ";" & "cellB1" & vbCr & vbLf
CSV (MS-DOS)A1B1.csv	cellA1;cellB1	"cellA1" & ";" & "cellB1" & vbCr & vbLf
CSV (Comma delimited)A1B1A2.csv	cellA1;cellB1 callA2;	"cellA1" & ";" & "cellB1" & vbCr & vbLf & "callA2" & ";" & vbCr & vbLf
CSV (Macintosh)A1B1A2.csv	cellA1;cellB1 callA2;	"cellA1" & ";" & "cellB1" & vbCr & "callA2" & ";" & vbCr & vbLf
CSV (MS-DOS)A1B1A2.csv	cellA1;cellB1 callA2;	"cellA1" & ";" & "cellB1" & vbCr & vbLf & "callA2" & ";" & vbCr & vbLf

Important Conclusions are
_ the Macintosh distinguishes itself with a carriage return character, vbCr , as the line separator for introduced lines
_ There is always a last vbCr & vbLf – Note this means that for a single line, or empty file, we could not tell if we had a Macintosh

When closing the file, I was prompted to answer if I wanted to save changes or not. ( I chose yes in the last experiment ). This is strange since I had previously saved the files before closing
DoYouWantToSaveChangesOnCloseDespiteAlreadySavedCS V.JPG : https://imgur.com/nfnVwSF
But it does not seem to have any effect if I chose Yes or No
Some other observations.
If I use a simple macro, as below, to save and close the file ( and except the changes, which I am still strangely asked for , with Yes), then I get commas instead for the separator/delimiter

Sub SaveCSVviaVBA()
 ActiveWorkbook.Save
 ActiveWorkbook.Close
End Sub

I get the same results for answering No

These macros gives me the same results

Sub SaveAsCSVviaVBA()
 ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "csv Text file Chaos\" & "CSV (Comma delimited)A1B1A2" & ".csv"
 ActiveWorkbook.Close
End Sub 

Sub SaveAsCSVviaVBAxlcsv()
 ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "csv Text file Chaos\" & "CSV (Comma delimited)A1B1A2" & ".csv", FileFormat:=xlCSV
 ActiveWorkbook.Close
End Sub

Ref
https://excelfox.com/forum/showthrea...mediate-Window
https://excelfox.com/forum/showthrea...ed-Text-String
https://excel.tips.net/T002519_Comma...C_and_Mac.html
https://excelribbon.tips.net/T010280...for_PC_and_Mac
https://sites.google.com/a/madrocket...svs?authuser=0
https://excelribbon.tips.net/T009508...ariations.html

Eileen’sLoungeTextFiles
http://www.eileenslounge.com/viewtop...274367#p274367


https://excel.tips.net/T003232_Speci...n_a_Macro.html -- printer line save ……. This works this way by design in VBA. The Excel implementation of the export routines for VBA always use whatever the Windows regional settings are to determine how items in a CSV should be separated. Specifically, the routine looks at the List Separator field for the delimiter. This means that you can, if desired, change the delimiter to a semicolon by changing the List Separator setting in your regional settings configuration.
If you don't want to change the regional settings, then you can instead write your own macro that will output the file in any way you desire. Consider, for a moment, the following macro, which will output the file:











https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgwWg8x2WxLSxxGsUP14AaABAg.9k3ShckGnhv9k89Lsaig oO
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxxxIaK1pY8nNvx6JF4AaABAg.9k-vfnj3ivI9k8B2r_uRa2
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=UgxKFXBNd6Pwvcp4Bsd4AaABAg
https://www.youtube.com/watch?v=yVgLmj0aojI&lc=Ugw9X6QS09LuZdZpBHJ4AaABAg