Excel 2002 Web 查询搞砸了 en-GB 日期
下午好，
我在 Excel 2002 中有一个针对返回日期列的网页的 Web 查询。 日期以 DD/MM/YYYY 形式返回，因为我想在电子表格中显示它们。 我运行 Excel 的计算机的区域设置设置为 en-GB，并且“Internet 选项”下设置的唯一语言是英国英语。
然而，Web 查询将日期提取为 MM/DD/YYYY，这会被 Excel 误解。
如何让 Web 查询返回区域设置中的日期 MM/DD/YYYY？
Good afternoon,
I have a web query in Excel 2002 going against a web page that returns a date column. The dates are returned as DD/MM/YYYY, as I would like to show them in my spreadsheet. My machine running Excel has its regional settings set to en-GB, and the only language set under Internet Options is UK English.
Nevertheless, the web query pulls the dates as MM/DD/YYYY, which is misinterpreted by Excel.
How can I get the Web Query to return the dates in my regional settings, MM/DD/YYYY?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
您可以修改“网络查询”吗？
通用日期格式是 Office 产品识别日期/时间的更好方法。 我在处理英国到美国日期时遇到了类似的问题，并发现将日期编码为这种格式可以节省很多麻烦。
Can you modify the "web query"?
The universal date format is the better way for Office products to recognise dates/times. I've had similar problems working with GB to US dates and found that coding your dates into this format saves you a lot of trouble.
也许您很幸运，Excel 可以理解 HTML
lang属性或元标记。 老实说 - 我的猜测是这没有什么区别。如果您可以控制该网站，则可以尝试添加它们，看看是否会产生影响。 OTOH - 如果您拥有网页的控制权，则可以更改日期格式。
另一种方法是完全禁用日期识别，并在查询后手动对其进行后处理。 为此，请重新创建 Web 查询，然后在“新建 Web 查询”对话框中单击右上角的“选项...”按钮。 有一个“禁用日期识别”复选框。
Maybe you are lucky and Excel understands either the HTML
langattribute or<meta http-equiv="content-language" content="...">meta tag. To be honest - my guess is that it doesn't make a difference.If you have control over the web site, you can try to add them and see if it makes a difference. OTOH - if you had control over the web page, you could change the date format.
The alternative would be to disable date recognition entirely and post-process it by hand after the query. To do this, recreate the web query and in the "New Web Query" dialog box, click the "Options..." button in the upper right. There is a "Disable date recognition" checkbox.
Excel 网络查询在国际事务方面非常糟糕。 它对我也不起作用。 我最终要做的就是使用宏手动提取数据，按区域设置解析数据并将其放在工作表上。 幸运的是，我查询了一个返回 XML 的 Web 服务，所以这很容易。 如果您解析 HTML，请执行 Tomalak 建议的操作。
Excel web query is awful in terms of international stuff. It didn't work for me either. What I finally had to do is just pull in the data manually, with the macro, parse it locale-wise and put onto the sheet. Fortunately, I queried a web service that returned XML so it was easy. If you parse HTML, do what Tomalak suggested.
在 Excel 2007 中，更改区域设置中的日期格式效果很好。
In Excel 2007, Changing the date format in regional setting worked fine.
网页格式为en-GB。 我将控制面板中的区域设置更改为 en-GB。 我将 Internet 选项语言更改为 en-GB。
尽管如此，Excel 的 Web 查询仍返回 en-US 格式的数据。 我不明白你的建议有什么帮助。 在这种情况下，不能选择后处理。
The webpage format is en-GB. I changed my regional settings in the Control Panel to en-GB. I changed my Internet Options language to en-GB.
Still, Excel's Web Query returns data in en-US. I don't see how any of what you suggested would help. In this case post-processing is not an option.
在 VBA 编辑器中，尝试设置
标准 Web 查询命令中的
属性：（请注意，通过在进行 Web 查询时简单地录制宏，您可以获得与上面相同的结果...）
在这里效果很好，希望它也适合您。
干杯!
In the VBA editor, try setting the
property inside the standard web query command :
(Notice you can get the same as above by simply recording a macro while making the web query...)
Worked well here, hope it does for you too.
Cheers!