Hello
I've noticed something peculiar when pasting text using a macro as opposed to right clicking and selecting "Keep Text Only" (the logo with a capital A).
I have a database with a date-time field. Unfortunately the field is stored as a varchar due to the different ways people have entered their data (and casting to datetime fails).
On the data sheet in Excel, the column that will house the datetime data is formatted to "dd/mm/yyyy hh:mm". When I run a query and copy the results over to Excel I can choose the 'A' logo and only paste the text, in fact that's the only option. The datetime field is correctly recognized in Excel and when I click on the Sort menu I can choose "Oldest to Newest."
I wanted to automate this so I recorded a macro of me doing this and Excel produced the following code:
Range("A6").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
However, when I run the macro and open the sort menu, I get the 'Zto A' or 'A to Z' options. In other words Excel no longer recognizes the field as datetime.
How can I paste as "text only" using VBA so that the date field will be correctly recognized as when I manually copy and paste the data so that I have the correct sort menu?
Thanks
Andrew
Reply With Quote
