如何粘贴前导"0"的文本在Excel中 [英] How to paste text with leading "0" in excel

问题描述

我有这段代码可将整个txt文件内容粘贴到我的活动工作簿中，但不会出现前导"0"字样.在单元格中:

I have this code that pastes entire txt file contents to my active workbook but it loses leading "0" in cells:

Dim FileToOpen As Variant
Dim OpenBook As Workbook

FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")

If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Sheets(1).UsedRange.Select
Selection.NumberFormat = "@"
OpenBook.Sheets(1).UsedRange.Copy
ThisWorkbook.Worksheets("BOM").Range("C1").PasteSpecial xlPasteValues
OpenBook.Close False
End If

我试图通过添加解决方法

I tried to work around it by adding

OpenBook.Sheets(1).UsedRange.Select
Selection.NumberFormat = "@"

但这并不能解决问题.

那我该如何粘贴内容而不丢失开头的"0"?

So how do I paste the contents and not lose leading "0"?

推荐答案

请尝试下一个代码.它将使用其他方式打开文本文件.如果列数不是恒定的，则代码将首先对它们进行计数，然后构建一个能够根据需要打开文件的数组:

Please, try the next code. It will use a different way of opening the text file. If the number of columns is not constant, the code will firstly count them and then build an array able to make the file opening as you need:

Sub openAsTextTextFormat()
 Dim FileToOpen As Variant, arrTXT, nrCol As Long, arr(), i As Long
 Dim OpenBook As Workbook

 FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")


 If FileToOpen <> False Then
    'Check the number of text file columns:
    arrTXT = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(FileToOpen, 1).ReadAll, vbLf)
    nrCol = UBound(Split(arrTXT(0), vbTab))
    '______________________________________
    ReDim arr(nrCol) 'redim the format array
    For i = 0 To nrCol
        arr(i) = Array(1, 2) 'fill the format array with variant for TEXT Format!
    Next

    Workbooks.OpenText FileName:=FileToOpen, origin:=932, startRow:=1, DataType:=xlDelimited, _
        other:=True, OtherChar:="|", FieldInfo:=arr()
    Set OpenBook = ActiveWorkbook
    Stop ' The code will stop here! Please, check if the text file has been open with the correct format.
    OpenBook.Sheets(1).UsedRange.Copy ThisWorkbook.Worksheets("BOM").Range("C1")
      
    OpenBook.Close False
 End If
End Sub

这篇关于如何粘贴前导"0"的文本在Excel中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！