Workbooks.Open and Localisation

Alderm

New Member
Joined
Jun 14, 2018
Messages
1
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,574
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Have you tried using OpenText instead, which will allow you to specify separators, as well as date formats using the FieldInfo argument?
 

Forum statistics

Threads
1,085,812
Messages
5,386,031
Members
401,980
Latest member
chaithanyakrishnagck

Some videos you may like

This Week's Hot Topics

Top