VBA to open, modify and save XML file

[JackDanIce]

Hi,

From a workbook I wish to open an XML file, modify it then save it with a change of name. I've got the following code so far, but it's failing on the SaveAsXMLData line in red with error message "Argument not optional" From what I can tell, I'm missing a schema map or haven't specified one.

My understanding of XML file types isn't great (if someone can suggest a good link, I'd appreciate that) but for now at least, please can someone suggest how to correct my code so far:

Sub ImportXMLFile()
     
Dim ThisWbk As Workbook:       Set ThisWbk = ActiveWorkbook

Dim FilePath As String:            FilePath = ThisWbk.Sheets(1).Range("FilePath")
Dim FileName As String:           FileName = "TimeTestingReport.xml"
Dim TradeTimeCol As String:     TradeTimeCol = "AC"

Dim BST_Time As String, j As String
Dim i As Long
Dim FileOpen As Date
     
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With
    
Workbooks.OpenXML FileName:=FilePath & FileName, LoadOption:=xlXmlLoadOpenXml
FileOpen = Time
With ThisWbk.Sheets(1).Range("FileOpen")
    .Value = FileOpen
    .Offset(, 2) = FileName
End With

i = Range(TradeTimeCol & Rows.Count).End(xlUp).Row

Do
    BST_Time = CStr(Val(Left(Range(TradeTimeCol & i), 2)) + 1)
    If Len(BST_Time) < 2 Then BST_Time = "0" & BST_Time
    Range(TradeTimeCol & i) = BST_Time & Right(Range(TradeTimeCol & i), Len(Range(TradeTimeCol & i)) - 2)
    i = i - 1    
Loop Until i = 1

FileName = Left(FileName, Len(FileName) - 4) & " BST Modified.xml"
FileOpen = Time
ActiveWorkbook.SaveAsXMLData FileName:=FileName

With ThisWbk.Sheets(1).Range("FileSave")
    .Value = FileOpen
    .Offset(, 2) = FileName
End With

With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

Set ThisWbk = Nothing

End Sub

I'd also like code included that closes the modified XML file as well please.

Thank you in advance,
Jack

 

[JackDanIce]

n/a

 

[sergioMabres]

I would start by reading the page
Create an XML data file and XML schema file from worksheet data - Excel - Office.com
It has a very well written article
Follow it then record macro and see how it saves as XML file
I hope this helps

 

[JackDanIce]

Hi Segrio, apologies for not replying, this got pushed down in priorities and now need to review again. Thank you for suggesting the website, reading it now.
Best,
Jack