Text to columns twice in one session 1

[VBAjedi]

I've seen this addressed before but can't find the thread.

I'm automating a process of pasting data from the clipboard, then using Text to Columns to break it out properly.

However, if you try to do this twice in one session, Excel somehow recognizes the data and automatically converts it to columns. Great for users, bad for code predictability.

Is there a way to turn this feature off via VBA, or somehow test for it? Only thing I can think of is testing for data in column B after the paste, but that seems less than ideal...

VBAjedi

 

[SkipVought]

VBA,

How 'bout some code? I do not understand the scerenio.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[VBAjedi]

I guess I didn't do as good a job explaining as I thought! Not suprising...
This is a simple sub to automate the formatting of a firewall event log (a text file). I'm after "quick-and-dirty", not pretty (so don't give me too much grief for the recorder scum I haven't cleaned out yet)! Here's the first bit of code:

Sub DataImport()
'
Dim ColData As Range, c As Range

' Prompt User
x = MsgBox("If you have not already copied the desired log data to the clipboard, do so now. Click Ok to continue.", vbOKCancel)
If x = 2 Then Exit Sub ' user clicked Cancel

' Clear old data from sheet
   Worksheets("Log").Activate
   Cells.Clear

' Paste new data to sheet, convert to columns if necessary
   Range("A1").Select
   ActiveSheet.Paste
   ' Check for values in column B (which is a crude indicator that data was converted to columns automatically)
   For Each c In Range("B2:B10")
      x = x & c.Value
   Next c
   If Len(x) = 0 Then ' Column B has no values
      Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
         TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
         Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
         :=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), 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))
   End If
' Filter for junk rows and delete
   Range("A2").AutoFilter Field:=4, Criteria1:="y"
   Range("A2").CurrentRegion.SpecialCells(xlCellTypeVisible).EntireRow.Delete
   Range("A2").AutoFilter Field:=4
   Range("C7").Select

And on it goes...
The scenario I'm trying to address is when the user fires this code a second time before they close the workbook. Excel remembers their previous Text-To-Columns settings and does the conversion automatically. So the code for the first time needs to do the conversion, but the code for any subsequent imports does not. My little check for data in column "B" is my kludged attempt at this.

VBAjedi

 

[SkipVought]

You stated

if you try to do this twice in one session, Excel somehow recognizes the data and automatically converts it to columns

I only see ONE TextToColumns.

Could it be the SELECT??

      [red][b]Selection[/b][/red].TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
         TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
         Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
         :=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), 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))
   End If
' Filter for junk rows and delete
   Range("A2").AutoFilter Field:=4, Criteria1:="y"
   Range("A2").CurrentRegion.SpecialCells(xlCellTypeVisible).EntireRow.Delete
   Range("A2").AutoFilter Field:=4
   Range("C7").[b][red]Select[/red][/b]

Might you try to reference a range rather than use Selection?

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[VBAjedi]

Yo, Skipper - I think you're missing it here. There is only one TextToColumns, but sometimes I need NONE. This is not just a code issue - it's an Excel "feature". Try this:

1) Copy the data below into cell A1 of a new spreadsheet.
2) Use Text to Columns, Space as delimiter, to put it into three columns.
3) Now clear the sheet, or switch to a new sheet. Copy the data below again and paste it to the clear/new sheet. Excel will automatically convert it to columns for you this time.

Val1 A 1
Val2 B 2
Val3 C 3
Val4 D 4

Annoying as heck to work with from code!



VBAjedi

 

[SkipVought]

Ahhhhhh,

This feature is annoying.

Once you complete the FIRST TTC, clear the Space Delimiter

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

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[VBAjedi]

Thanks, Skip!

The problem was that I only do the conversion once in my code, but it's entirely possible that my users will fire the code more than once (it's triggered by a big, green, tempting button!) in the same instance in Excel.

I ended up sticking with the clumsy workaround of doing a check for contents in col B, and not firing the Text-To-Columns if there is something there.

VBAjedi

 

[SkipVought]

Why won't the above code work in your situation?

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[VBAjedi]

Well, it's working for now... as long as I don't get a chunk of data that is naturally empty in column B (after it has been converted to columns). But that column is always supposed to have data in it, so I'll probably be ok.

Mostly I just don't like this apparent inability to directly test whether Text To Columns has shifted into "automatic conversion" mode.

VBAjedi

 

[SkipVought]

What does column B have to do with anything?

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[VBAjedi]

Well, if you paste a bunch of unconverted data into cell A1, it only puts values into column A (no matter how many columns it will take up after you do Text To Columns). So after my code does the paste to column A, I check column B for contents. If there's nothing in column B, then this is the first time the user has run my code today, and I need to do Text-To-Columns. If there IS data in column B, then this is NOT the first time the user has run the code (which means that Excel's "feature" has kicked in to convert the data to columns automatically), so I skip the Text To Columns code.

Like I said, it's a kludge and I don't like it - but it should work at least 99% of the time.

VBAjedi