Workbooks.Open macro changes the cell format

I have a an issue... Opening a .xls-file manually, the number formats in the file are with dots such as 1.200 --> As they should.

While opening the same file through a Workbooks.Open macro, it opens the file with the format changed to commas, in this case 1,2 .

My goal is to get all the numbers in the file with over 999 to be in "0"-format , such as 1200 in this case.

This drives me crazy. I feel this should be a simple thing to bypass, but I haven't been able to figure this out.

Any help and workaround & explanation would be much appreciated.

I've added an example file how it opens while opening it manually (with dots).



The system we use can only save the data in .xls or .txt .

I've tried to save the files to .xlsx and .xlsm before opening them through VBA, but still the Workbooks.Open macro changes the cell format when it opens the file.

I attached the file just to give the idea of the original file and format and the goal. I hoped someone could find a macro or solution that helps. This error occurs on opening the file, so there is really no written code to evaluate in this case.

It seems to me that the file you attached isn't a 'true' Excel file but a .txt renamed .xls .
Anyway since your file doesn't contain macro it's impossibile to evalutate what happens with your Workbook.Open.

For opening I just use a basic Workbooks.Open - command from the directory where the file is.

Here's the macro where I think the issues lies:

I want to open the .txt-file then do the correct format on the number cells and save it as .xlsx-format. After this macro, the dots are (back to/again/still) in commas.

Please Login or Register  to view this content.

But the problem here is too, that when the macro opens the workbook, dots are in commas. When opening manually, dots are dots. Why is the macro doing this?

Workbooks.Open has a Local parameter you should be using - add:

Please Login or Register  to view this content.

to your Open command.

Glad we could help.

It tells Excel to open the file using your regional settings to interpret any data rather than the default US regional settings.