txt文件分割(一个大txt文件可按字数分割为若干小文件)vba实例

     当txt文本文件字数比较多时,读写很不方便,这时我们可借助以下excel vba代码实现txt文件的分割,分割后每个文件的字数及总文件的路径及全名只需在代码中替换即可。

1、按字符分割(保留完整行)

Sub split_txtfile()
Dim bigfile As String, smallfile As String
Dim current_line As String, all_line As String
Dim file_count As Integer, wordcount As Integer
wordcount = 20000 ''分割后每个文件的字节数,可自己修改
''总文件的完整路径及名称需在下行代码进行替换
''以下为调用excel内置api
On Error Resume Next
With Application.FileDialog(msoFileDialogOpen)
    .Title = "请选择你要的文件"
    .AllowMultiSelect = True
    .InitialFileName = "C:\Users\Administrator\Desktop\"
    .Filters.Clear
    .Filters.Add "all files", "*.txt,*.xls,*.xlsx,*.dwg"
    If .Show = True Then
    Set gof = .SelectedItems
    bigfile_fullname = gof.Item(1)
 
    Else: Exit Sub
    End If
End With
''上面从on error resume next 行开始到此行为excel内置打开对话框的函数,如果不用excel vba,可删除上面代码,
''手工录入文件路径及全程 bigfile_fullname =  "C:\Users\Administrator\Desktop\*.txt"
Open bigfile_fullname For Input As #1
Do While Not EOF(1)
  Line Input #1, current_line
  If all_line <> "" Then
       all_line = all_line & vbCrLf & current_line
   Else
      all_line = current_line
   End If
  
  If Len(all_line) > wordcount Then
    file_count = file_count + 1
    small_file = Left(bigfile_fullname, Len(bigfile_fullname) - 4) & "_" & file_count & ".txt"
    Open small_file For Output As #2
    Print #2, all_line
    Close #2
    all_line = ""
  End If

Loop
If all_line <> "" Then
    file_count = file_count + 1
    small_file = Left(bigfile_fullname, Len(bigfile_fullname) - 4) & "_" & file_count & ".txt"
    Open small_file For Output As #2
    Print #2, all_line
    Close #2
    all_line = ""
End If
 Close #1
 MsgBox "txt分割已完成" & Space(50) & vbCr & _
 "vba代码二次开发qq:443440204", vbInformation, "版权所有qq:443440204"
End Sub

2、按字符分割(不保留完整行)

Sub 按字符分割txt()
'' split_txtfile()
Dim bigfile As String, smallfile As String
Dim current_line As String, all_line As String
Dim file_count As Integer, wordcount As Integer
wordcount = 2000 ''分割后每个文件的字节数,可自己修改
''总文件的完整路径及名称需在下行代码进行替换
''以下为调用excel内置api
On Error Resume Next
With Application.FileDialog(msoFileDialogOpen)
    .Title = "请选择你要的文件"
    .AllowMultiSelect = True
    .InitialFileName = "C:\Users\Administrator\Desktop\"
    .Filters.Clear
    .Filters.Add "all files", "*.txt,*.xls,*.xlsx,*.pdf,*.dwg"
    If .Show = True Then
    Set gof = .SelectedItems
    bigfile_fullname = gof.Item(1)
    Else: Exit Sub
    End If
End With
''上面从on error resume next 行开始到此行为excel内置打开对话框的函数,如果不用excel vba,可删除上面代码,
''手工录入文件路径及全称 bigfile_fullname =  "C:\Users\Administrator\Desktop\*.txt"
Open bigfile_fullname For Input As #1
Do While Not EOF(1)
  current_line = Input(30, #1)
  If all_line <> "" Then
       all_line = all_line & current_line
   Else
      all_line = current_line
   End If
  
  If Len(all_line) > wordcount Then
    file_count = file_count + 1
    small_file = Left(bigfile_fullname, Len(bigfile_fullname) - 4) & "_" & file_count & ".txt"
    Open small_file For Output As #2
    Print #2, all_line
    Close #2
    all_line = ""
  End If
Loop
If all_line <> "" Then
    file_count = file_count + 1
    small_file = Left(bigfile_fullname, Len(bigfile_fullname) - 4) & "_" & file_count & ".txt"
    Open small_file For Output As #2
    Print #2, all_line
    Close #2
    all_line = ""
End If
 Close #1
 MsgBox "txt分割已完成" & Space(50) & vbCr & _
 "vba代码二次开发qq:443440204", vbInformation, "版权所有qq:443440204"
End Sub

本文附有xlsm文件可供下载参考使用。 

另:若print输出若想不换号,加个分号即可,Print #2;

Print #1, "Hello" ; " " ; "World"    ' 以空格隔开两个字符串。
Print #1, Spc(5) ; "5 leading spaces "    ' 在字符串之前写入五个空格。
Print #1, Tab(10) ; "Hello"    ' 将数据写在第十列。
 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

山水CAD插件定制

你的鼓励是我创作最大的动力!

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值