|
3学分
求大神帮忙修改一下,用以前的vba代码始终出问题!有代码备注最好,方便学习!Sub test()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set fso = CreateObject("scripting.filesystemobject")
For Each file In fso.getfolder(ThisWorkbook.Path).Files
If file.Name Like "*.txt" Then
Set mytext = fso.opentextfile(file.Path)
mycontent = mytext.readall
mytext.Close
arr = Split(mycontent, vbCrLf)
Set wb = Workbooks.Add
wb.Sheets(1).[a1].Resize(UBound(arr, 1)) = arr
wb.Sheets(1).[a1].CurrentRegion.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(10, 1), Array(17, 1), Array(23, 1), _
Array(28, 1), Array(31, 1), Array(36, 1), Array(41, 1), Array(48, 1), Array(50, 1)), _
TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "" & Split(file.Name, ".")(0) & ".xlsx"
ActiveWindow.Close True
End If
Next file
Application.ScreenUpdating =TRUE
Application.DisplayAlerts =TRUE
End Sub
Sub demo()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set fso = CreateObject("scripting.filesystemobject")
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
Path = ThisWorkbook.Path & "\"
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='text;HDR=No';Data Source =" & Path
For Each file In fso.getfolder(ThisWorkbook.Path).Files
If Not file.Name Like "*.txt" Then GoTo 1
Sql = "select * from " & file.Name
rs.Open Sql, conn, 3, 3
If rs.RecordCount <> 0 Then
[a1:g5000].ClearContents
[a1].CopyFromRecordset rs
[a:a].TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Space:=True
End If
rs.Close
NewFile = Path & Split(file.Name, ".")(0) & ".xlsx"
ActiveWorkbook.SaveAs Filename:=NewFile, FileFormat:=xlOpenXMLWorkbook
1:
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
祝順心,南無阿彌陀佛!
|
最佳答案
查看完整内容
Sub demo()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set fso = CreateObject("scripting.filesystemobject")
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
Path = ThisWorkbook.Path & "\"
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='text;HDR=No';Data Source =" & Path
For Each file In ...
|