Convert the Auxiliary Report for Mexico to an XML Format

To upload the Auxiliary Report for Mexico to the Tax Administration Services (SAT) website, you must first convert the report into an XML format.

To convert the report, perform these steps:

Run the Auxiliary Report Mexico report in Oracle Analytics Publisher.

Export the report to Microsoft Excel.
Click the Export option, select Data, and then select CSV Format.
On the Opening Balance Journals Details with Prompts dialog box, select the Open with option and click OK.

The report appears in Microsoft Excel.
Click the View tab.
Click Macros > Record Macros.

The Record Macros dialog box appears.
Click OK.
Click the View tab.
Click Macros > View Macros.
Click Edit on the Macros dialog box.

Microsoft Visual Basic editor appears.

Replace the existing code with the following code:

Sub MakeXML()
' create an XML file from an Excel table
Dim MyRow As Long, MyCol As Long, MyColStart As Long, MyColEnd As Long, MyCol2 As Long, Temp As String, YesNo As Variant, DefFolder As String
Dim XMLFileName As String, XMLRecBuffer As String, XMLRec2Buffer As String, MyLF As String, RTC1 As Long
Dim RangeOne As String, RangeTwo As String, Tt As String, FldName(99) As String, CheckFlag As Long, Arr
Dim Version As String, RFC As String, Mes As String, Anio As String, TipoSolicitud As String, NumOrden As String
Dim NumTramite As String, Sello As String, noCertificado As String, Certificado As String, lastRow As Long
Dim XML As String, MidStr As String, Row As String, XML3 As String, XML4 As String, Pos As String, Pos2 As String
Dim fsT As Object
'Create stream object
Set fsT = CreateObject("ADODB.Stream")
'save text/string data
fsT.Type = 2
'Specify charset for the source text data
fsT.CharSet = "utf-8"

MyLF = Chr(10) & Chr(13)    ' a line feed command

YesNo = MsgBox("This procedure requires the following data:" & MyLF _
 & "1 A filename for the XML file" & MyLF _
 & "2 Version Value" & MyLF _
 & "3 RFC Value" & MyLF _
 & "4 Mes Value" & MyLF _
 & "5 Anio Value" & MyLF _
 & "6 TipoSolicitud Value" & MyLF _
 & "7 NumOrden Value" & MyLF _
 & "8 NumTramite Value" & MyLF _
 & "9 Sello Value" & MyLF _
 & "10 noCertificado Value" & MyLF _
 & "11 Certificado Value" & MyLF _
 & "Are you ready to proceed?", vbQuestion + vbYesNo, "MakeXML CiM")
 
If YesNo = vbNo Then
 Debug.Print "User aborted with 'No'"
 Exit Sub
End If

XMLFileName = FillSpaces(InputBox("1. Enter the name of the XML file:", "MakeXML CiM", "xl_xml_data"))
If Right(XMLFileName, 4) <> ".xml" Then
 XMLFileName = XMLFileName & ".xml"
End If

If Len(Dir("C:\Mexico", vbDirectory)) = 0 Then
   MkDir "C:\Mexico"
End If

If Len(Dir("C:\Mexico\Auxiliary", vbDirectory)) = 0 Then
   MkDir "C:\Mexico\Auxiliary"
End If

DefFolder = ActiveWorkbook.Path
'"c:\Mexico\Auxiliary"
'InputBox("2. Enter the location of the path for the output file", "MakeXML CiM", "Enter the Path")
RangeOne = "A1:K1"
If MyRng(RangeOne, 1) <> MyRng(RangeOne, 2) Then
  MsgBox "Error: names must be on a single row" & MyLF & "Procedure STOPPED", vbOKOnly + vbCritical, "MakeXML CiM"
  Exit Sub
End If
MyRow = MyRng(RangeOne, 1)
For MyCol = MyRng(RangeOne, 3) To MyRng(RangeOne, 4)
 If Len(Cells(MyRow, MyCol).Value) = 0 Then
  MsgBox "Error: names range contains blank cell" & MyLF & "Procedure STOPPED", vbOKOnly + vbCritical, "MakeXML CiM"
  Exit Sub
 End If
 FldName(MyCol - MyRng(RangeOne, 3)) = FillSpaces(Cells(MyRow, MyCol).Value)
Next MyCol

'RangeTwo = InputBox("3. Enter the range of cells containing the data table:", "MakeXML CiM", "A2:I2")

lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

