为什么我无法将Application.ReplaceFormat.NumberFormat设置为一些有效的格式？ [英] Why can't I set Application.ReplaceFormat.NumberFormat to some valid formats?

问题描述

我正在尝试使用Excel宏重新格式化使用OLE自动化导出的电子表格

I'm trying to use an Excel Macro to reformat a spreadsheet exported using OLE-Automation

以下代码可以正常工作：

The following code works OK:

Application.FindFormat.NumberFormat = "#,##0.0000000"
Application.ReplaceFormat.NumberFormat = "#,##0.00"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

如果我将ReplaceFormat更改为

if I change the ReplaceFormat to

Application.ReplaceFormat.NumberFormat = "#,##0.0" 

仅显示1个小数我收到错误1004（应用程序定义或对象定义的错误）。 0.0失败。

to display only 1 decimal place, I get an error 1004 (Application-defined or object-defined error). "0.0" fails as well.

我可以将单元格格式（Cells.NumberFormat）设置为＃，## 0.0

I can set the cell format (Cells.NumberFormat) to "#,##0.0"

我只是针对Excel-2003尝试过，因为它是唯一可用的版本。

I have only tried this against Excel-2003 as it is the only version I have available.

推荐答案

我找到了答案的一部分。为了使其工作，NumberFormat必须已经存在于工作簿中。

I have found part of the answer. For this to work, the NumberFormat must already exist in the workbook.

解决方法是将单元格的格式设置为＃，## 0.0然后进行替换：

A work-around is to set the format of a cell to "#,##0.0", then do the replace:

Worksheets("Sheet1").Range("A1").NumberFormat = "#,##0.0" 
Application.FindFormat.NumberFormat = "#,##0.0000000"
Application.ReplaceFormat.NumberFormat = "#,##0.00"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

似乎没有任何可以让我获取自定义数字格式的集合（根据本网站）。

There doesn't seem to be any Collections that allow me to get at the custom number formats (According to this site anyway).

当将Application.FindFormat设置为新的时候，我发现了格式开始抛出错误！

I discovered this when setting Application.FindFormat to a new format started throwing errors!

这篇关于为什么我无法将Application.ReplaceFormat.NumberFormat设置为一些有效的格式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！