Forum Discussion

Mirco_Gabriel's avatar
Mirco_Gabriel
Copper Contributor
Apr 25, 2023

Import Test Steps via Excel Addin

Hello Together,

we use the Excel AddIn (https://learn.microsoft.com/en-us/azure/devops/boards/backlogs/office/bulk-add-modify-work-items-excel?view=azure-devops&tabs=agile-process

In this Documentation you can find the following Information: 

"You can't import or update test case steps or other test artifacts"

Actually you can, if you paste the Steps as XML Tags (https://oshamrai.wordpress.com/2019/03/01/azure-devops-services-rest-api-14-create-and-add-test-cases/

 

 

So far so good. It works pretty well. I define my TestStep in another Excel Sheet, transform these Steps into the XML Format via VBA Script and Paste it in the Excel Sheet for synchronisation. If i do so, with pasting the plain text directly in the Cell, it automaticly transform these to a text after publishing. In DevOps i got my Test Steps as defined.

 

There is only one Problem:

Otherwise, if I paste it not as Text but paste the cell, it stays as XML text after Publishing. The reason seems to be, that the PlugIn or DevOps converts the special Characters like < > " " into "&lt;" "&quot;" etc. You only realize this, when you export these TestCases to csv. Thats why it will not detected as TestSteps. The "Paste special" options makes no different.

 

In Case of Import a banch of Testcases via Excel i have to go into every cell an paste the XML String again. Is there any Workaround? (Using the grid view in Test Plans is no option for us)

 

Greetings Mirco

azure devops

1 Reply

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Sep 23, 2025

    Take this:

     

    1. Force Plain Text Paste via VBA
    Instead of manually pasting into each cell, use VBA to inject the XML string as plain text, bypassing Excel’s formatting engine:

    Sub PasteAsPlainText()
    Dim ws As Worksheet
    Dim rng As Range
    Dim xmlText As String

    Set ws = ThisWorkbook.Sheets("YourTargetSheet")
    Set rng = ws.Range("B2") ' Target cell for XML

    xmlText = "<steps id=""0"" last=""2""><step type=""ActionStep"" id=""2""><parameterizedString isformatted=""true"">Open App</parameterizedString><parameterizedString isformatted=""true""></parameterizedString></step></steps>"

    rng.Value = xmlText
End Sub

    This ensures the XML is written as raw text, not as a formatted object.

    2. Use Power Query to Inject XML
    If you’re importing from another sheet, use Power Query to transform and load the XML strings as plain text. This avoids Excel’s automatic character escaping.

    3. Pre-encode XML in VBA
    If you must paste via cell copy, pre-encode the XML manually so DevOps interprets it correctly:

    Function EncodeXML(xml As String) As String
    xml = Replace(xml, "<", "<")
    xml = Replace(xml, ">", ">")
    xml = Replace(xml, """", """)
    EncodeXML = xml
End Function


    But this only works if DevOps is expecting encoded XML—which it usually isn’t for test steps.

    4. Use REST API for Bulk Upload
    If Excel is not a consideration, may switch to the Azure DevOps REST API method. You can batch-create test cases with steps using structured XML and avoid Excel altogether.

Resources