After OpenText all pastes from external apps use format from OpenText
I use this subroutine to deal with that irritation. Watch for
linewrap.
Sub FixTextToColumns()
Dim rngO As Range
Application.ScreenUpdating = False
On Error Resume Next
Set rngO = ActiveSheet.UsedRange.SpecialCells(xlBlanks).Cells (1)
If rngO Is Nothing Then Set rngO = ActiveSheet.Cells(1, 1)
With rngO
.Value = "Tim"
.TextToColumns Destination:=rngO, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1)
.ClearContents
End With
End Sub
HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
I've used the OpenText method to open a txt file as space
delimited. The problem is is that after this macro has
been run, whenever I try to paste into Excel from another
application (Impromptu, Arthur), the data I'm pasting is
inserted as if it's space delimited also. It's as if
some property was altered with the OpenText method and
was not reset, but I can't find any documentation on it.
I have to exit out of Excel in order to get the pastes to
work again. Any ideas? FYI: Excel 2000, Windows XP.
TIA,
Jim
|