将一列英国日期转换为VBA中的文本 [英] Converting a column of UK dates to text in VBA

问题描述

我的系统位于英国语言环境(Windows 7，Office Professional Plus 2013).

My system is in a UK locale (windows 7, office professional Plus 2013).

我有一些日期列，以Uk格式(dd/mm/yyyy)作为日期存储，我需要将它们转换为相同格式的文本.我遇到的问题是，似乎每个VBA函数默认都会在转换为文本时吐出美国格式的日期.

I have some columns of dates, stored as dates in the Uk format (dd/mm/yyyy) and I need to convert them to text in the same format. The problem i'm having is it seems that every VBA function defaults to spitting out US format dates when converting to text.

我尝试使用range.TextToColumns函数，下面是示例代码(主要基于宏记录器-奇怪的是，文本到列在excel中工作正常，但是记录的代码给出了美国日期...很奇怪)

I've tried using the range.TextToColumns function and example code is below (mostly macro recorder based - oddly text to columns works fine in excel, but the recorded code gives US dates... bizarre)

Sub ColumnToText(rngColumn As Range)
' ---------------------------------------------------------------------
'   Takes a column, coverts it to text
' ---------------------------------------------------------------------

rngColumn.TextToColumns _
    Destination:=rngColumn.Range("A1"), _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=False, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=False, _
    Other:=False, _
    FieldInfo:=Array(1, 2), _
    TrailingMinusNumbers:=True

End Sub

我有很多方法可以通过遍历单元格来做到这一点，但是我的目标是寻找一种可以在将来以及这种情况下使用的有效解决方案(我对优化有一点OCD).

There are plenty of ways I could do this by looping through the cells, but i'm aiming for an efficient solution that I can use in future as well as this case (i'm a little OCD about optimisation).

理想情况下，我希望能够使TextToColumns函数在VBA中以与在Excel中相同的方式工作，但我意识到这不可能.

Ideally I'd like to be able to get the TextToColumns function to work in VBA the same way it does in Excel, but I realise that may not be possible.

推荐答案

带有日期，如果您的操作系统(Windows)为英语-美国，则可以使用r.Value = t

With dates, if your OS (Windows) is in English-US you can use r.Value = t

Set r = Range("a1:a9")
t = r.Value
r.NumberFormat = "@"
r.Value = t

否则，如果您不希望将日期转换为美国日期格式，则必须使用r.FormulaLocal = t

Otherwise if you do not want the dates to be converted to the US date format, you must use r.FormulaLocal = t

Set r = Range("a1:a9")
t = r.Value
r.NumberFormat = "@"
r.FormulaLocal = t

当数据从vba发送到单元时，如果需要，即如果数据类型与numberformat属性不同，则调用内部类型转换函数.此内部类型转换功能仅识别国际日期格式(yyyy/mm/dd)和美国日期格式(例如mm/dd/yyyy)

When a data is sent from vba to a cell, an internal type conversion function is called if required, that is if the data type is different from the numberformat property. This internal type conversion function recognizes only the international date format (yyyy / mm / dd) and US dates formats (eg. mm / dd / yyyy)

您可以使用cell.FormulaLocal = data代替cell.Value = data来避免调用此函数.

You can use cell.FormulaLocal = data in place of cell.Value = data to avoid this function to be called.

这篇关于将一列英国日期转换为VBA中的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！