Automated Credit Note PDF Generation in Power Automate Desktop

Pro Tip: Build a fully automated Credit Note generation system in Power Automate Desktop (PAD) using Excel, Word templates, and VBScript—no manual editing required.

Flow Overview

1. Get current date and time for folder creation
2. Read configuration paths from an Excel config file
3. Retrieve input files dynamically
4. Execute VBScript to generate PDFs
5. Log results in a date-time-based folder

Step-by-Step Workflow

Step 1

Get Current Date and Time

Use the DateTime.GetCurrentDateTime action to fetch the system date and time, stored in CurrentDateTime.

Step 2

Format the Date

Create a folder-friendly date format using:

SET CurrentDate TO "%CurrentDateTime.Day%_%CurrentDateTime.Month%_%CurrentDateTime.Year%"

Step 3

Select Configuration File

Prompt the user to select an Excel configuration file:

Display.SelectFileDialog
Title: "Please provide the 'Credit Note' config File"
SelectedFile => SelectedFile
ButtonPressed => ButtonPressed

Step 4

Read Config Data

Once the config file is selected, open and read it using Excel actions:

Input Folder – Source of Excel files
Output Folder – Destination for generated PDFs
Address Mapping File – Customer address data
Template File – Word template with placeholders

Step 5

Retrieve Input Files

Use Folder.GetFiles to gather all Excel files from the input folder.

Step 6

Validate Input Folder

If no files are found, display an error and exit gracefully:

IF IsEmpty(InPutfiles)
THEN Display.ShowMessageDialog "Input folder does not contain files"
EXIT

Step 7

Create Output Folders

The flow dynamically creates folders based on current date and time:

Date Folder: YYYY_MM_DD
Time Subfolder: Hour_Minute_Second

OutputFolder/14_01_2026/10_22_45/

Step 8

Loop Through Input Files

Iterate through all Excel files in the input directory using Loop Foreach to process each one.

Step 9

Run VBScript for PDF Creation

The VBScript handles reading Excel data, mapping addresses, replacing Word placeholders, and saving PDFs.

Full VBScript Reference

This is the complete VBScript used inside Power Automate Desktop to generate Credit Note PDFs dynamically.


Dim objWord, objDoc, objNewDoc
Dim result 
Dim spelledOutNumber,numText
Dim TotalProcessed, TotalSuccess, TotalFailed
TotalProcessed = 0
TotalSuccess = 0
TotalFailed = 0

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("%InputFile%")
sheetName = "Sheet1"  
Set objWorkbook2 = objExcel.Workbooks.Open("%AddressMappingFile%")
AddSheetName = "Sheet1"  


Set objSheet = objWorkbook.Sheets(sheetName)
Set AddressSheet = objWorkbook2.Sheets(AddSheetName)

Set objRange = objSheet.UsedRange

' Find the last used row in column A (Column 1)
Dim lastRow
lastRow = objSheet.Cells(objSheet.Rows.Count, 1).End(-4162).Row

' Display the last row number
' WScript.Echo "Last available row in column A: " & lastRow

Dim colBValue, colDValue, colEValue

Function ConvertCurrencyToEnglish(ByVal MyNumber)
   Dim Temp
   Dim DecimalPlace, Count

   ReDim Place(9)

   Place(2) = " Thousand "
   Place(3) = " Million "
   Place(4) = " Billion "
   Place(5) = " Trillion "

   ' Convert MyNumber to a string, trimming extra spaces.
   MyNumber = Trim(CStr(MyNumber))

   ' Find decimal place.
   DecimalPlace = InStr(MyNumber, ".")

   ' If we find a decimal place...
   If DecimalPlace > 0 Then
      ' Convert cents
      Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
      Cents = ConvertTens(Temp)
      ' Strip off cents from remainder to convert.
      MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
   End If

   Count = 1
   Result = ""

   Do While MyNumber <> ""
      ' Convert last 3 digits of MyNumber to English dollars.
      Temp = ConvertHundreds(Right(MyNumber, 3))
      If Temp <> "" Then Result = Temp & Place(Count) & Result
      If Len(MyNumber) > 3 Then
         ' Remove last 3 converted digits from MyNumber.
         MyNumber = Left(MyNumber, Len(MyNumber) - 3)
      Else
         MyNumber = ""
      End If
      Count = Count + 1
   Loop

   ConvertCurrencyToEnglish = Trim(Result)
End Function