RangeTwo = "A2:K" & lastRow
If MyRng(RangeOne, 4) - MyRng(RangeOne, 3) <> MyRng(RangeTwo, 4) - MyRng(RangeTwo, 3) Then
  MsgBox "Error: number of field names <> data columns" & MyLF & "Procedure STOPPED", vbOKOnly + vbCritical, "MakeXML CiM"
  Exit Sub
End If
RTC1 = MyRng(RangeTwo, 3)

Version = InputBox("2. Enter the Version Value:", "MakeXML CiM", "1.3")
Version = "Version=" & Chr(34) & Version & Chr(34)

RFC = InputBox("3. Enter the RFC Value:", "MakeXML CiM", "RFC Value")
RFC = "RFC=" & Chr(34) & RFC & Chr(34)

Mes = InputBox("4. Enter the Mes Value:", "MakeXML CiM", "Mes Value")
Mes = "Mes=" & Chr(34) & Mes & Chr(34)

Anio = InputBox("5. Enter the Anio Value:", "MakeXML CiM", "Anio Value")
Anio = "Anio=" & Chr(34) & Anio & Chr(34)

TipoSolicitud = InputBox("6. Enter the TipoSolicitud Value:", "MakeXML CiM", "TipoSolicitud Value")
TipoSolicitud = "TipoSolicitud=" & Chr(34) & TipoSolicitud & Chr(34)

NumOrden = InputBox("7. Enter the NumOrden Value:", "MakeXML CiM", "NumOrden Value")
NumOrden = "NumOrden=" & Chr(34) & NumOrden & Chr(34)

NumTramite = InputBox("8. Enter the NumTramite Value:", "MakeXML CiM", "NumTramite Value")
NumTramite = "NumTramite=" & Chr(34) & NumTramite & Chr(34)

Sello = InputBox("9. Enter the Sello Value:", "MakeXML CiM", "Sello Value")
Sello = "Sello=" & Chr(34) & Sello & Chr(34)

noCertificado = InputBox("10. Enter the Certificado Number Value:", "MakeXML CiM", "noCertificado Value")
noCertificado = "noCertificado=" & Chr(34) & noCertificado & Chr(34)

Certificado = InputBox("11. Enter the Certificado Value:", "MakeXML CiM", "Certificado Value")
Certificado = "Certificado=" & Chr(34) & Certificado & Chr(34)

Arr = Array(Version, RFC, Mes, Anio, TipoSolicitud, NumOrden, NumTramite, Sello, noCertificado, Certificado)

