Excel language breaks VBA macro

[alfranco17]

Hi.

I have a macro enabled workbook in a shared folder. It is used by three people. One has Excel in English, the other two have Excel in Spanish.

To use Special Paste - Text in Spanish, the format is "Texto", in English, it is "Unicode Text".

I decided to try to do it both ways with an "On Error Resume Next" and expect one of them to fail.

    On Error Resume Next
    ActiveSheet.PasteSpecial Format:="Texto"
    ActiveSheet.PasteSpecial Format:="Unicode Text"
    On Error GoTo 0

Is there a more elegant solution? Like using a number instead of a string for format?

Thanks.
Armando.

 

[rlv01]

If you know it is always going to be one of those two choices, that actually does not seem to be a bad way to do it. Another alternative might be to add code to detect which flavor of Excel, perhaps by reading a registry setting (like the codepage for example), is calling the macro. I'm not sure it's worth the effort if what you have is working.

 

[alfranco17]

If you know it is always going to be one of those two choices, that actually does not seem to be a bad way to do it. Another alternative might be to add code to detect which flavor of Excel, perhaps by reading a registry setting (like the codepage for example), is calling the macro. I'm not sure it's worth the effort if what you have is working.

Thanks. I tried that approach once. There were too many codepages for English and Spanish.

 

[Marc L]

Hi,​

​

as just using the VBA property International you can find for example the country code, to see on VBA help …​

 

[Marc L]

​

Another VBA help must see : LanguageSettings.LanguageID …​

 

[RoryA]

Try Format:=1. The options you get when doing it manually can be converted to numbers using the order they appear in the dialog, starting at 0, not 1.

 

[alfranco17]

​

Another VBA help must see : LanguageSettings.LanguageID …​

Thank you. It's a very interesting property. Application.LanguageSettings.LanguageID(msoLanguageIDUI) has 20 different Spanish variants. RoryA's answer is the one I was looking for, but this one is very interesting.

 

[alfranco17]

Try Format:=1. The options you get when doing it manually can be converted to numbers using the order they appear in the dialog, starting at 0, not 1.

That's it. Thanks!