1

I have multiple ascii files in a folder. I want to open each ascii file with delimiter comma, then I want to do some operation and save the file as Excel work book without overwriting the ascii file. Like this need to do for many files ascii file using macro. Anyone help me write excel macro code.

In the loop need to open the ascii file and do the my job then save the file as Excel file in the final folder.then reading next ascii file in the files actually.

But my code is opening the the first ascii file doing the my job, then saving as new Excel file. Instead of opening the next ascii file, it is doing the job on the saved Excel file.

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Siddhu
  • 48
  • 6
  • You should at least post you attempts. People may help you with that but it is unlikely that people here are just doing the job for you. – Ronald Jul 05 '20 at 14:05
  • code added brother – Siddhu Jul 05 '20 at 14:16
  • Much better this way :-). Even better if you can pinpoint the location in the code where things don't go as expected. I'm sorry that personally I can't help you with this, because I'm not really an Excel-macro expert :-/, but I'm sure there will be others. – Ronald Jul 05 '20 at 14:25
  • VTC, no question and no hint where there might be a problem with the code. And [Anyone help me write excel macro code](https://meta.stackoverflow.com/questions/284236/why-is-can-someone-help-me-not-an-actual-question) is not a question – Storax Jul 05 '20 at 15:32
  • I mentioned my problem @Storax – Siddhu Jul 06 '20 at 07:35
  • It seems you are missing a call to `Dir` in the lop, i.e. `filename=Dir` before `loop`, see this [eaxmple](https://stackoverflow.com/a/45749626/6600940). – Storax Jul 06 '20 at 13:08

1 Answers1

1

You need to do somthing like that

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
Storax
  • 11,158
  • 3
  • 16
  • 33