我在一个文件夹中有多个ascii文件。我想用定界符逗号打开每个ascii文件,然后我要执行一些操作并将文件另存为Excel工作簿,而不会覆盖ascii文件。像这样需要使用宏为许多文件做ascii文件。任何人都可以帮助我编写excel宏代码。
在循环中,需要打开ascii文件并执行我的工作,然后将文件另存为Excel文件在最终文件夹中。然后实际读取文件中的下一个ascii文件。
但是我的代码是打开第一个执行我的工作的ascii文件,然后另存为新的Excel文件。而不是打开下一个ascii文件,而是在保存的Excel文件上执行此工作。
Sub ProcessFiles()
Dim Filename, Pathname As String
Dim wb As Workbook
Pathname = ThisWorkbook.path & "\files\"
Filename = Dir(Pathname)
Do While Filename <> ""
Call Workbooks.OpenText(Filename:=Pathname & Filename, DataType:=xlDelimited, Comma:=True)
Set wb = ActiveWorkbook
DoWork wb
wb.SaveAs Filename:=wb.path & "\final\" & wb.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, ConflictResolution:=True
wb.Saved = True
wb.Close
'wb.Close SaveChanges:=True
'Filename = Dir(Pathname & "*.xlsx")
Loop
MsgBox "Successfully Completed. Developed By Siddhu"
End Sub
Sub DoWork(wb As Workbook)
With wb
myjob goes here in each file
End With
End Sub
你需要做这样的事情
Sub ProcessFiles()
Dim Filename, Pathname As String
Dim wb As Workbook
Pathname = ThisWorkbook.path & "\files\"
Filename = Dir(Pathname)
Do While Filename <> ""
Call Workbooks.OpenText(Filename:=Pathname & Filename, DataType:=xlDelimited, Comma:=True)
Set wb = ActiveWorkbook
DoWork wb
wb.SaveAs Filename:=wb.path & "\final\" & wb.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, ConflictResolution:=True
wb.Saved = True
wb.Close
'wb.Close SaveChanges:=True
'Filename = Dir(Pathname & "*.xlsx")
Filename = Dir `<=== this will go to the next file in the directory
Loop
MsgBox "Successfully Completed. Developed By Siddhu"
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句