以文本类型从txt文件导入到Excel

Question

嗨，我给了一个VBA代码谁在Excel中导入一些txt文件和做一些文本列，并取代。一无所有。我希望一些列是文本格式之前，我做替换。

我该怎么做呢?

下面是我的代码:

Sub ImportFichierTxt()

Dim ObjFSO As FileSystemObject
Dim VarFileToOpen As Variant
Dim WkNewWk As Worksheet
Dim ObjFileToRead As Object
Dim LngLine As Long
Dim StrLine As String
Dim fnd As Variant
Dim rplc As Variant

fnd = "."
rplc = ""

Application.ScreenUpdating = False

VarFileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If VarFileToOpen = False Then Exit Sub
Set WkNewWk = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Set ObjFileToRead = ObjFSO.OpenTextFile(VarFileToOpen, ForReading)
LngLine = 1
Do While ObjFileToRead.AtEndOfStream = False
StrLine = ObjFileToRead.ReadLine
WkNewWk.Cells(LngLine, 1) = StrLine
LngLine = LngLine + 1
Loop
ObjFileToRead.Close

WkNewWk.Columns("A:A").TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, _
1), Array(59, 1), Array(80, 1), Array(92, 1), Array(105, 1), Array(123, 1), Array(134, 1), _
Array(146, 1), Array(165, 1)), TrailingMinusNumbers:=True

WkNewWk.Columns.AutoFit



Dim Cell As Range, NoCol As Integer
Dim NoLig As Long, DerLig As Long, Var As Variant



DerLig = Split(WkNewWk.UsedRange.Address, "$")(4)

'Fixe le N° de la colonne à lire
NoCol = 2





For NoLig = 1 To DerLig
Var = WkNewWk.Cells(NoLig, NoCol)

If Var = "MOIS DE" Then
With WkNewWk.Cells(NoLig + 1, NoCol)
.NumberFormat = "m/d/yyyy;@"
WkNewWk.Cells.Replace what:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End With

End If
Next
Set WkNewWk = Nothing
Set ObjFSO = Nothing
Set ObjFileToRead = Nothing



End Sub

在txt文件中，数字格式是123.870行，当我导入txt文件时，末尾的0消失了。

Answer 1

你得到的每一列都是在这部分的代码中确定的:

Array(Array(0, _
1), Array(59, 1), Array(80, 1), Array(92, 1), Array(105, 1), Array(123, 1), Array(134, 1), _
Array(146, 1), Array(165, 1))

所以Array(0, 1)是你的第一列,Array(59, 1)是第二个等等。

找到要作为文本的列，并将1更改为2。所以，一个例子，如果你的目标列是第二个，只需执行Array(59, 2)，它应该被认为是文本