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
You can view the Trail Balance Report for Mexico from the Financial Reporting Center.
Follow these steps:
-
Sign in the Oracle Fusion Cloud Applications.
-
On the home page of General Account Manager, select General Accounting: Financial Reporting Center, My Folders, Trial Balance Report for Mexico.
-
View the report in HTML.
-
Click the HTML Preview list at the beginning of the page and select Export to Excel.
The report opens in Microsoft Excel.
-
Save the report in your local folder as an Excel Macro-Enabled Workbook.
Convert the Trial Balance Report for Mexico to XML Format
You can convert your report to an XML format that can be uploaded to the SAT portal.
Follow these steps:
-
Open the report in Microsoft Excel and click the View tab.
-
Click Macros, View Macros.
-
Enter GenerateXML in the Macro name field and click Create.
The Microsoft Visual Basic editor opens.
-
Copy and paste the following report conversion code in the Microsoft Visual Basic editor.
Sub GenerateXML() ' ' 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 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(1, 1).Value, " ", "") RFC = Replace(ActiveSheet.Cells(2, 1).Value, " ", "") Mes = Replace(ActiveSheet.Cells(3, 1).Value, " ", "") Anio = Replace(ActiveSheet.Cells(4, 1).Value, " ", "") TipoEnvio = Replace(ActiveSheet.Cells(5, 1).Value, " ", "") FechaModBal = Replace(ActiveSheet.Cells(6, 1).Value, " ", "") Sello = Replace(ActiveSheet.Cells(7, 1).Value, " ", "") noCertificado = Replace(ActiveSheet.Cells(8, 1).Value, " ", "") BalanzaCertificado = Replace(ActiveSheet.Cells(9, 1).Value, " ", "") Arr = Array("Version", "RFC", "Mes", "Anio", "TipoEnvio", "FechaModBal", "Sello", "noCertificado", "Certificado") MesP1 = Mid(Mes, 6, 3) If MesP1 = "Jan" Then Mes = "Mes=" & Chr(34) & "01" & Chr(34) ElseIf MesP1 = "Feb" Then Mes = "Mes=" & Chr(34) & "02" & Chr(34) ElseIf MesP1 = "Mar" Then Mes = "Mes=" & Chr(34) & "03" & Chr(34) ElseIf MesP1 = "Apr" Then Mes = "Mes=" & Chr(34) & "04" & Chr(34) ElseIf MesP1 = "May" Then Mes = "Mes=" & Chr(34) & "05" & Chr(34) ElseIf MesP1 = "Jun" Then Mes = "Mes=" & Chr(34) & "06" & Chr(34) ElseIf MesP1 = "Jul" Then Mes = "Mes=" & Chr(34) & "07" & Chr(34) ElseIf MesP1 = "Aug" Then Mes = "Mes=" & Chr(34) & "08" & Chr(34) ElseIf MesP1 = "Sep" Then Mes = "Mes=" & Chr(34) & "09" & Chr(34) ElseIf MesP1 = "Oct" Then Mes = "Mes=" & Chr(34) & "10" & Chr(34) ElseIf MesP1 = "Nov" Then Mes = "Mes=" & Chr(34) & "11" & Chr(34) ElseIf MesP1 = "Dec" Then Mes = "Mes=" & Chr(34) & "12" & Chr(34) Else Mes = "Mes=" & Chr(34) & "13" & Chr(34) End If '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_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) XML2 = XML Row2 = "" 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 = 3 MidStr = Mid(Replace(ActiveSheet.Cells(I, 1).Value, " ", ""), Pos, Pos2) MidStr2 = Mid(Replace(ActiveSheet.Cells(I, 1).Value, " ", ""), 1, Pos3) If MidStr <> "" Then If MidStr2 = "Mes" Then Row2 = Row2 & " " & Mes Else Row2 = Row2 & " " & Replace(ActiveSheet.Cells(I, 1).Value, " ", "") End If End If Next I 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, " ", "") ColumnValue = ActiveSheet.Cells(I, J).Value 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 -
Click File > Save.
Save the file in your local folder and close Microsoft Visual Basic.
-
Select the GenerateXML macro and click Run that appears on the Macros dialog box in Microsoft Excel.
-
Save the XML file in your local folder.
-
Open the XML file and verify the output.