After OpenText all pastes from external apps use format from OpenText

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

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
 
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
 
Back
Top