Function ConvertHundreds(ByVal MyNumber)
   Dim Result

   ' Exit if there is nothing to convert.
   If CInt(MyNumber) = 0 Then Exit Function

   ' Append leading zeros to number.
   MyNumber = Right("000" & MyNumber, 3)

   ' Do we have a hundreds place digit to convert?
   If Left(MyNumber, 1) <> "0" Then
      Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
   End If

   ' Do we have a tens place digit to convert?
   If Mid(MyNumber, 2, 1) <> "0" Then
      Result = Result & ConvertTens(Mid(MyNumber, 2))
   Else
      ' If not, then convert the ones place digit.
      Result = Result & ConvertDigit(Mid(MyNumber, 3))
   End If

   ConvertHundreds = Trim(Result)
End Function

Function ConvertTens(ByVal MyTens)
   Dim Result

   ' Is the value between 10 and 19?
   If CInt(Left(MyTens, 1)) = 1 Then
      Select Case CInt(MyTens)
         Case 10: Result = "Ten"
         Case 11: Result = "Eleven"
         Case 12: Result = "Twelve"
         Case 13: Result = "Thirteen"
         Case 14: Result = "Fourteen"
         Case 15: Result = "Fifteen"
         Case 16: Result = "Sixteen"
         Case 17: Result = "Seventeen"
         Case 18: Result = "Eighteen"
         Case 19: Result = "Nineteen"
         Case Else
      End Select
   Else
      ' ... otherwise, it's between 20 and 99.
      Select Case CInt(Left(MyTens, 1))
         Case 2: Result = "Twenty "
         Case 3: Result = "Thirty "
         Case 4: Result = "Forty "
         Case 5: Result = "Fifty "
         Case 6: Result = "Sixty "
         Case 7: Result = "Seventy "
         Case 8: Result = "Eighty "
         Case 9: Result = "Ninety "
         Case Else
      End Select
      ' Convert ones place digit.
      Result = Result & ConvertDigit(Right(MyTens, 1))
   End If

   ConvertTens = Result
End Function

Function ConvertDigit(ByVal MyDigit)
   Select Case CInt(MyDigit)
      Case 1: ConvertDigit = "One"
      Case 2: ConvertDigit = "Two"
      Case 3: ConvertDigit = "Three"
      Case 4: ConvertDigit = "Four"
      Case 5: ConvertDigit = "Five"
      Case 6: ConvertDigit = "Six"
      Case 7: ConvertDigit = "Seven"
      Case 8: ConvertDigit = "Eight"
      Case 9: ConvertDigit = "Nine"
      Case Else: ConvertDigit = ""
   End Select
End Function


Sub CustomReplaceText(findText, replaceText)
        With objWord.Selection.Find
            .ClearFormatting
            .Text = findText
            .Replacement.ClearFormatting
            .Replacement.Text = replaceText
            .Forward = True
            .Wrap = 1 ' wdFindContinue (continue searching through the document)
            .Execute , , , , , , , , , , 2 ' wdReplaceAll (replace all occurrences)
        End With
End Sub


' Loop through each row in the used range starting from row 3
For rowIndex = 4 To lastRow - 1 
CustomerName = ""
Address1 = ""
Address2 = ""
City = ""
PostalCode = ""

TotalProcessed = TotalProcessed + 1

 

    ' Get the values in columns B, D, and E for the current row
    Lable = objSheet.Cells(rowIndex, 1).Value ' Column B
    Amount = objSheet.Cells(rowIndex, 2).Value ' Column B
    CreNote = objSheet.Cells(rowIndex, 4).Value ' Column D
    inDate = objSheet.Cells(rowIndex, 5).Value ' Column E
