Excel VBA中XMLHTTP GET请求无法获取目标URL响应（405错误）
2026-8-8
解决Excel VBA XMLHTTP请求NSEIndia时的405错误
我正尝试用Excel VBA的XMLHTTP GET方法抓取NSEIndia网站的表格数据，请求主页面Url_Main和Url_Sub1都正常，但请求目标接口Url_Sub2时返回HTTP 405错误，以下是相关代码和截图：
问题代码
Option Explicit Dim xmlhttp As New MSXML2.XMLHTTP60,html As New HTMLDocument Dim sResp$, Url_Main$, Url_Sub1$, Url_Sub2$ Sub FetchHistoricalDataWorking() 'URL Url_Main$ = "https://www1.nseindia.com/products/content/equities/equities/eq_security.htm" Url_Sub1$ = "https://www1.nseindia.com/marketinfo/sym_map/symbolCount.jsp?symbol=LUPIN" Url_Sub2$ = "https://www1.nseindia.com/products/dynaContent/common/productsSymbolMapping.jsp?symbol=LUPIN&segmentLink=3&symbolCount=2&series=ALL&dateRange=3month&fromDate=&toDate=&dataType=PRICEVOLUMEDELIVERABLE" 'XML Link With xmlhttp .Open "GET", Url_Main$, False '/products/content/equities/equities/eq_security.htm" .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36" .setRequestHeader "Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9" .setRequestHeader "Accept-Encoding", "gzip, deflate, br" .setRequestHeader "Accept-Language", "en-IN,en-US;q=0.9,en;q=0.8" .setRequestHeader "Cache-Control", "no-cache" .setRequestHeader "Connection", "keep-alive" .setRequestHeader "Host", "www1.nseindia.com" .send While .readyState < 4: DoEvents: Wend .Open "GET", Url_Sub1$, False .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36" .setRequestHeader "Referer", Url_Main$ .setRequestHeader "Accept", "*/*" .setRequestHeader "Accept-Encoding", "gzip, deflate, br" .setRequestHeader "Accept-Language", "en-IN,en-US;q=0.9,en;q=0.8" .setRequestHeader "Cache-Control", "no-cache" .setRequestHeader "Connection", "keep-alive" .setRequestHeader "Host", "www1.nseindia.com" .send While .readyState < 4: DoEvents: Wend .Open "GET", Url_Sub2$, False .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36" .setRequestHeader "Referer", Url_Main$ .setRequestHeader "Accept", "*/*" .setRequestHeader "Accept-Encoding", "gzip, deflate, br" .setRequestHeader "Accept-Language", "en-IN,en-US;q=0.9,en;q=0.8" .setRequestHeader "Cache-Control", "no-cache" .setRequestHeader "Connection", "keep-alive" .setRequestHeader "Host", "www1.nseindia.com" .send While .readyState < 4: DoEvents: Wend sResp = .responseText Debug.Print sResp End With html.body.innerHTML = sResp ' Debug.Print oHttp.readyState & oHttp.Status End Sub
相关截图
- 所需输出表格：
- 网络工具发起源：
- Url_Sub1请求响应：
- Url_Sub2请求405错误：
- 网络工具详情：
解决方案
HTTP 405错误意味着服务器不允许使用当前请求方法（此处为GET）访问目标资源，针对NSEIndia的接口特性，可通过以下方式排查修复：
1. 确认请求方法是否正确
打开浏览器开发者工具，查看Url_Sub2对应的实际请求方法。如果浏览器使用POST而非GET，需将VBA代码中的请求方式改为POST，并把URL中的参数作为表单数据提交。
2. 优化会话Cookie处理
NSEIndia依赖Cookie验证会话合法性，建议改用MSXML2.ServerXMLHTTP60替代MSXML2.XMLHTTP60，它在Cookie自动管理上更稳定，能确保后续请求携带主页面返回的会话Cookie。
3. 精简并对齐请求头
- 移除
Accept-Encoding头：XMLHTTP60无法自动解压压缩内容，会导致响应解析异常，直接让服务器返回未压缩文本。 - 移除
Cache-Control和Host头：这些头会由ServerXMLHTTP60自动生成，手动设置可能引发冲突。 - 确保
Referer头与浏览器一致，通常为Url_Main。
修改后的示例代码
Option Explicit Dim xmlhttp As New MSXML2.ServerXMLHTTP60, html As New HTMLDocument Dim sResp$, Url_Main$, Url_Sub1$, Url_Sub2$ Sub FetchHistoricalDataFixed() 'URL定义 Url_Main = "https://www1.nseindia.com/products/content/equities/equities/eq_security.htm" Url_Sub1 = "https://www1.nseindia.com/marketinfo/sym_map/symbolCount.jsp?symbol=LUPIN" Url_Sub2 = "https://www1.nseindia.com/products/dynaContent/common/productsSymbolMapping.jsp?symbol=LUPIN&segmentLink=3&symbolCount=2&series=ALL&dateRange=3month&fromDate=&toDate=&dataType=PRICEVOLUMEDELIVERABLE" '初始化会话并获取会话Cookie With xmlhttp .Open "GET", Url_Main, False .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36" .setRequestHeader "Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8" .setRequestHeader "Accept-Language", "en-IN,en-US;q=0.9,en;q=0.8" .setRequestHeader "Connection", "keep-alive" .send While .readyState < 4: DoEvents: Wend '请求Url_Sub1 .Open "GET", Url_Sub1, False .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36" .setRequestHeader "Referer", Url_Main .setRequestHeader "Accept", "*/*" .setRequestHeader "Accept-Language", "en-IN,en-US;q=0.9,en;q=0.8" .setRequestHeader "Connection", "keep-alive" .send While .readyState < 4: DoEvents: Wend '请求Url_Sub2 .Open "GET", Url_Sub2, False .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36" .setRequestHeader "Referer", Url_Main .setRequestHeader "Accept", "*/*" .setRequestHeader "Accept-Language", "en-IN,en-US;q=0.9,en;q=0.8" .setRequestHeader "Connection", "keep-alive" .send While .readyState < 4: DoEvents: Wend '输出响应状态与内容 If .Status = 200 Then sResp = .responseText Debug.Print sResp Else Debug.Print "请求失败，状态码：" & .Status End If End With html.body.innerHTML = sResp End Sub
4. 额外提示
NSEIndia的反爬机制会定期更新，如果上述调整后仍未解决，需再次对比浏览器中的完整请求头（包括Cookie、Origin等），确保VBA请求头与浏览器完全一致。
内容的提问来源于stack exchange，提问作者Sourav
相关产品推荐
相关产品推荐
