我有一个文本文件,格式如下：
我在VBA中使用以下代码将文本文件写入excel：
Sub test()
Dim Fn As String,WS As Worksheet,st As String
Fn = "Path.txt" ' the file path and name
Set WS = Sheets("Sheet1")
'Read text file to st string
With CreateObject("Scripting.FileSystemObject")
If Not .FileExists(Fn) Then
MsgBox Fn & " : is missing."
Exit Sub
Else
If FileLen(Fn) = 0 Then
MsgBox Fn & " : is empty"
Exit Sub
Else
With .OpenTextFile(Fn,1)
st = .ReadAll
.Close
End With
End If
End If
End With
'Replace every two or more space in st string with vbTab
With CreateObject("VBScript.RegExp")
.Pattern = "[ ]{2,}"
.Global = True
.Execute st
st = .Replace(st,vbTab)
End With
'Put st string in Clipboard
With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.SetText st
.PutInClipboard
End With
'Paste Clipboard to range
WS.Range("A1").PasteSpecial
End Sub
我的目标是保留Excel中文本文件中的列.
但是,我的代码无法判断计划类型下的空白和福利计划下的空白实际上是两个不同的数据列.它将两列下的空白处理为一个长空格,并且不保留格式.
从视觉上我们知道有列,但我的代码看不到这一点.
有没有办法对此进行编程,以便它识别出文本文件中有两个空格而不是一个大空格？
我想要避免的是必须用字符手动消除它.那可能吗？
假设每列长度为10个字符,我会使用此宽度而不是空格分隔符
Sub FeedTextFiletoActiveSheet(ByVal TextFile As String)
Dim i As Integer,Line As String
Open TextFile For Input As #1
While Not EOF(#1)
i = i + 1
Input #1,Line
Range("A" & i) = Trim(Mid(Line,1,10)) 'Business ID
Range("B" & i) = Trim(Mid(Line,11,10)) 'Employee ID
' ... and so on
Wend
Close #1
End Sub
要使用它,只需调用FeedTextFiletoActiveSheet(“Path.txt”)