Excel international CSV file opening consistency

[RSGB]

Hi

We have a VB app that produces CSV formatted files. I have done a macro that opens these CSV files and creates charts based on the data therein and it works fine.

Only I (in UK) have given this Excel macro to other offices in our group (French, German, Danish, Norwegian, Spanish) to use too. They all use commas instead of decimals and Excel does not treat CSV files in the same way.

Does anyone know how to get CSV files to always be opened consistently regardless of local language settings etc.?

Thanx in advance

Vince

 

[Dreamboat]

You're probably selecting the formatting with the import wizard, right? I wouldn't. Just import the stuff, and THEN go do your cell formatting. Not sure if that's what you mean...
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[RSGB]

Hi DreamBoat,

Thanx for your help.

I'm just using:

FileToOpen = Application.GetOpenFilename("Conversion Model csv files (*.csv),*.csv&quot
Workbooks.Open (FileToOpen)

I did try specifying the import filter as comma delimited at one time but this apparently didn't fix it.

For some people what happens is all the data is put in the first column, with commas. This I could fix by splitting each row string where commas are supposed to be separators (as opposed to legitimately appearing in a text string).

For some people, though, the commas are used as delimiters but decimal points also seem to be too and I can't see how to put this back together as not all cells are formatted to a known number of decimal places.

To complicate matters further the French have said that the former behaviour seems to happen on the first time of running the macros but the latter behaviour occurs on subsequent runnings within that instance of Excel!!!

The main problem I have is that I don't have the problem, it's everyone else, so I can't really see it.

Thanx

Vince

 

[Dreamboat]

Do you have an intranet site? Perhaps this is something that big that you'd want to...

Otherwise, how are your VB (not vba) skills? Maybe you need to get someone who can write a little executable routine that'll

a) change the regional settings,
b) open Excel and ask for a file, let them run that stuff, and
c) the on-close event can put their regional settings back

I don't write VB, but I have learned a little bit and that doesn't seem very far-fetched or difficult to me.

techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[RSGB]

Hi Dreamboat,

We do have an intranet site but in the short term I don't have access to doing anything on it.

The ultimate solution is, as you say, to get it sorted in VB (which I sadly don't have), ideally by us hiring the guy who wrote the front app to sort it out (I was thinking of getting it changed to TAB delimited file output to avoid the whole issue) but this isn't happening anytime soon it seems...

Only other thing I could think of was in another Excel macro opening and charting TAB files from another app, for the US the problem was their dates being the wrong way round and Excel deciding to be clever and interpreting possible dates (eg 02/02)as dates but not impossible dates (eg 16/02).

What I did was open the TAB file, create an array the size of the data series, then close the TAB file and open it again, this time specifying each column that could have dates in to be opened as Text format, then I could subsequently change all the numerical cells affected back to numbers.

Maybe there is a similar thing I can do here?

Thanks for your assistance and consideration,

Vince

 

[Dreamboat]

Sounds worth your time, Vince. As for me, I think I'd have to see a few-record sample. Change the names if you're worried about confidentiality.
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[RSGB]

Thanks for your continued interest, Dreamboat, I hope you had a good weekend

For example one simple data chart is arrayed a bit like this to me:

Esso Users Esso Non-Users
Ent Ave Shal Conv Avail Ambiv WUn StrUn Unaware
Esso 0.6 6.2 9.8 3.2 2.1 7.5 13.3 11.4 45.9
etc.

But for others on opening it appears like this:

Esso Users,Esso Non-Users
Ent,Ave,Shal,Conv,Avail,Ambiv,WUn,StrUn,Unaware
Esso, 0.6,6.2,9.8,3.2,2.1,7.5,13.3,11.4,45.9

etc.

or

Esso Users Esso Non-Users
Ent Ave Shal Conv Avail Ambiv WUn StrUn Unaware
Esso 0 6 6 2 9 8 3 2 2 1 7 5 13 3 11 4 45 9

etc.


So it has either not used commas as separators at all or used decimals as separators and also can be a combination of the two, depending on country tried in (BTW I did tell them at one point to switch to English settings and try then but that didn't seem to work).

Any thoughts?

Thanx in advance

Vince