Copied text strings have delimiters removed after upgrading to Excel 2010

[Durian910]

Hello,

I have recently upgraded from Windows XP/Excel 2003 to Windows 7/Excel 2010.

I have a file comparison utility that takes in pipe-delimited text files, compares them, and then allows me to select and copy the "rows" of delimited text that show up in the comparison viewer and then paste these rows to another into Excel, Notepad, etc.

Under Excel 2003, when I pasted the copied data, it came out as tab-delimited rather than pipe-delimited when I pasted into Excel. Under Excel 2010 the tabs (and whitespace?) are getting automatically stripped out when I paste the data into Excel. And they are also missing from whatever single row I can see in the formula bar. H

How can I prevent this? Is there a setting somewhere in the GUI to restore the old behavior? Or can I control it through VBA?

The problem is not with the way the data comes out of the file comparison tool. If I paste to Notepad or some other text editor, the tabs are still there. And if I use Excel 2010 Text-to-Columns utility, it can still detect where the tabs are.

My problem is that I do not do the text-to-columns process manually. Instead I wrote a macro that does it. The macro can handle data that is pipe-delimited, tab-delimited or comma-delimited. But the macro does not work when when the tab symbols have been stripped out of the text strings.

Below is a fragment of my macro. Underlined values are Boolean variables dictating which delimiter and which text qualifier constant to use.

Select Case ExtractType
Case "Grant Level Expense Export"
Selection.TextToColumns Destination:=UpperLeftDataCell, DataType:=xlDelimited, _
TextQualifier:=TextWrapper, ConsecutiveDelimiter:=False, Tab:=StartedAsTabDelimited, _
Semicolon:=False, Comma:=StartedAsCommaDelimited, Space:=False, Other:=True, OtherChar _
:=Delimiter, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 3), Array(4, 3), Array(5, 2), Array(6, 2), _
Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), _
Array(13, 2), Array(14, 2), Array(15, 2), Array(16, 2), Array(17, 2), Array(18, 2), _
Array(19, 2), Array(20, 2), Array(21, 2), Array(22, 2), Array(23, 2), Array(24, 1), _
Array(25, 2), Array(26, 2), Array(27, 2), Array(28, 2), Array(29, 2), Array(30, 2), _
Array(31, 1), Array(32, 3), Array(33, 3), Array(34, 1), Array(35, 1), Array(36, 1), _
Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), _
Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), _
Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 2)), TrailingMinusNumbers:=True


Any assistance would be greatly appreciated.

 

[Durian910]

I have solved the problem. I completely misdiagnosed it. It had nothing to do with upgrading to higher versions of Excel or Windows.

FWIW, here is what was really going wrong. The true reason why the TextToColumns method was not working for me is that I was passing bad data to the method. In the code snippet above, I am assigning the value stored by variable TextWrapper to the parameter called TextQualifier. This parameter expects an Excel constant, which is actually an integer value. But my variable TextWrapper was a String, not an Integer or a Long, so the TextToColumns method wasn't working.