軟體: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