If InStr(1, XMLFileName, ":\") = 0 Then
 XMLFileName = DefFolder & "\" & XMLFileName
End If

XMLRecBuffer = "    <AuxiliarCtas:Cuenta "
CheckFlag = 0

'XML3 = "<AuxiliarCtas  xmlns:xsi=" & Chr(34) & "http://www.w3.org/2001/XMLSchema-instance" & Chr(34) & " xsi:schemaLocation=" & Chr(34) & "http://www.sat.gob.mx/esquemas/ContabilidadE/1_1/AuxiliarCtas http://www.sat.gob.mx/esquemas/ContabilidadE/1_1/AuxiliarCtas/AuxiliarCtas_1_1.xsd" & Chr(34) & " xmlns=" & Chr(34) & "http://www.sat.gob.mx/esquemas/ContabilidadE/1_1/AuxiliarCtas" & Chr(34) & " Version=" & Chr(34) & Version & Chr(34) & " RFC=" & Chr(34) & RFC & Chr(34) & " Mes=" & Chr(34) & Mes & Chr(34) & " Anio=" & Chr(34) & Anio & Chr(34) & " TipoSolicitud=" & Chr(34) & TipoSolicitud & Chr(34) & " NumOrden=" & Chr(34) & NumOrden & Chr(34) & " NumTramite=" & Chr(34) & NumTramite & Chr(34) & " Sello=" & Chr(34) & Sello & Chr(34) & " noCertificado=" & Chr(34) & noCertificado & Chr(34) & " Certificado=" & Chr(34) & Certificado & Chr(34) & ">"
XML3 = "<AuxiliarCtas:AuxiliarCtas xmlns:AuxiliarCtas=" & Chr(34) & "http://www.sat.gob.mx/esquemas/ContabilidadE/1_3/AuxiliarCtas" & Chr(34) & " xmlns:xsi=" & Chr(34) & "http://www.w3.org/2001/XMLSchema-instance" & Chr(34) & " xsi:schemaLocation=" & Chr(34) & "http://www.sat.gob.mx/esquemas/ContabilidadE/1_3/AuxiliarCtas http://www.sat.gob.mx/esquemas/ContabilidadE/1_3/AuxiliarCtas/AuxiliarCtas_1_3.xsd" & Chr(34)
XML4 = XML3

    Row = ""

'Open XMLFileName For Output As #1
'Open the stream and write binary data to the object
fsT.Open
    
    For I = 0 To 9
        Pos = InStr(1, Arr(I), Chr(34))
        Pos2 = InStrRev(Arr(I), Chr(34), -1)
        Pos2 = Pos2 - Pos
        
        MidStr = Mid(Arr(I), (Pos + 1), (Pos2 - 1))
        'MsgBox MidStr
        If MidStr <> "" Then
            Row = Row & " " & Arr(I)
        End If
        
    Next I
    
    XML4 = XML4 & Row
    
    XML3 = XML4 & ">"

'Open XMLFileName For Output As #1
fsT.writetext "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF-8" & Chr(34) & "?>" & Chr(13) & Chr(10)
fsT.writetext XML3 & Chr(13) & Chr(10)
If FldName(1) = "SubCta" Then
    MyColStart = 7
  Else
    MyColStart = 5
End If
If FldName(1) = "SubCta" Then
    MyColEnd = 0
  Else
    MyColEnd = 2
End If
'MsgBox MyRng(RangeTwo, 4)
'MsgBox MyColStart
'MsgBox FldName(1)
For MyRow = MyRng(RangeTwo, 1) To MyRng(RangeTwo, 2)
  For MyCol = RTC1 To MyRng(RangeTwo, 4) - MyColEnd
  If MyCol = MyColStart And CheckFlag = 0 Then
    'MsgBox XMLRecBuffer
    fsT.writetext XMLRecBuffer & ">" & Chr(13) & Chr(10)
    CheckFlag = 1
  End If
  If MyCol = MyColStart Then
    XMLRec2Buffer = "        <AuxiliarCtas:DetalleAux "
    For MyCol2 = MyCol To MyRng(RangeTwo, 4) - MyColEnd
        If RemoveAmpersands(FormChk(MyRow, MyCol2)) <> "" Then
            XMLRec2Buffer = XMLRec2Buffer & " " & FldName(MyCol2 - 1) & "=" & Chr(34) & RemoveAmpersands(FormChk(MyRow, MyCol2)) & Chr(34)
        ElseIf FldName(MyCol2 - 1) = "Debe" Or FldName(MyCol2 - 1) = "Haber" And RemoveAmpersands(FormChk(MyRow, MyCol2)) = "" Then
                XMLRec2Buffer = XMLRec2Buffer & " " & FldName(MyCol2 - 1) & "=" & Chr(34) & "0" & Chr(34)
        End If
  ' the next line uses the FormChk function to format dates and numbers
  ' the next line does not apply any formatting
  '  fsT.writetext "<" & FldName(MyCol - RTC1) & ">" & RemoveAmpersands(Cells(MyRow, MyCol).Value) & "</" & FldName(MyCol - RTC1) & ">" & Chr(13) & Chr(10)
        Next MyCol2
    fsT.writetext XMLRec2Buffer & "/>" & Chr(13) & Chr(10)
    End If
  ' the next line uses the FormChk function to format dates and numbers
  ' the next line does not apply any formatting
  '  fsT.writetext "<" & FldName(MyCol - RTC1) & ">" & RemoveAmpersands(Cells(MyRow, MyCol).Value) & "</" & FldName(MyCol - RTC1) & ">" & Chr(13) & Chr(10)
    If CheckFlag = 0 Then
        If RemoveAmpersands(FormChk(MyRow, MyCol)) <> "" Then
            If FldName(MyCol - RTC1) = "Debe" Or FldName(MyCol - RTC1) = "Haber" Then
                XMLRecBuffer = XMLRecBuffer & " " & FldName(MyCol - RTC1) & "=" & Chr(34) & "0" & Chr(34)
            ElseIf FldName(MyCol - RTC1) = "NumCta" And FldName(1) = "SubCta" Then
                If RemoveAmpersands(FormChk(MyRow, MyCol + 1)) <> "" Then
                    XMLRecBuffer = XMLRecBuffer & " " & FldName(MyCol - RTC1) & "=" & Chr(34) & RemoveAmpersands(FormChk(MyRow, MyCol)) & "-" & RemoveAmpersands(FormChk(MyRow, MyCol + 1)) & Chr(34)
                Else
                    XMLRecBuffer = XMLRecBuffer & " " & FldName(MyCol - RTC1) & "=" & Chr(34) & RemoveAmpersands(FormChk(MyRow, MyCol)) & Chr(34)
                End If
            ElseIf FldName(MyCol - RTC1) = "DesCta" And FldName(1) = "SubCta" Then
                If RemoveAmpersands(FormChk(MyRow, MyCol + 1)) <> "" Then
                    XMLRecBuffer = XMLRecBuffer & " " & FldName(MyCol - RTC1) & "=" & Chr(34) & RemoveAmpersands(FormChk(MyRow, MyCol)) & "-" & RemoveAmpersands(FormChk(MyRow, MyCol + 1)) & Chr(34)
                Else
                    XMLRecBuffer = XMLRecBuffer & " " & FldName(MyCol - RTC1) & "=" & Chr(34) & RemoveAmpersands(FormChk(MyRow, MyCol)) & Chr(34)
                End If
            ElseIf FldName(MyCol - RTC1) = "SubCta" Or FldName(MyCol - RTC1) = "SubDesCta" Then
                XML3 = "SubCta"
            Else
                XMLRecBuffer = XMLRecBuffer & " " & FldName(MyCol - RTC1) & "=" & Chr(34) & RemoveAmpersands(FormChk(MyRow, MyCol)) & Chr(34)
                'MsgBox XMLRecBuffer
            End If
        End If
    End If
    Next MyCol
    If FormChk(MyRow, 1) <> FormChk(MyRow + 1, 1) Then
        fsT.writetext "    </AuxiliarCtas:Cuenta>" & Chr(13) & Chr(10)
        XMLRecBuffer = "    <AuxiliarCtas:Cuenta "
        CheckFlag = 0
    End If
Next MyRow

fsT.writetext "</AuxiliarCtas:AuxiliarCtas>" & Chr(13) & Chr(10)
'Close #1
'Save binary data To disk
fsT.SaveToFile XMLFileName, 2
MsgBox XMLFileName & " created." & MyLF & "Process finished", vbOKOnly + vbInformation, "MakeXML CiM"
Debug.Print XMLFileName & " saved"

End Sub
Function MyRng(MyRangeAsText As String, MyItem As Long) As Long
' analyse a range, where MyItem represents 1=TR, 2=BR, 3=LHC, 4=RHC

Dim UserRange As Range
Set UserRange = Range(MyRangeAsText)
Select Case MyItem
 Case 1
 MyRng = UserRange.Row
 Case 2
 MyRng = UserRange.Row + UserRange.Rows.Count - 1
 Case 3
 MyRng = UserRange.Column
 Case 4
 MyRng = UserRange.Columns(UserRange.Columns.Count).Column
End Select
Exit Function

End Function
Function FillSpaces(AnyStr As String) As String
' remove any spaces and replace with underscore character
Dim MyPos As Long
MyPos = InStr(1, AnyStr, " ")
Do While MyPos > 0
 Mid(AnyStr, MyPos, 1) = "_"
 MyPos = InStr(1, AnyStr, " ")
Loop
FillSpaces = AnyStr
End Function

Function FormChk(RowNum As Long, ColNum As Long) As String
' formats numeric and date cell values to comma 000's and DD MMM YY
FormChk = Cells(RowNum, ColNum).Value
'If IsNumeric(Cells(RowNum, ColNum).Value) Then
' FormChk = Format(Cells(RowNum, ColNum).Value, "#,##0 ;(#,##0)")
'End If
If IsDate(Cells(RowNum, ColNum).Value) Then
 FormChk = Format(Cells(RowNum, ColNum).Value, "yyyy-mm-dd")
End If
End Function

Function RemoveAmpersands(AnyStr As String) As String
Dim MyPos As Long
' replace Ampersands (&) with plus symbols (+)

MyPos = InStr(1, AnyStr, "&")
Do While MyPos > 0
 Mid(AnyStr, MyPos, 1) = "+"
 MyPos = InStr(1, AnyStr, "&")
Loop
RemoveAmpersands = AnyStr
End Function

Run the macro.

The MakeXML CiM dialog box appears containing instructions to provide several details in a series of dialog boxes.
Click Yes and proceed with information entry on each dialog box as they appear one after the other. Enter the following information:
- Name of the output file
- Location to save the file
- Version of the file
- RFC
- Mes
- Anio
- TipoSolicitud
- NumOrden
- NumTramite
- Seal
- Certificate number
- Certificate details
Verify the location where the output files will be saved and click OK.

Open the XML report that's saved at location you specified.