Automating Text to Columns -- can I auto-click "OK" on the message
 

Hi,

As part of my code, I am performing a text to columns separation. During
this process, an Excel generated message comes up asking if I would like to
replace the contents of the cells in the columns where the data is being
separated to -- is there any way I can automate the clicking of "OK" on this
message box? Or not have it come up at all? Below is my code. Thanks!

With Sheets("Remove Dups")
.Columns("A:C").PasteSpecial
.Columns("B:B").Delete Shift:=xlToLeft
.Columns("B:B").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End With

--
Robert

Automating Text to Columns -- can I auto-click "OK" on the message
 

you can try

application,displayalerts = false
With Sheets("Remove Dups")
.Columns("A:C").PasteSpecial
.Columns("B:B").Delete Shift:=xlToLeft
.Columns("B:B").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End With
application.displayalerts = true

--


Gary


"robs3131" wrote in message
...
Hi,

As part of my code, I am performing a text to columns separation. During
this process, an Excel generated message comes up asking if I would like to
replace the contents of the cells in the columns where the data is being
separated to -- is there any way I can automate the clicking of "OK" on this
message box? Or not have it come up at all? Below is my code. Thanks!

With Sheets("Remove Dups")
.Columns("A:C").PasteSpecial
.Columns("B:B").Delete Shift:=xlToLeft
.Columns("B:B").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End With

--
Robert

Automating Text to Columns -- can I auto-click "OK" on the message
 

Typo in first line:
application,displayalerts = false
comma should be a period
application.displayalerts = false

Mike F
"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
you can try

application,displayalerts = false
With Sheets("Remove Dups")
.Columns("A:C").PasteSpecial
.Columns("B:B").Delete Shift:=xlToLeft
.Columns("B:B").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End With
application.displayalerts = true

--


Gary


"robs3131" wrote in message
...
Hi,

As part of my code, I am performing a text to columns separation. During
this process, an Excel generated message comes up asking if I would like
to
replace the contents of the cells in the columns where the data is being
separated to -- is there any way I can automate the clicking of "OK" on
this
message box? Or not have it come up at all? Below is my code. Thanks!

With Sheets("Remove Dups")
.Columns("A:C").PasteSpecial
.Columns("B:B").Delete Shift:=xlToLeft
.Columns("B:B").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
End With

--
Robert