Excel with macro // PDF conversion in Workflow and manual

HI all,

I have a excel sheet with a small macro which is making an http request to get the distance between 2 towns and update it in the cell (=Distance("myDestination"))

When opening the file in read only mode, the cells are up to date.

When visualizing the excel file in M-Files, they are not (error #name)

When converting in PDF thanks to a workflow action, the PDF file as the same error.

When converting the excel from M-Files to PDF with the PDF conversion command, cells are up to date.

Do you know why and how to convert via workflow this file without any error?

Public Const DIST = "http://www.distance2villes.com/recherche?source="

Function Distance(dest As String) As Variant
Dim lg As Integer, i As Integer
Dim Url As String, Txt As String, S As String
Application.ScreenUpdating = False

With ActiveSheet

            Url = DIST & "Hirtzfelden" & "&destination=" & dest
            With CreateObject("WINHTTP.WinHTTPRequest.5.1")
                .Open "GET", Url, False
                Txt = .responseText
            End With
            If InStr(1, Txt, "distanciaRuta") > 0 Then
                Distance = Split(Split(Txt, "id=""distanciaRuta"">")(1), " km</strong>")(0)
               Distance = ""
            End If
End With
Application.ScreenUpdating = True
End Function

  • I don't really have a solution for you but as to why this happens: 

    • When converting the file to a PDF using the context menu command in the client, M-Files basically "prints" the document locally using a PDF printer so it tends to produce a more what-you-see-is-what-you-get version of the document. This is why the cells are up to date when you open the file in Excel and also when you convert it to PDF on the client side.
    • When converting the file to a PDF using the workflow actions, M-Files uses a third-party library (Aspose) to convert the file on the server side. The result is not always 100% the same as what you get in Excel on the client side. I'm not certain what should happen to macros in such a situation, you may want to raise this with our support team to see if they have any further comments or suggestions.