In my Windows OS, I set my "Date, time or number formats" with the following separators:
Decimal: ","
Milesimal: "."
List: ";"
In excel, I set in File > Options > Advanced > Use System Separators (ticked)
And when I open my CSV file manually, it goes according to the System Separators (data hidden due to confidential data). In the picture below, each data is in one Column
PROBLEM 1:
But when I asked my VBA macro to Open this CSV file, it totally ignored the System Separators. In the picture below, all data are in the Column A
The command I used in VBA to open the file was:
Workbooks.Open (MacroFile.Range("B" & i).Value & "\" & MacroFile.Range("C" & i).Value), UpdateLinks:=False
Note: It gets the Path + FileName through Cell Content
SOLUTION FOR PROBLEM 1 (credit to @FoxfireAndBurnsAndBurns):
Added property Local:=True to Workbooks.Open (MacroFile.Range("B" & i).Value & "\" & MacroFile.Range("C" & i).Value), UpdateLinks:=False, Local:=True
PROBLEM 2:
When I asked my VBA macro to Close+Save this CSV file, it totally ignored the System Separators. It replaced all the ";" with "," list delimiters
The command I used in VBA to Close+Save the file was:
Workbooks(MacroFile.Range("C" & i).Value).Close SaveChanges:=True
PROBLEM 3:
I've noticed too that when I tried to use the Formula command below, it didn't work, even having Separator as ";"
File_CSV.Range("Z1").Formula = "=SUMIF(F:F;""C"";G:G)"
I had to replace for:
File_CSV.Range("Z1").Formula = "=SUMIF(F:F,""C"",G:G)"
SOLUTION FOR PROBLEM 3 (Credit for TimWilliams):
Used .FormulaLocal in File_CSV.Range("Z1").FormulaLocal = "=SUMIF(F:F;""C"";G:G)"