|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
你这个是UTF-8格式的文件,简单提取了下,不知道你最终要实现的表格数据效果,根据结果再用数组处理一次就行。
- Sub test()
- Dim i%, j%, s, st, arrByte() As Byte, arr()
- Application.ScreenUpdating = False
- Filename = ThisWorkbook.Path & "" & Dir(ThisWorkbook.Path & "" & "*.txt") '获取当前目录下的txt文件
- If Len(Dir(ThisWorkbook.Path & "" & "*.txt")) Then '判断是否存在txt文件
- Open Filename For Binary Access Read As #1
- ReDim arrByte(LOF(1) - 1)
- Get #1, , arrByte
- Close #1
- s = Split(ByteToStr(arrByte, "UTF-8"), Chr(10)) '调用ByteToStr函数,s获取UTF-8或Unicode编码的内容
- Else
- MsgBox "当前目录下没有txt文件!": Exit Sub '当前目录下没有txt文件,退出程序
- End If
- If UBound(s) < 0 Then MsgBox "txt文件中没有数据!": Exit Sub 'txt文件中没有内容,退出程序
- ReDim arr(1 To UBound(s) + 1, 1 To 100) '预设100列
- For i = 0 To UBound(s)
- st = Split(s(i), ",") '以逗号将数组s的内容分割开
- If UBound(arr, 2) > UBound(st) + 1 And UBound(st) > 0 Then ReDim Preserve arr(1 To UBound(s) + 1, 1 To UBound(st) + 1) '根据数组st中的最大个数重新设置arr的列数
- For j = 0 To UBound(st)
- arr(i + 1, j + 1) = st(j)
- Next
- Next
- With Sheets("原始数据")
- .UsedRange.ClearContents
- .[A1].Resize(UBound(arr), UBound(arr, 2)) = arr
- End With
- Application.ScreenUpdating = True
- MsgBox "txt文件读取完成!", "64", "温馨提示"
- End Sub
- Function ByteToStr(arrByte, strCharset As String) As String 'ByteToStr函数读取UTF-8或Unicode编码的内容
- With CreateObject("Adodb.Stream")
- .Type = 1
- .Open
- .Write arrByte
- .Position = 0
- .Type = 2
- .Charset = strCharset
- ByteToStr = .Readtext
- .Close
- End With
- End Function
复制代码
|
评分
-
1
查看全部评分
-
|