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

※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 60.250.67.78 ※ 文章網址: https://webptt.com/m.aspx?n=bbs/Office/M.1540353131.A.D50.html