Convert the Trial Balance Report for Mexico to XML Format

You can view the Trail Balance Report for Mexico from the Financial Reporting Center. After you have viewed the report, you can convert it to an XML format to upload it to the Tax Administration Services (SAT) internet portal.

View the Trial Balance Report for Mexico

To view the report, perform the following steps:

1. Sign in the Oracle Fusion Cloud Applications.

2. On the home page of General Account Manager, select General Accounting: Financial Reporting Center > My Folders > Trial Balance Report for Mexico.

3. View the report in HTML.

4. Click the HTML Preview list at the beginning of the page and select Export to Excel.

   The report opens in Microsoft Excel.

5. Save the report in your local folder as an Excel Macro-Enabled Workbook.

Convert the Trial Balance Report for Mexico to XML Format

Perform the following steps to convert your report to an XML format that can be uploaded to the SAT portal:

1. Open the report in Microsoft Excel and click the View tab.

2. Click Macros > View Macros.

3. Enter GenerateXML in the Macro name field and click Create.

   The Microsoft Visual Basic editor opens.

4. Copy and paste the following report conversion code in the Microsoft Visual Basic editor.

   Sub Generate_XML()
   '
   ' GenerateXML Macro
       Dim Version As String
       Dim RFC As String
       Dim Mes As String
       Dim Anio As String
       Dim TipoEnvio As String
       Dim FechaModBal As String
       Dim Sello As String
       Dim noCertificado As String
       Dim BalanzaCertificado As String
       Dim XML As String
       Dim XML2 As String
       Dim Row As String
       Dim Row2 As String
       Dim ColumnName As String
       Dim ColumnValue As String
       Dim MesP1 As String
       Dim MesP2 As String
   	Dim MesPr As String
       Dim Pos As Integer
       Dim Pos2 As Integer
       Dim Pos3 As Integer
       Dim MidStr As String
       Dim MidStr2 As String
       Dim Arr
   
       Sheets(1).Select
       Version = Replace(ActiveSheet.Cells(3, 1).Value, " ", "")
       RFC = Replace(ActiveSheet.Cells(4, 1).Value, " ", "")
       Mes = Replace(ActiveSheet.Cells(3, 1).Value, " ", "")
       Anio = Replace(ActiveSheet.Cells(6, 1).Value, " ", "")
       TipoEnvio = Replace(ActiveSheet.Cells(7, 1).Value, " ", "")
       FechaModBal = Replace(ActiveSheet.Cells(8, 1).Value, " ", "")
       Sello = Replace(ActiveSheet.Cells(9, 1).Value, " ", "")
       noCertificado = Replace(ActiveSheet.Cells(10, 1).Value, " ", "")
       BalanzaCertificado = Replace(ActiveSheet.Cells(11, 1).Value, " ", "")
       
       Arr = Array("Version", "RFC", "Mes", "Anio", "TipoEnvio", "FechaModBal", "Sello", "noCertificado", "Certificado")
       
       MesP1 = Mid(Mes, 6, 3)
       'MsgBox MesP1
   	
   	MesPr = Mid(Mes, 6, 2)
           
       If ((MesP1 = "Jan") Or (MesP1 = "Ene") Or (MesP1 = "ENE") Or (MesP1 = "JAN") Or (MesPr = "01")) Then
           MesP2 = "Mes=" & Chr(34) & "01" & Chr(34)
           'MsgBox Mes
       ElseIf ((MesP1 = "Feb") Or (Mes = "FEB") Or (MesPr = "02")) Then
           MesP2 = "Mes=" & Chr(34) & "02" & Chr(34)
       ElseIf ((MesP1 = "Mar") Or (Mes = "MAR") Or (MesPr = "03")) Then
           MesP2 = "Mes=" & Chr(34) & "03" & Chr(34)
       ElseIf ((MesP1 = "Apr") Or (MesP1 = "Abr") Or (MesP1 = "ABR") Or (MesP1 = "APR") Or (MesPr = "04")) Then
           MesP2 = "Mes=" & Chr(34) & "04" & Chr(34)
       ElseIf ((MesP1 = "May") Or (MesP1 = "MAY") Or (MesPr = "05")) Then
           MesP2 = "Mes=" & Chr(34) & "05" & Chr(34)
       ElseIf ((MesP1 = "Jun") Or (MesP1 = "JUN") Or (MesPr = "06")) Then
           MesP2 = "Mes=" & Chr(34) & "06" & Chr(34)
       ElseIf ((Mes = "Jul") Or (MesP1 = "JUL")) Or (MesPr = "07") Then
           MesP2 = "Mes=" & Chr(34) & "07" & Chr(34)
       ElseIf ((MesP1 = "Aug") Or (MesP1 = "AGO") Or (MesP1 = "Ago") Or (MesP1 = "AUG") Or (MesPr = "08")) Then
           MesP2 = "Mes=" & Chr(34) & "08" & Chr(34)
       ElseIf ((MesP1 = "Sep") Or (MesP1 = "SEP") Or (MesPr = "09")) Then
           MesP2 = "Mes=" & Chr(34) & "09" & Chr(34)
       ElseIf ((MesP1 = "Oct") Or (MesP1 = "OCT") Or (MesPr = "10")) Then
           MesP2 = "Mes=" & Chr(34) & "10" & Chr(34)
       ElseIf ((MesP1 = "Nov") Or (MesP1 = "NOV") Or (MesPr = "11")) Then
           MesP2 = "Mes=" & Chr(34) & "11" & Chr(34)
       ElseIf ((MesP1 = "Dec") Or (MesP1 = "DEC") Or (MesP1 = "Dic") Or (MesP1 = "DIC") Or (MesPr = "12")) Then
           MesP2 = "Mes=" & Chr(34) & "12" & Chr(34)
       Else
           MesP2 = "Mes=" & Chr(34) & "13" & Chr(34)
       End If
       'MsgBox MesP2
       'XML = XML & "<BCE:Balanza xmlns:BCE=" & Chr(34) & "http://www.sat.gob.mx/esquemas/ContabilidadE/1_1/BalanzaComprobacion" & 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_1/BalanzaComprobacion http://www.sat.gob.mx/esquemas/ContabilidadE/1_1/BalanzaComprobacion/BalanzaComprobacion_1_1.xsd" & Chr(34) & " " & Version & " " & RFC & " " & Mes & " " & Anio & " " & TipoEnvio & " " & FechaModBal & " " & Sello & " " & noCertificado & " " & BalanzaCertificado & ">" & vbNewLine
       XML = XML & "<BCE:Balanza xmlns:BCE=" & Chr(34) & "http://www.sat.gob.mx/esquemas/ContabilidadE/1_3/BalanzaComprobacion" & 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/BalanzaComprobacion http://www.sat.gob.mx/esquemas/ContabilidadE/1_3/BalanzaComprobacion/BalanzaComprobacion_1_3.xsd" & Chr(34)
       XML2 = XML
       Row2 = ""
       
       'Pos is Position of Inverted Commas from Start
       'Pos2 is Length of String between Inverted Commas
       'Pos3 is Length of Field Name
       
       For I = 1 To 9
           Pos = (InStr(1, Replace(ActiveSheet.Cells(I, 1).Value, " ", ""), Chr(34))) + 1
           Pos2 = InStr(Pos, Replace(ActiveSheet.Cells(I, 1).Value, " ", ""), Chr(34))
           Pos2 = Pos2 - Pos
           Pos3 = Pos - 3
           'MsgBox Pos2
         '  MsgBox I
           MidStr = Mid(Replace(ActiveSheet.Cells(I, 1).Value, " ", ""), Pos, Pos2)
           MidStr2 = Mid(Replace(ActiveSheet.Cells(I, 1).Value, " ", ""), 1, Pos3)
          ' MsgBox MidStr2
           If MidStr <> "" Then
               If MidStr2 = "Mes" Then
                   Row2 = Row2 & MesP2 & "  "
               Else
                   Row2 = Row2 & MidStr2 & "=" & Chr(34) & MidStr & Chr(34) & "  "
               End If
               'Row2 = Row2 & " " & Replace(ActiveSheet.Cells(I, 1).Value, " ", "")
               'MsgBox Row2
           End If
           
       Next I
       'MsgBox XML2
       'MsgBox Row2
       XML2 = XML2 & "  " & Row2
       
       XML = XML2 & ">" & vbNewLine
       
       ActiveSheet.Range("A10").Select
       Selection.End(xlDown).Select
       SheetLastRow = ActiveCell.Row
       
       ActiveSheet.Range("A10").Select
       Selection.End(xlToRight).Select
       SheetLastColumn = ActiveCell.Column
       
       For I = 11 To SheetLastRow
           Row = vbTab & "<BCE:Ctas "
           For J = 1 To SheetLastColumn
               ColumnName = Replace(ActiveSheet.Cells(10, J).Value, " ", "")
               If ColumnName = "SaldoIni" Or ColumnName = "Debe" Or ColumnName = "Haber" Or ColumnName = "SaldoFin" Then
                   'MsgBox ColumnValue
                   If ActiveSheet.Cells(I, J).Value = "" Or ActiveSheet.Cells(I, J).Value = "0" Then
                   
                       ColumnValue = 0
                       'MsgBox ColumnValue
                   Else
                       ActiveSheet.Cells(I, J).Value = Format(ActiveSheet.Cells(I, J).Value, "#,###")
                   
                       ColumnValue = ActiveSheet.Cells(I, J).Value
                   End If
                   'Selection.NumberFormat = "#,###"
               Else
                   ColumnValue = ActiveSheet.Cells(I, J).Value
               End If
               Row = Row & ColumnName & "=" & Chr(34) & ColumnValue & Chr(34) & " "
           Next J
           Row = Row & "/>" & vbNewLine
           
           XML = XML & Row
       Next I
       
       XML = XML & "</BCE:Balanza>"
       
       fileSaveName = Application.GetSaveAsFilename(ActiveSheet.Name, fileFilter:="XML Files (*.xml), *.xml")
           
       Open fileSaveName For Output As #1
       Print #1, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF-8" & Chr(34) & "?>"
       Print #1, XML
       Close #1
   '
   End Sub
   

5. Click File > Save.

   Save the file in your local folder and close Microsoft Visual Basic.

6. Select the GenerateXML macro and click Run that appears on the Macros dialog box in Microsoft Excel.

7. Save the XML file in your local folder.

8. Open the XML file and verify the output.