Cara, muito obrigado pelo retorno até aqui... Segui sua sugestão, rodou sem erros, porém ele não trás o resultado esperado... Veja o código:
Option Explicit
Sub export_in_json_format()
Dim fs As Object
Dim jsonfile
Dim rangetoexport As Range
Dim rowcounter As Long
Dim columncounter As Long
Dim linedata As String
Dim UltimaLinhaAtivaE As Long
Dim UltimaLinhaAtivaG As Long
Dim UltimaLinhaAtivaH As Long
Dim UltimaLinhaAtivaL As Long
Dim UltimaLinhaAtivaN As Long
Dim UltimaLinhaAtivaO As Long
Dim UltimaLinhaAtivaP As Long
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim r4 As Range
Dim r5 As Range
Dim r6 As Range
Dim r7 As Range
UltimaLinhaAtivaE = Planilha1.Cells(Planilha1.Rows.Count, 5).End(xlUp).Row
UltimaLinhaAtivaG = Planilha1.Cells(Planilha1.Rows.Count, 7).End(xlUp).Row
UltimaLinhaAtivaH = Planilha1.Cells(Planilha1.Rows.Count, 7).End(xlUp).Row
UltimaLinhaAtivaL = Planilha1.Cells(Planilha1.Rows.Count, 12).End(xlUp).Row
UltimaLinhaAtivaN = Planilha1.Cells(Planilha1.Rows.Count, 14).End(xlUp).Row
UltimaLinhaAtivaO = Planilha1.Cells(Planilha1.Rows.Count, 15).End(xlUp).Row
UltimaLinhaAtivaP = Planilha1.Cells(Planilha1.Rows.Count, 16).End(xlUp).Row
Set r1 = Worksheets("Sheet1").Range("N1:N" & UltimaLinhaAtivaN)
Set r2 = Worksheets("Sheet1").Range("O1:O" & UltimaLinhaAtivaO)
Set r3 = Worksheets("Sheet1").Range("P1:P" & UltimaLinhaAtivaP)
Set r4 = Worksheets("Sheet1").Range("H1:H" & UltimaLinhaAtivaH)
Set r5 = Worksheets("Sheet1").Range("G1:G" & UltimaLinhaAtivaG)
Set r6 = Worksheets("Sheet1").Range("L1:L" & UltimaLinhaAtivaL)
Set r7 = Worksheets("Sheet1").Range("E1:E" & UltimaLinhaAtivaE)
' change range here
Set rangetoexport = Union(r1, r2, r3, r4, r5, r6,r7)
Set fs = CreateObject("Scripting.FileSystemObject")
' change dir here
Set jsonfile = fs.CreateTextFile("C:Userserick.l.santiagoDesktop" & "jsondata.json", True)
linedata = "{""Output"": ["
jsonfile.WriteLine linedata
For rowcounter = 2 To rangetoexport.Rows.Count
linedata = ""
For columncounter = 1 To rangetoexport.Columns.Count
linedata = linedata & """" & rangetoexport.Cells(1, columncounter) & """" & ":" & """" & rangetoexport.Cells(rowcounter, columncounter) & """" & ","
Next
linedata = Left(linedata, Len(linedata) - 1)
If rowcounter = rangetoexport.Rows.Count Then
linedata = "{" & linedata & "}"
Else
linedata = "{" & linedata & "},"
End If
jsonfile.WriteLine linedata
Next
linedata = "]}"
jsonfile.WriteLine linedata
jsonfile.Close
Set fs = Nothing
End Sub
ele trás como resultado:
{"Output": [
{"Supplier Number":"20136570","Supplier Number Desc":"fornecedor1","Invoice Number":""},
{"Supplier Number":"40016609","Supplier Number Desc":"fornecedor2","Invoice Number":"000050412000"},
{"Supplier Number":"40018644","Supplier Number Desc":"fornecedor3","Invoice Number":"10006600"},
{"Supplier Number":"40017433","Supplier Number Desc":"fornecedor4","Invoice Number":"00912200"},
{"Supplier Number":"40017966","Supplier Number Desc":"fornecedor5","Invoice Number":"00055900"}
]}
é como ele só considerasse as 3 primeiras ranges, as colunas N, O, P, desconsiderando as H, G, L, E...
Acho que tem algo haver com o formato das células a serem transformadas em json... Consegue identificar isso no código para eu corrigir ?
Postado : 30/05/2018 12:46 pm