使用VBA在Excel中打开ASCII文件以获取多个文件-循环在同一输出文件中运行,而不是下一个文件

悉达

我在一个文件夹中有多个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] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章