how to generate PF ECR text file automatic from excel – vba code

Hey!

Below Excel VBA code with ready made process to generate and check monthly PF provident fund ECR (Electronic Challan Report) automatic from Excel in 1 click.

How to use generate ECR text file from Excel: – Steps

1. Follow the ECR Format as downloaded from Unified portal (PF Website)
2. Fill the all columns e.g. wage, contribution and days etc
3. Copy below given code and paste it in your VBA Module 1 a Explain in video
4. change the very first loop counts in you wish you change
5. Press F5 or run this code to generate ECR text file.
6. That’s All.

Sub ECR_Creator_code()
'this code made by Ajit Sir
'Follow on www.99Excel.com
'Follow on payrollpedia.org
Application.ScreenUpdating = True
Dim fso As Object, oFile As Object, FilePath, i, c, Livevalue, FolderPath, FileName, e As Long
FolderPath = "C:\Examples"
FileName = "PFECR_" & UCase(Format(Now(), "DD_MMMM_YYYY")) & ".txt"
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(FolderPath) Then fso.CreateFolder FolderPath
Set oFile = fso.CreateTextFile(FolderPath & "\" & FileName)


For i = 2 To (Application.WorksheetFunction.CountA(Range("A2:A60000")) + 1)
    For c = 3 To 8
    Cells(i, c).Select
        If Cells(i, c).Value < 0 Then
        Cells(i, c).Interior.Color = 255
        e = e + 1
        Else
        Cells(i, c).Interior.Pattern = xlNone
        End If
    Next c
Next i


If e = 0 Then

For i = 2 To (Application.WorksheetFunction.CountA(Range("A2:A60000")) + 1)
    For c = 1 To 11
        If c = 1 Then
        Livevalue = Cells(i, c).Value & "#~#"
        Else
        Livevalue = Livevalue & Cells(i, c).Value & "#~#"
        End If
        
        If Cells(i, c).Value < 0 Then
        Range("E3").Interior.Color = 255
        Else: End If
    Next c
oFile.WriteLine Livevalue
Cells(i, 1).Select
Next i



oFile.Close
Set fso = Nothing
Set oFile = Nothing
Application.ScreenUpdating = False
MsgBox " ECR Saved in C:\Examples"
Else
MsgBox "error in ECR"
End If
End Sub