我有一个文本文件,格式如下:
- 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
但是,我的代码无法判断计划类型下的空白和福利计划下的空白实际上是两个不同的数据列.它将两列下的空白处理为一个长空格,并且不保留格式.
从视觉上我们知道有列,但我的代码看不到这一点.
有没有办法对此进行编程,以便它识别出文本文件中有两个空格而不是一个大空格?
我想要避免的是必须用字符手动消除它.那可能吗?
假设每列长度为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