Notes tests, string, manipulation of text files and string manipulations

**DocAElstein** · 06-16-2020, 11:14 AM

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

Code:

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

I get the same results for answering No

These macros gives me the same results

Code:

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

Code:

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