将导入的txt文件数据中的数字转换为文本格式 [英] Convert numbers in imported txt file data to text format

问题描述

我正在重用代码导入多个文本文件进入工作簿，其中工作表名称与文本文件名称匹配

这段代码正在创建多个工作表，并将管道分隔的数据导入到单独的列中.

This code is creating multiple worksheets and importing pipe delimited data into individual columns.

我需要先将所有单元格设置为文本限定"，然后再将其转换为列.

I need all the cells to be set to Text qualified before it does the text to columns.

基本上，我希望所有列均采用文本格式，而不是默认的常规，因为我文件中的16位数字已被常规格式弄乱了.

Basically I want all the columns to be in text format instead of default General as the 16 digit numbers that I have in my file are getting messed up with General format.

我确实尝试将行放在下面，但是它会在文本到列完成后更改格式.

I did try to put below lines but it changes the format after text to column is completed.

cells.select
Selection.NumberFormat = "@"

这是我正在使用的代码

Sub Extract()
Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim sDelimiter As String
On Error GoTo ErrHandler
Application.ScreenUpdating = False

sDelimiter = "|"

FilesToOpen = Application.GetOpenFilename _
    (FileFilter:="Text Files (*.txt), *.txt", _
    MultiSelect:=True, Title:="Text Files to Open")

If TypeName(FilesToOpen) = "Boolean" Then
    MsgBox "No Files were selected"
    GoTo ExitHandler
End If


Set wkbAll = Application.ActiveWorkbook
x = 1
With Workbooks.Open(Filename:=FilesToOpen(x))
    .Worksheets(1).Columns("A:A").TextToColumns _
        Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
        Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
        Other:=True, OtherChar:="|"
    .Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    .Close False
End With

x = x + 1

While x <= UBound(FilesToOpen)
    With Workbooks.Open(Filename:=FilesToOpen(x))
        .Worksheets(1).Columns("A:A").TextToColumns _
            Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            Tab:=False, Semicolon:=False, _
            Comma:=False, Space:=False, _
            Other:=True, OtherChar:=sDelimiter
        .Sheets(1).Move  After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

    End With
    x = x + 1
Wend

wkbAll.Save
ExitHandler:
Application.ScreenUpdating = True
Set wkbAll = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler

End Sub

推荐答案

不是永久解决方案，但我添加了以下更改，看起来似乎解决了我的问题.由于我的文件之一中最长的记录具有45个单元格，因此我自动记录到Macro下方，并附加到OtherChar之后的代码中现在它可以按我的意愿工作了.

Not the permanent solution but I added below change which looks like resolved my problem. Since the longest record in one of my file has 45 cells I auto recorded below Macro and appended to my code after OtherChar:="|" and now it is working as I wanted.

OtherChar:="|", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, _
    2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12 _
    , 2), Array(13, 2), Array(14, 2), Array(15, 2), Array(16, 2), Array(17, 2), Array(18, 2), _
    Array(19, 2), Array(20, 2), Array(21, 2), Array(22, 2), Array(23, 2), Array(24, 2), Array( _
    25, 2), Array(26, 2), Array(27, 2), Array(28, 2), Array(29, 2), Array(30, 2), Array(31, 2), _
    Array(32, 2), Array(33, 2), Array(34, 2), Array(35, 2), Array(36, 2), Array(37, 2), Array( _
    38, 2), Array(39, 2), Array(40, 2), Array(41, 2), Array(42, 2), Array(43, 2), Array(44, 2), _
    Array(45, 2)), TrailingMinusNumbers:=True

这篇关于将导入的txt文件数据中的数字转换为文本格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！