WScript.Echo "--------------------------------------------------"
WScript.Echo "Credit Note No : " & CreNote
WScript.Echo "Customer Name  : " & Lable
WScript.Echo "Date           : " & inDate
WScript.Echo "Amount         : " & FormatNumber(Amount, 2)
WScript.Echo "PDF Generated  : YES"
WScript.Echo "PDF Path       : " & Lable & ".pdf"

    Dim dateString
    dateString = inDate


    Dim dateParts
    dateParts = Split(dateString, ".")

    ' Extract the day, month, and year from the array
    Dim monthName, yearValue

    If UBound(dateParts) = 2 Then
        monthNumber = dateParts(1)
        yearValue = dateParts(2)


        ' Define an array of month names
        Dim monthNames
        monthNames = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")

        ' Check if the month number is within a valid range
        If monthNumber >= 1 And monthNumber <= 12 Then
            monthName = monthNames(monthNumber - 1) ' Subtract 1 because arrays are zero-based
            WScript.Echo "Month: " & monthName
            WScript.Echo "Year: " & yearValue
            WScript.Echo "Month / Year   : " & monthName & " " & yearValue

        Else
            WScript.Echo "Invalid month number."
        End If
    Else
        WScript.Echo "Invalid date format."
    End If

    
    spelledOutNumber = ConvertCurrencyToEnglish(Amount) 
    numText = spelledOutNumber & " Only"

    ' Set the filter value
    filterValue = Lable
    AddressFound = False


    ' Apply AutoFilter to the first row (header) with the filter value
    AddressSheet.Rows(1).AutoFilter 1, filterValue

    ' Get the visible rows (excluding the header)
    Set visibleRows = AddressSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(12) ' xlCellTypeVisible

    ' Loop through the visible rows to retrieve the values from the first visible row
    For Each row In visibleRows.Rows
            CustomerName = row.Cells(1, 2).Value ' Column A
            Address1 = row.Cells(1, 3).Value
            Address2 = row.Cells(1, 4).Value 
            City = row.Cells(1, 5).Value 
            PostalCode = row.Cells(1, 6).Value
        Exit For 
    Next
    WScript.Echo "Address        : " & City


    ' Clear the AutoFilter
    AddressSheet.AutoFilterMode = False


    ' Set the path to your input Word document
    Dim docPath
    docPath = "%TemplateFile%"

    ' Create an instance of Word
    Set objWord = CreateObject("Word.Application")
    objWord.Visible = True

    ' Open the input Word document
    Set objDoc = objWord.Documents.Open(docPath)

    Dim inputString
    inputString = CreNote

    If InStr(inputString, "CD") > 0 Then
        crenote2 = "Auto Credit Note for the month of " & monthName & " " & yearValue
    Else
        crenote2 = "Auto Credit Note for the month of  " & yearValue
    End If

    ' Call the custom function for each replacement
    Call CustomReplaceText("{Date}", inDate)
    Call CustomReplaceText("{CreNote1}", CreNote)
    Call CustomReplaceText("{CreNote2}", crenote2)
    Call CustomReplaceText("{Amo1}", Amount)
    Call CustomReplaceText("{Amo2}", Amount)
    Call CustomReplaceText("{NAME}", CustomerName)
    Call CustomReplaceText("{Address1}", Address1)
    Call CustomReplaceText("{Address2}", Address2)
    Call CustomReplaceText("{City}", City)
    Call CustomReplaceText("{PostalCode}", PostalCode)
    Call CustomReplaceText("{AmoInText}", numText)

    ' Save the modified content as a new Word document

    ' Save the document as a PDF
    Dim pdfPath
    pdfPath = "%TodaysFolder%\\" & Lable & ".pdf"
    objDoc.SaveAs2 pdfPath, 17 ' 17 represents wdFormatPDF
    TotalSuccess = TotalSuccess + 1
    
    WScript.Echo Lable&"_PDF: "&pdfPath

    ' Close the input Word document
    objDoc.Close False ' Don't save changes to the input document

    ' Quit Word
    objWord.Quit

    ' Release the objects
    Set objWord = Nothing
    Set objDoc = Nothing

WScript.Echo "Status         : SUCCESS"
WScript.Echo "--------------------------------------------------"
WScript.Echo ""

Next

' Close the workbook without saving changes (or save as needed)
objWorkbook.Close False
objWorkbook2.Close False

' Quit Excel
objExcel.Quit
WScript.Echo ""
WScript.Echo "=================================================="
WScript.Echo "SUMMARY"
WScript.Echo "=================================================="
WScript.Echo "Total Credit Notes Processed : " & TotalProcessed
WScript.Echo "Total PDFs Created           : " & TotalSuccess
WScript.Echo "Total Success                : " & TotalSuccess
WScript.Echo "Total Failed                 : " & TotalFailed
WScript.Echo "=================================================="

' Release the objects
Set objRange = Nothing
Set objSheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

Sample Log Output

==================================================
SUMMARY
==================================================
Total Credit Notes Processed : 5
Total PDFs Created           : 5
Total Success                : 5
Total Failed                 : 0
==================================================

✅ Conclusion: This flow automates Credit Note PDF creation end-to-end, integrating Excel, Word, and VBScript in Power Automate Desktop. It improves efficiency, reduces human error, and provides clear log outputs for auditing.

Author

Thiragabathina Mahesh – Power Automate Desktop Developer and Automation Enthusiast