英文:
502 Bad Gateway Error when trying to make a request to ANEEL API using VBA
问题
你在Excel VBA宏中遇到502 Bad Gateway错误是因为API请求未能成功。这个错误通常表示服务器无法提供有效的响应。你可以尝试以下方法来解决这个问题:
-
检查URL: 确保你的API请求URL没有拼写错误,并且与在浏览器中测试的URL一致。
-
网络连接: 确保你的计算机具有可靠的网络连接,能够访问API服务器。
-
代理设置: 如果你需要通过代理服务器访问互联网,确保你的Excel VBA宏已配置正确的代理设置。
-
API服务器状态: 502 Bad Gateway错误可能是API服务器问题。检查API服务器是否正常运行,并且没有计划的维护。
-
API密钥: 如果API需要密钥或授权,请确保你已正确设置并传递了API密钥。
-
超时设置: 在VBA宏中,可以设置HTTP请求的超时时间。如果API响应时间较长,你可能需要增加超时时间。
-
错误处理: 在VBA宏中,添加适当的错误处理机制,以便更好地捕获和处理API请求的错误。这有助于你更容易诊断问题。
在确认了这些方面后,如果问题仍然存在,你可能需要联系API提供者以获取更多支持和帮助,以确保你的请求能够正常工作。
英文:
I'm having an issue with the ANEEL API query through a macro in an Excel spreadsheet. Although I can successfully make the request in a browser, I'm unable to write some fields in the Excel table due to a 502 Bad Gateway error. I'm investigating what might be causing this issue.
I'm trying to create a VBA macro to make a request to the ANEEL API and retrieve the desired data. I've written the following function:
Sub ConsultAPI()
Dim xmlhttp As Object
Dim url As String
Dim response As String
Dim json As Object
Dim utilityCompany As String
Dim tariffGroup As String
' Get the utility company name from the desired cell
utilityCompany = Sheets("Start").Range("B21").Value
' Get the tariff group
tariffGroup = Sheets("Start").Range("B27").Value
' Get the tariff class
tariffClass = Sheets("Start").Range("B19").Value
' Get the value from cell B20 as the filter for subclass
Dim subclassFilter As String
subclassFilter = Sheets("Start").Range("B20").Value
' Replace whitespace with %20 in the utility company string
utilityCompany = Replace(utilityCompany, " ", "%20")
' Replace whitespace with %20 in the tariff group string
tariffGroup = Replace(tariffGroup, " ", "%20")
' Replace whitespace with %20 in the class (DscClasse) string
tariffClass = Replace(tariffClass, " ", "%20")
' Replace whitespace with %20 in the subclass string
subclassFilter = Replace(subclassFilter, " ", "%20")
' Add a filter for the DscSubClasse field in the URL
Dim filterDscSubClasse As String
filterDscSubClasse = "%20AND%20%22DscSubClasse%22%20%3D%20%27" & subclassFilter & "%27"
' Add a filter for the DscModalidadeTarifaria field in the URL
Dim filterDscModalidadeTarifaria As String
filterDscModalidadeTarifaria = "%20AND%20%22DscModalidadeTarifaria%22%20%3D%20%27Convencional%27"
' Add the filter for the NomPostoTarifario field in the URL
url = "https://dadosabertos.aneel.gov.br/api/3/action/datastore_search_sql?sql=SELECT%20*%20FROM%20%22fcf2906c-7c32-4b9b-a637-054e7a5234f4%22%20WHERE%20%22SigAgente%22%20%3D%20%27" & utilityCompany & "%27%20AND%20%22DscSubGrupo%22%20%3D%20%27" & tariffGroup & "%27%20AND%20%22DscClasse%22%20%3D%20%27" & tariffClass & "%27%20AND%20%22SigAgenteAcessante%22%20IN%20(%27NA%27,%20%27N%C3%A3o%20se%20aplica%27)%20AND%20%22DscBaseTarifaria%22%20%3D%20%27Tarifa%20de%20Aplica%C3%A7%C3%A3o%27" & filterDscSubClasse & filterDscModalidadeTarifaria & "%20AND%20%22NomPostoTarifario%22%20%3D%20%27N%C3%A3o%20se%20aplica%27%20ORDER%20BY%20%22DatInicioVigencia%22%20DESC%20LIMIT%201"
' Save the request details before making the GET request
SaveRequestDetails url
' Initialize the xmlhttp object
Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
' Make the GET request to the API
xmlhttp.Open "GET", url, False
xmlhttp.send
' Check the API response status
If xmlhttp.Status = 200 Then
' Parse the returned JSON
Set json = JsonConverter.ParseJson(xmlhttp.responseText)
' Check if the "success" field is true
If json("success") = True Then
' The query was successful, retrieve the result
Dim records As Object
Set records = json("result")("records")
' Check if there are any returned records
If records.Count > 0 Then
' Get the latest record
Dim record As Object
Set record = records(1)
' Access the fields and assign the values to Excel cells as needed
Sheets("Start").Range("B23").Value = record("DscREH")
Sheets("Start").Range("B24").Value = record("DatInicioVigencia")
Sheets("Start").Range("B25").Value = record("DatFimVigencia")
Sheets("Start").Range("B28").Value = record("VlrTUSD")
Sheets("Start").Range("B29").Value = record("VlrTE")
' ...
' Assign other fields and cells as needed
Else
MsgBox "No records found."
End If
Else
MsgBox "Query failed: " & json("error")
End If
Else
' The request failed, display the error message
MsgBox "API request failed! Status: " & xmlhttp.Status & " Response: " & xmlhttp.responseText
End If
' Release the xmlhttp object
Set xmlhttp = Nothing
End Sub
This macro is making the following API request:
https://dadosabertos.aneel.gov.br/api/3/action/datastore_search_sql?sql=SELECT%20*%20FROM%20%22fcf2906c-7c32-4b9b-a637-054e7a5234f4%22%20WHERE%20%22SigAgente%22%20%3D%20%27Equatorial%20AL%27%20AND%20%22DscSubGrupo%22%20%3D%20%27B3%27%20AND%20%22DscClasse%22%20%3D%20%27N%C3%A3o%20se%20aplica%27%20AND%20%22SigAgenteAcessante%22%20IN%20(%27NA%27,%20%27N%C3%A3o%20se%20aplica%27)%20AND%20%22DscBaseTarifaria%22%20%3D%20%27Tarifa%20de%20Aplica%C3%A7%C3%A3o%27%20AND%20%22DscSubClasse%22%20%3D%20%27N%C3%A3o%20se%20aplica%27%20AND%20%22DscModalidadeTarifaria%22%20%3D%20%27Convencional%27%20AND%20%22NomPostoTarifario%22%20%3D%20%27N%C3%A3o%20se%20aplica%27%20ORDER%20BY%20%22DatInicioVigencia%22%20DESC%20LIMIT%201
When testing the request in a browser, I can verify that it is working as expected and returning the following response:
// 20230605160415
// https://dadosabertos.aneel.gov.br/api/3/action/datastore_search_sql?sql=SELECT%20*%20FROM%20%22fcf2906c-7c32-4b9b-a637-054e7a5234f4%22%20WHERE%20%22SigAgente%22%20%3D%20%27Equatorial%20AL%27%20AND%20%22DscSubGrupo%22%20%3D%20%27B3%27%20AND%20%22DscClasse%22%20%3D%20%27N%C3%A3o%20se%20aplica%27%20AND%20%22SigAgenteAcessante%22%20IN%20(%27NA%27,%20%27N%C3%A3o%20se%20aplica%27)%20AND%20%22DscBaseTarifaria%22%20%3D%20%27Tarifa%20de%20Aplica%C3%A7%C3%A3o%27%20AND%20%22DscSubClasse%22%20%3D%20%27N%C3%A3o%20se%20aplica%27%20AND%20%22DscModalidadeTarifaria%22%20%3D%20%27Convencional%27%20AND%20%22NomPostoTarifario%22%20%3D%20%27N%C3%A3o%20se%20aplica%27%20ORDER%20BY%20%22DatInicioVigencia%22%20DESC%20LIMIT%201
{
"help": "https://dadosabertos.aneel.gov.br/api/3/action/help_show?name=datastore_search_sql",
"success": true,
"result": {
"sql": "SELECT * FROM \"fcf2906c-7c32-4b9b-a637-054e7a5234f4\" WHERE \"SigAgente\" = 'Equatorial AL' AND \"DscSubGrupo\" = 'B3' AND \"DscClasse\" = 'Não se aplica' AND \"SigAgenteAcessante\" IN ('NA', 'Não se aplica') AND \"DscBaseTarifaria\" = 'Tarifa de Aplicação' AND \"DscSubClasse\" = 'Não se aplica' AND \"DscModalidadeTarifaria\" = 'Convencional' AND \"NomPostoTarifario\" = 'Não se aplica' ORDER BY \"DatInicioVigencia\" DESC LIMIT 1",
"records": [
{
"_id": 221157,
"_full_text": "'-02':22 '-05':3,18,21 '-06':2 '-28':19 '12272084000100':16 '19':47 '2023':1,13,17 '2024':20 '24':9 '3.203':7 '304':46 '561':44 '83':45 'al':15 'aplica':30,33,36,39,43 'aplicação':25 'b3':26 'convencion':27 'de':8,10,12,24 'equatori':14 'homologatória':5 'maio':11 'mwh':40 'nº':6 'não':28,31,34,37,41 'resolução':4 'se':29,32,35,38,42 'tarifa':23",
"DatGeracaoConjuntoDados": "2023-06-05",
"DscREH": "RESOLUÇÃO HOMOLOGATÓRIA Nº 3.203, DE 24 DE MAIO DE 2023",
"SigAgente": "Equatorial AL",
"NumCNPJDistribuidora": "12272084000100",
"DatInicioVigencia": "2023-05-28",
"DatFimVigencia": "2024-05-02",
"DscBaseTarifaria": "Tarifa de Aplicação",
"DscSubGrupo": "B3",
"DscModalidadeTarifaria": "Convencional",
"DscClasse": "Não se aplica",
"DscSubClasse": "Não se aplica",
"DscDetalhe": "Não se aplica",
"NomPostoTarifario": "Não se aplica",
"DscUnidadeTerciaria": "MWh",
"SigAgenteAcessante": "Não se aplica",
"VlrTUSD": "561,83",
"VlrTE": "304,19"
}
],
"fields": [
{
"id": "_id",
"type": "int4"
},
{
"id": "_full_text",
"type": "tsvector"
},
{
"id": "DatGeracaoConjuntoDados",
"type": "text"
},
{
"id": "DscREH",
"type": "text"
},
{
"id": "SigAgente",
"type": "text"
},
{
"id": "NumCNPJDistribuidora",
"type": "text"
},
{
"id": "DatInicioVigencia",
"type": "text"
},
{
"id": "DatFimVigencia",
"type": "text"
},
{
"id": "DscBaseTarifaria",
"type": "text"
},
{
"id": "DscSubGrupo",
"type": "text"
},
{
"id": "DscModalidadeTarifaria",
"type": "text"
},
{
"id": "DscClasse",
"type": "text"
},
{
"id": "DscSubClasse",
"type": "text"
},
{
"id": "DscDetalhe",
"type": "text"
},
{
"id": "NomPostoTarifario",
"type": "text"
},
{
"id": "DscUnidadeTerciaria",
"type": "text"
},
{
"id": "SigAgenteAcessante",
"type": "text"
},
{
"id": "VlrTUSD",
"type": "text"
},
{
"id": "VlrTE",
"type": "text"
}
]
}
}
However, when I try to use the macro in Excel through the VBA editor, I receive this message:
<html>
<head>
<title>502 Bad Gateway </title>
</head>
<body>
<center>
<h1>502 Bad Gateway</h1>
</center>
<hr>
<center>nginx/1.18.0(Ubuntu)
</center>
</body>
</html>
How can I fix this?
答案1
得分: 2
这对我有用 - 重新排列以更易读:
Sub ConsultAPI()
Dim xmlhttp As Object
Dim url As String, qs As String
Dim response As String
Dim json As Object
Dim utilityCompany As String
Dim tariffGroup As String, tariffClass As String
Dim subclassFilter As String, wsStart As Worksheet
Set wsStart = ThisWorkbook.Sheets("Start")
utilityCompany = wsStart.Range("B21").Value ' 公用事业公司名称
tariffGroup = wsStart.Range("B27").Value ' 费率组
tariffClass = wsStart.Range("B19").Value ' 费率类别
subclassFilter = wsStart.Range("B20").Value
url = "https://dadosabertos.aneel.gov.br/api/3/action/datastore_search_sql?sql="
qs = "SELECT * FROM ""fcf2906c-7c32-4b9b-a637-054e7a5234f4"" WHERE " & _
"""SigAgente"" = '" & utilityCompany & "' AND " & _
"""DscSubGrupo"" = '" & tariffGroup & "' AND " & _
"""DscClasse"" = '" & tariffClass & "' AND " & _
"""SigAgenteAcessante"" IN ('NA', 'Não se aplica') AND " & _
"""DscBaseTarifaria"" = 'Tarifa de Aplicação' AND " & _
"""DscSubClasse"" = '" & subclassFilter & "' AND " & _
"""DscModalidadeTarifaria"" = 'Convencional' AND " & _
"""NomPostoTarifario"" = 'Não se aplica' ORDER BY ""DatInicioVigencia"" DESC LIMIT 1"
url = url & WorksheetFunction.EncodeURL(qs) ' 编码SQL(Excel 2013)
' 在进行GET请求之前保存请求详细信息
' SaveRequestDetails url
Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
xmlhttp.Open "GET", url, False
xmlhttp.send ' 从这点开始正常...
英文:
This works for me - re-arranged to be a little more readable:
Sub ConsultAPI()
Dim xmlhttp As Object
Dim url As String, qs As String
Dim response As String
Dim json As Object
Dim utilityCompany As String
Dim tariffGroup As String, tariffClass As String
Dim subclassFilter As String, wsStart As Worksheet
Set wsStart = ThisWorkbook.Sheets("Start")
utilityCompany = wsStart.Range("B21").Value ' utility company name
tariffGroup = wsStart.Range("B27").Value ' tariff group
tariffClass = wsStart.Range("B19").Value ' tariff class
subclassFilter = wsStart.Range("B20").Value
url = "https://dadosabertos.aneel.gov.br/api/3/action/datastore_search_sql?sql="
qs = "SELECT * FROM ""fcf2906c-7c32-4b9b-a637-054e7a5234f4"" WHERE " & _
" ""SigAgente"" = '" & utilityCompany & "' AND " & _
" ""DscSubGrupo"" = '" & tariffGroup & "' AND " & _
" ""DscClasse"" = '" & tariffClass & "' AND " & _
" ""SigAgenteAcessante"" IN ('NA', 'Não se aplica') AND " & _
" ""DscBaseTarifaria"" = 'Tarifa de Aplicação' AND " & _
" ""DscSubClasse"" = '" & subclassFilter & "' AND " & _
" ""DscModalidadeTarifaria"" = 'Convencional' AND " & _
" ""NomPostoTarifario"" = 'Não se aplica' ORDER BY ""DatInicioVigencia"" DESC LIMIT 1"
url = url & WorksheetFunction.EncodeURL(qs) 'encode the SQL (Excel 2013 on)
' Save the request details before making the GET request
'SaveRequestDetails url
Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
xmlhttp.Open "GET", url, False
xmlhttp.send 'OK from this point...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论