I have created an Excel dashboard that uses VBA code to import data files in csv format. The data includes dates, and since I and other users are European I had to add the "Local:=True" parameter to my Workbooks.Open command to avoid certain European format dates being converted into US date formats.
This worked well for me. I am based in the UK so my Windows local settings are DD/MM/YY date format and the delimiter is comma. However when I tried to enable the same dashboard with import code for a colleague in Austria I hit a problem with the delimiter. In Austria the Windows delimiter is a semi-colon, which meant the CSV files were not delimited when opened by them.
Luckily Workbooks.Open has another parameter - Format - which allows me to default to using a comma for files opened with this command. Therefore adding Format:=2 as a parameter for Workbooks.Open meant the data import then also worked for my Austrian colleague.
However... here is the problem: the Format:=2 only worked when I removed the Local:=True parameter. If I include Local:=True, in whatever sequence, with Format:=2 the Format parameter is then ignored and the csv file is loaded assuming a semi-colon delimiter. If I delete the Local:=True parameter then the import switches to commas but the date format problem reappears!
It seems that Local cancels out or takes priority over Format. Is there anything I can do to enable both? Or is there a different way to achieve both?
Thanks for any advice!
Peter
This worked well for me. I am based in the UK so my Windows local settings are DD/MM/YY date format and the delimiter is comma. However when I tried to enable the same dashboard with import code for a colleague in Austria I hit a problem with the delimiter. In Austria the Windows delimiter is a semi-colon, which meant the CSV files were not delimited when opened by them.
Luckily Workbooks.Open has another parameter - Format - which allows me to default to using a comma for files opened with this command. Therefore adding Format:=2 as a parameter for Workbooks.Open meant the data import then also worked for my Austrian colleague.
However... here is the problem: the Format:=2 only worked when I removed the Local:=True parameter. If I include Local:=True, in whatever sequence, with Format:=2 the Format parameter is then ignored and the csv file is loaded assuming a semi-colon delimiter. If I delete the Local:=True parameter then the import switches to commas but the date format problem reappears!
It seems that Local cancels out or takes priority over Format. Is there anything I can do to enable both? Or is there a different way to achieve both?
Thanks for any advice!
Peter
