0

I am rolling out a simple VBA Macro to users which converts a .xlsx spreadsheet into a .csv file.

The macro works well for all users except those in China. When they run the macro they get a

1004: Application-defined or object-defined Error

on the line below:

    Columns("A:Q").Select
    Selection.Replace What:=ChrW(&H2C), Replacement:=ChrW(&H20), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

This code replaces all commas with a period so the the csv formatting is correct.

Does anyone know why this error is only affecting users in China? Are there specific security setting I need to change? Or is there an alternative way in VBA to remove all commas (deleting them would be fine too)?

Any suggestions would be gratefully received. I cannot travel to China due to Covid, and remote assist to a Chinese Language laptop is basically unworkable.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
niiru-san
  • 1
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Jul 19 '21 at 06:08
  • Any formulas in that sheet? Since comma is the default seperator in arguments of formulas, they become invalid if you replace the comma with a period there. – Pᴇʜ Jul 19 '21 at 06:10
  • 1
    Whatever you are trying to do, `ChrW(&H20)` is the space character, not a period. – FunThomas Jul 19 '21 at 07:20
  • Of course ```ChrW(&H20)``` is the space character. My mistake -duh. In this case, I don't think it's important as the objective is to remove the commas. – niiru-san Jul 21 '21 at 03:15
  • There are no formulas in the sheet. It is a data download as an excel .xlsx file from an ERP system. – niiru-san Jul 21 '21 at 03:16
  • See the link above your question `FormulaVersion:=xlReplaceFormula2` seems to be the issue to cause this kind of error. And according the documentation of the [Range.Replace method](https://learn.microsoft.com/en-us/office/vba/api/excel.range.replace) this parameter does not exist. While this might have been introduced in some recent Excel 365 version it does not exist in all versions of Excel and therefore probably errors only in some. So looks like it is not a problem with China but a problem of the Excel version your chinese colleagues are using. – Pᴇʜ Jul 21 '21 at 05:37
  • 1
    A combination of removing ```SELECT``` and removing ```FormulaVersion:=xlReplaceFormula2``` seems to have done the trick. The macro is now running as it should on my Chinese colleagues computer. The problem code has now been updated to ```Columns("A:Q").Replace What:=ChrW(&H2C), Replacement:=ChrW(&H20), LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, MatchByte:=False, SearchFormat:=False, _ ReplaceFormat:=False``` Many thank for all your help!!! – niiru-san Jul 22 '21 at 01:57

0 Answers0