Ability to upload shifts, resource schedules

Thanks Charith, that’s interesting and sounds attractive. Where can we learn more about how that would be done? Are there any examples of that?

@JonWoster - Not sure if that helps, I always import rota data into my demo system based on an XLS. Have attached it as a working sample. I use a simple VBA script to convert the XLS data into XML and upload this to PSO. If you have a VBA expert, it would probably take him less than a day to develop the upload from Excel to PSO directly.

Hi @Charith Fernando

We are looking for the feasibility of upload resource shift planning data in bulk.

I found this topic is related and looking on the Rota.xlsx excel file attached above. 

I have few questions on this topic - 

1. As you mentioned earlier that we can upload bulk shift planning as xml feed. Is it possible, only RAM_Rota_Item data can be uploaded in bulk not other sheets data?
2. Do we have any native feature by which we can convert RAM_Rota_Item data to native xml feed from excel?

For #2 I asked ChatGPT “Write me a VBA script that converts a table structure into XML.” and got the response below. No warranties, but a good start. :-)

-------------------------------------------------------------

To convert a table structure into XML using VBA in Microsoft Excel, you can follow these steps:

1. Open Excel and press `Alt + F11` to open the VBA editor.
2. Insert a new module by clicking `Insert` > `Module`.

Here's a VBA script that demonstrates how to convert a simple Excel table into XML:

Sub ConvertTableToXML()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim xmlData As String
    Dim iRow As Long

    ' Set the worksheet containing your table
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

    ' Set the table
    Set tbl = ws.ListObjects("Table1") ' Change "Table1" to your table name

    ' Initialize the XML string
    xmlData = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf
    xmlData = xmlData & "<data>" & vbCrLf

    ' Loop through each row in the table
    For iRow = 1 To tbl.ListRows.Count
        xmlData = xmlData & "    <record>" & vbCrLf
        For Each col In tbl.ListColumns
            xmlData = xmlData & "        <" & col.Name & ">" & tbl.ListRows(iRow).Range.Cells(1, col.Index).Value & "</" & col.Name & ">" & vbCrLf
        Next col
        xmlData = xmlData & "    </record>" & vbCrLf
    Next iRow

    ' Close the root element
    xmlData = xmlData & "</data>"

    ' Create a new worksheet to store the XML
    Set xmlWs = ThisWorkbook.Sheets.Add
    xmlWs.Name = "XMLData"

    ' Paste the XML into the new worksheet
    xmlWs.Cells(1, 1).Value = xmlData

    ' Format the XML if needed
    xmlWs.Cells(1, 1).EntireColumn.AutoFit

    ' Optional: Save the XML to a file
    xmlWs.SaveAs "C:\YourFolder\YourFile.xml" ' Change the path and filename

    ' Clean up
    Set xmlWs = Nothing

    MsgBox "Table converted to XML and saved!", vbInformation
End Sub

Make sure to customize the code according to your specific table and sheet names. This script creates a new worksheet named "XMLData" to store the XML and optionally saves it to a file. You can modify the file path and name as needed.

To run this script, press `Alt + F8`, select "ConvertTableToXML," and click "Run."

Please be cautious when running VBA macros, especially if they modify your data. Always make a backup of your workbook before running macros.