软件:excel
版本:2007
改了一版vba可以指定文字档案多少列数
平均分配到工作表去
不过现在遇到的问题是
若是刚好文字档案的列数可以整除设定值
就会出错
例如我设定 50
有个300列的文字档案就会整除
然后就出错
没有整除的文字档案就ok
不知道这边要怎么修改
还有分割档案那边判断要用什么方式切割的部分
因为下面会重复写一次
不知道有什么缩短方式写法...
档案在下面:
https://goo.gl/LqxTmG
宏:
Sub ReadTxtByinput()
Dim myFile$, Jm, AA$, uMax$, xArr(), xR As Range
Sheets("output").Select
uMax = Range("B2")
fn = Range("B4")
savefn = Range("B3") & Range("B4")
delnum = Range("B5")
myFile = Application.GetOpenFilename("allfiles, *.txt*")
If myFile = "False" Then
Exit Sub
End If
Workbooks.Add 1
ActiveWorkbook.SaveAs savefn
Workbooks(fn).Activate
Set xR = Sheets("Sheet1").[a1]
Open myFile For Input As #1
Do While Not EOF(1)
Line Input #1, AA
If Jm = 0 Then ReDim xArr(1 To uMax, 0)
Jm = Jm + 1: xArr(Jm, 0) = AA
If Jm = uMax Then
xR.Resize(uMax).Value = xArr
'分割的选择
If delnum = "" Or delnum = "不分割" Then
ElseIf delnum = "TAB" Then
[a:a].TextToColumns [a1], xlDelimited, Tab:=True
ElseIf delnum = "逗号" Then
[a:a].TextToColumns [a1], xlDelimited, Semicolon:=True
ElseIf delnum = "分号" Then
[a:a].TextToColumns [a1], xlDelimited, Comma:=True
ElseIf delnum = "空格" Then
[a:a].TextToColumns [a1], xlDelimited, Space:=True
End If
Jm = 0: Set xR = Sheets.Add(, Sheets(Sheets.Count)).[a1]
End If
Loop
Close #1
If Jm > 0 Then xR.Resize(uMax).Value = xArr
'分割的选择
If delnum = "" Or delnum = "不分割" Then
ElseIf delnum = "TAB" Then
[a:a].TextToColumns [a1], xlDelimited, Tab:=True
ElseIf delnum = "逗号" Then
[a:a].TextToColumns [a1], xlDelimited, Semicolon:=True
ElseIf delnum = "分号" Then
[a:a].TextToColumns [a1], xlDelimited, Comma:=True
ElseIf delnum = "空格" Then
[a:a].TextToColumns [a1], xlDelimited, Space:=True
End If
Erase xArr
Workbooks(fn).Close (1)
MsgBox "档案已经储存在" & savefn
End Sub