Power Platform Community Forum Thread Details

Hi! This one is a doozy! So kudos to whoever figures it out! 🙂

There is a VBA button in a Word document that I'd like to trigger a flow in PA but deliver the Word doc as a PDF instead to the PA Flow for processing and SharePoint file creation in subsequent actions.

Button Code (Currently):

Private Sub CommandButton1_Click()
' Generate PDF file name and subject line
Dim teacher As String
Dim location As String
Dim dateValue As String

' Retrieve values from content controls
teacher = ActiveDocument.SelectContentControlsByTitle("Teacher").Item(1).Range.Text
location = ActiveDocument.SelectContentControlsByTitle("Location").Item(1).Range.Text
dateValue = ActiveDocument.SelectContentControlsByTitle("Date").Item(1).Range.Text

' Create a valid file name by removing invalid characters
Dim subjectLine As String
subjectLine = teacher & " - " & location & " - " & dateValue
subjectLine = Replace(subjectLine, "\", "_")
subjectLine = Replace(subjectLine, "/", "_")
subjectLine = Replace(subjectLine, ":", "_")
subjectLine = Replace(subjectLine, "*", "_")
subjectLine = Replace(subjectLine, "?", "_")
subjectLine = Replace(subjectLine, """", "_")
subjectLine = Replace(subjectLine, "<", "_")
subjectLine = Replace(subjectLine, ">", "_")
subjectLine = Replace(subjectLine, "|", "_")

Dim pdfFileName As String
pdfFileName = Environ("TEMP") & "\" & subjectLine & ".pdf"

' Save the document as PDF
ActiveDocument.ExportAsFixedFormat OutputFileName:=pdfFileName, ExportFormat:=wdExportFormatPDF

' Read the PDF file as binary
Dim fileContent() As Byte
fileContent = GetFileContent(pdfFileName)

' Convert the byte array to a Base64 encoded string
Dim base64String As String
base64String = EncodeBase64(fileContent)

' Create a new HTTP request
Dim httpRequest As Object
Set httpRequest = CreateObject("MSXML2.XMLHTTP")

' Set up the HTTP request
httpRequest.Open "POST", "https://prod-18.westus.logic.azure.com:443/workflows/ecb12d41c17a44699102505b76b29702/triggers/manual/paths/invoke?api-version=2016-06-01", False
httpRequest.setRequestHeader "Content-Type", "application/pdf"

' Send the Base64 encoded string in the request body
httpRequest.send base64String

' Clean up: delete the temporary PDF file
Kill pdfFileName
End Sub

Function GetFileContent(filePath As String) As Byte()
' Read file content as binary
Dim fileNum As Integer
fileNum = FreeFile()
Open filePath For Binary As fileNum
GetFileContent = InputB(LOF(fileNum), fileNum)
Close fileNum
End Function

Function EncodeBase64(data() As Byte) As String
' Encode byte array to Base64 string
Dim xml As Object
Set xml = CreateObject("MSXML2.DOMDocument")
Dim elem As Object
Set elem = xml.createElement("b64")
elem.DataType = "bin.base64"
elem.nodeTypedValue = data
EncodeBase64 = elem.Text
Set xml = Nothing
End Function

2024-05-10 08_41_58-Edit your flow _ Power Automate and 1 more page - Work - Microsoft Edge.png 2024-05-10 08_40_12-Document1 - Word.png