在 VBA 中读取包含日期时间的 Excel 单元格时出现问题
一些背景：对于我正在从事的项目（从给定的客户数据 Excel 电子表格构建 XML DOM），我需要能够读取其中包含日期时间的单元格的内容。有问题的单元格包含“7/22/2011 0:00”，当我右键单击->格式化单元格时，它告诉我类别是“自定义”（不在标准日期类别中），并且类型为“月/日/年 时：毫米。”然而，当我选择单元格时，公式窗格将其显示为“7/22/2011 12:00:00AM”。因此，所有这三种对数据类型进行分类的尝试都不匹配。
问题： 当我出于调试目的在 MsgBox 中使用 ActiveWorkbook.ActiveSheet.Cells(x,y) 显示单元格内容时，它仅显示 7/22/2011（截断时间）。它不可能是日期和时间之间的空格造成的，因为我成功地读取了电子表格中其他位置带有空格的单元格。
谁能告诉我为什么会发生这种情况，或者为我指明一个 VBA/Excel 方法的正确方向，该方法不会执行 Sheet.Cells(x,y) 正在执行的这种奇怪的裁剪操作？谢谢。如果每次日期时间数据类型给我带来问题我都能得到一分钱就好了。
Some background: For a project I'm working on (building an XML DOM from a given excel spreadsheet of customer data), I need to be able to read the contents of a cell with a datetime in it. The cell in question contains "7/22/2011 0:00," and when I right-click->format cells, it tells me the category is "Custom" (not in the standard date category), and of type "m/d/yyyy h:mm." Yet when I select the cell, the formula pane displays it as "7/22/2011 12:00:00AM." So all three of these attempts to categorize the datatypes don't match up.
The problem: When I display the cell contents using ActiveWorkbook.ActiveSheet.Cells(x,y) in a MsgBox for debugging purposes, it only shows 7/22/2011 (cutting off the time). It can't be the space in between the date and time that throws it off, as I am successfully reading cells with spaces in them elsewhere in the spreadsheet.
Can anyone tell me why this is happening, or point me in the right direction for a VBA/Excel method that doesn't do this weird cropping thing that Sheet.Cells(x,y) is doing? Thanks. If only I had a penny for every time datetime datatypes caused problems for me..
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在内部，Excel 将日期存储为数字。它没有实现日期类型。该数字是自过去某个时间点（1900 年或 1904 年，取决于操作系统，内置一些关于闰年的错误）以来的天数。时间表示为数字的小数部分。
要使其对用户来说看起来像日期，您必须为单元格分配日期格式。然后该数字在单元格中显示为日期。 （如果您向 Excel 输入类似日期的内容，Excel 会自动分配日期格式。）
使用
ActiveWorkbook.ActiveSheet.Cells(x,y)时，您会创建一个Rangecode> 对象并调用其默认属性，即Value。Value有很多内置的魔力。在您的情况下，它会查看单元格格式，并查看日期格式，将内部存储的数字转换为子类型日期的Variant。当您使用消息框显示它时，下一个技巧就会发生。如果时间为 0:00，则日期将转换为不带时间的字符串。如果时间与 0:00 不同，则会将其转换为包含日期和时间的字符串。日期格式取自您的用户设置。它与您分配给 Excel 单元格的日期格式无关。如果您使用
Value2而不是Value（例如，通过使用消息框显示ActiveWorkbook.ActiveSheet.Cells(x,y).Value2） ，然后您将看到日期的内部表示，即数字。当您编辑单元格时，Excel 使用另一种日期格式，以便您可以查看和编辑日期的所有部分：年、月、日，可能还包括小时、分钟和秒。这是因为单元格的日期格式可能仅限于月份名称。
国际化又增加了一个复杂性。某些日期格式不会直接应用，而是使用当前用户设置中的日期格式的提示。因此，首先将该格式替换为另一种日期格式，然后再应用。此外，日期和时间格式的某些部分会受到用户设置的影响。最后，翻译日期格式的模式（在英语中，
yyyy代表年份，在德语中为jjjj）。保存 Excel 电子表格时，这些格式将以英文形式存储，以便用户和 Excel 安装程序可以使用任何语言打开该工作表。就您而言，国际化可能会影响您编辑单元格时使用的日期格式（将月份放在日期之前并使用 AM/PM 的 12 小时显示，看起来像北美）。我不太明白为什么 Excel 显示“7/22/2011 12:00:00AM”（而不是“7/22/2011 0:00:00AM”）。我很确定您的日期/时间的时间部分为“0:00”。但内部数字（由
Value2显示）会告诉你肯定的信息。Internally, Excel stores dates as numbers. It doesn't implement a date type. The number is the number of days since some point in the past (1900 or 1904, depending on the operation system, with some mistakes built-in regarding leap years). Time is represented as the fractional part of the number.
To make it look like a date to the user, you have to assign the cell a date format. Then the number is displayed as a date in the cell. (Excel assigns a date format automatically if you enter somethings that looks like a date to Excel.)
When you use
ActiveWorkbook.ActiveSheet.Cells(x,y), you create aRangeobject and call its default property, which isValue.Valuehas a lot of magic built-in. In your case, it looks at the cell format and - seeing a date format - in converts the internally stored number into aVariantof subtype date. When you display it using a message box, the next trick happens. If the time is 0:00, the date is converted to a string without time. If the time were different from 0:00, it would be converted to a string with date and time. The date format is taken from your user's settings. Its independent of the date format you have assigned to the Excel cell.If you use
Value2instead ofValue(e.g. by using a messag box to displayActiveWorkbook.ActiveSheet.Cells(x,y).Value2), then you'll see the internal representation of the date, namely a number.When you edit a cell, Excel uses yet another date format so you can see and edit all parts of the date: year, month, day and possibly hour, minutes and seconds. This is because your cell's date format could be restricted to just the month name.
Another complexity is added by internationalization. Certain date formats aren't applied directly but are a hint for using a date format from the current user's settings. So the format is first replaced with another date format and then applied. Furthermore, certain parts of the date and time formats are affected by the user's settings. And finally, the patterns of the date format are translated (in English,
yyyystands for the year, in German it'sjjjj). When the Excel spreadsheet is saved, these formats are stored in the English form so that the sheet can be opened by user's and Excel installations with any language. In your case, internationalization probably affects the date format used when you edit the cell (putting month before day and using 12-hour display with AM/PM looks like Northern America).I don't quite understand why Excel displays "7/22/2011 12:00:00AM" (instead of "7/22/2011 0:00:00AM"). I'm pretty sure your date/time has a time part of "0:00". But the internal number (as reveal by
Value2) will tell you for sure.它就像一个公式，在单元格中它将显示结果，在公式栏中它将显示公式。您可以设置单元格格式，但不能设置公式栏。因此，您可以将单元格的格式更改为您希望的格式。
因此，如果您想格式化 msgbox，则需要执行以下操作：
It's like a formula, in the cell it will show the result, in the formula bar it will show the formula. The cell you can format, the formula bar you cannot. So you can change the cell's format to however you would like it to look like.
So, if you want to format the msgbox then you would need to do the following: