French Excel Macro issue related to cell formats

**nozerf** · 10-21-2011, 06:52 AM

Hi There,

I have a strange problem that doesn't appear in the English version of excel.

I have a spreadsheet that has a macro to load data. All the load data macro is meant to do is take from the indicated text file, the information and populate a pre-formatted worksheet.

The text file contains predominantly numbers which when imported into the excel worksheet should be depicted in percentages so 0.1 would be 10%.

What I have observed is that when the number being imported only has one digit after the decimal place (e.g. 0.1) the format of the cell changes from percentage to date and a random date is entered into the cell.

To confirm this was the case, I changed the 0.1 to 0.11 in the text file and then imported the file into the excel sheet using the macro load data and the cell containing that value was correctly showing 11%.

Now of course its going to be a real bind to have to go through the text file and add an extra digit for each number that has only one digit after decimal point so I was hoping that someone here can help?

If you need the macro code I can add it to this forum but i noticed on another post that it is required to add "code tags" so please tell me what you mean by "code tags" if you wish to me to add the code.

Please Login or Register  to view this content.

Thanks for reading and even bigger thanks in advance if you are able to help.

Regards,

Nozer

**arthurbr** · 10-21-2011, 08:16 AM

Hi and welcome to the board
Concerning code tags ( and other issues) please have a look at Forum Rules

**nozerf** · 10-21-2011, 08:21 AM

Originally Posted by arthurbr

Hi and welcome to the board
Concerning code tags ( and other issues) please have a look at Forum Rules

Thanks.

Now having read the forum rules I know what to do so will add the code in a mo.

**nozerf** · 10-23-2011, 07:33 AM

Hi All,

PLease note that the code has been added into the original text above.

Thanks,

Nozer

**Andrew-R** · 10-23-2011, 07:48 AM

Are the dates actually random, or do they represent the date value of the number you're importing?

Does it help if you use

Please Login or Register  to view this content.

**nozerf** · 10-23-2011, 07:54 AM

Originally Posted by Andrew-R

Are the dates actually random, or do they represent the date value of the number you're importing?

Does it help if you use

Please Login or Register  to view this content.

They do look like there is a relation but cant be 100% sure. WHere would I put the above code into my macro?

But wouldnt that mean that the number would then get displayed simply as a number?

i.e if the imported value is 0.1, then the number displayed would be 0.1 rather than 10%?

It would certainly be interesting to expand on the above code to be able to state which cells needed to be percentage to see what would happen.

I will test what you suggest and get back to you.

**Andrew-R** · 10-23-2011, 07:58 AM

Sorry, yes. The best thing to do would be to after you import the text file to set the format to:

Please Login or Register  to view this content.

So long as the imported text file is the active sheet that should work and will not affect any non-numeric values.

**nozerf** · 10-23-2011, 08:11 AM

Originally Posted by Andrew-R

Sorry, yes. The best thing to do would be to after you import the text file to set the format to:

Please Login or Register  to view this content.

So long as the imported text file is the active sheet that should work and will not affect any non-numeric values.

Hmmm, I tried to manually change the cell that is supposed to be %age (which came out as date) into percentage but the value it came out as was not what it meant to be so I cant change the format after the file is imported

**Andrew-R** · 10-23-2011, 09:05 AM

That's me a bit stumped then. I suppose you could write some code to pre-edit your text file, but I've no idea what's causing the problem in the first place.

If it's only an issue in the French version of Excel I'd guess the answer must be in the country settings somewhere.