vba 使用vba在excel中复制带前导零的数字文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23232049/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copying numeric text with leading zeroes in excel using vba
提问by laffoyb
I've written a macro to take the contents of an Excel worksheet and export them to a CSV file (for import by another application).
我编写了一个宏来获取 Excel 工作表的内容并将它们导出到 CSV 文件（供另一个应用程序导入）。
My problem is that on export, any cell containing text which could be interpreted as a number is converted to a number. This means that if the text contained leading zeroes, these are lost.
我的问题是在导出时，任何包含可以解释为数字的文本的单元格都会转换为数字。这意味着如果文本包含前导零，则这些都将丢失。
So, data like this:
所以，数据是这样的：
ASSETS 0100738 1/05/2003 612
资产 0100738 1/05/2003 612
would be exported as
将导出为
ASSETS,100738,1/05/2003,612
资产,100738,1/05/2003,612
My code is:
我的代码是：
Public Sub ExportSheetAsCSV(ByRef sh As Worksheet)
Dim dataToExport As Excel.Range
Dim newWrkbk As Excel.Workbook
Dim FSO As New FileSystemObject
Dim dstdir As String
Dim dstpath As String
Set dataToExport = sh.UsedRange
Set newWrkbk = newWorkbook(1, ActiveWorkbook)
'' Copy Step:
newWrkbk.Sheets("Sheet1").Range("A1").Resize(dataToExport.Rows.Count, dataToExport.Columns.Count).Value = dataToExport.Value
dstdir = FSO.BuildPath(FSO.BuildPath(ActiveWorkbook.path, "CSVs"), FSO.GetBaseName(ActiveWorkbook.Name))
dstpath = FSO.BuildPath(dstdir, sh.Name & ".csv")
MkDirStructure dstdir
newWrkbk.SaveAs Filename:=dstpath, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
newWrkbk.Close False
End Sub
By going through this step by step, I can see that the leading zeroes disappear at the copy step (as opposed to the save step).
通过逐步执行此操作，我可以看到前导零在复制步骤（与保存步骤相反）消失了。
This question, VBA: preceding zeros dropped when copied over, covers a similar issue, but none of the given answers work for me. The accepted answer is to convert the offending cells to text (and the other top answer is to do the same using VBA's numberFormat), but
这个问题，VBA：前面的零在复制时丢失，涵盖了一个类似的问题，但没有一个给定的答案对我有用。接受的答案是将有问题的单元格转换为文本（另一个最佳答案是使用 VBA 的 numberFormat 做同样的事情），但是
- I don't know in advance which cells will need to be converted so this is inconvenient
- the value of the cells is updated by a formula so if I convert to text, then the text of the formula becomes the value of the cell
- Date values will be converted to their integer representation
- This doesn't even work. Having converted cells to text, the leading zeroes are still stripped.
- 我事先不知道需要转换哪些单元格，所以这很不方便
- 单元格的值由公式更新，因此如果我转换为文本，则公式的文本将成为单元格的值
- 日期值将转换为其整数表示
- 这甚至不起作用。将单元格转换为文本后，仍会去除前导零。
Does anybody have a solution to this problem?
有没有人有解决这个问题的方法？
Thanks
谢谢
回答by bp696
' convert the format of all cells to text and add the below line for the required range of cells...before copying the data..
' 将所有单元格的格式转换为文本，并在复制数据之前为所需的单元格范围添加以下行...
Range("B1:X100000").NumberFormat = "@"
Range("B1:X100000").NumberFormat = "@"
回答by laffoyb
Okay, after a little bit more work, the workaround I found is to do a copy/paste-special rather than to assign the value of target range to the value of the source.
好的，经过更多的工作，我发现的解决方法是进行复制/粘贴特殊操作，而不是将目标范围的值分配给源的值。
So, this line:
所以，这一行：
newWrkbk.Sheets("Sheet1").Range("A1").Resize(dataToExport.Rows.Count, dataToExport.Columns.Count).Value = dataToExport.Value
is replaced by
被替换为
Application.CutCopyMode = False
Set oldWrkbk = ActiveWorkbook
dataToExport.Copy
newWrkbk.Sheets("Sheet1").Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
oldWrkbk.Activate
Where a reference to the source workbook is cached so that we can return to where we started before closing the new file.
对源工作簿的引用被缓存的位置，以便我们可以在关闭新文件之前返回到我们开始的位置。
