VBA auto click ok on pop up box

[davidcoad]

Hi,

I have a file that contains all of our products in a .csv format and is updated every night. So I can import it into our local reporting software I need to convert it to an excel document with variables in their own column as opposed to being all in one column and seperated by "|" (as per the .csv), and then save as an excel spreadsheet .xls.

I am writing a macro to open the .csv file, select column A and then select "Text to Columns" to move the data deliminated by a "|" into individual columns.

when I do this I get a pop up box that asks "do you want to replace the contents of the destination cells" which requires me to hit OK.

I want to automate this whole process so it happens everyday based on a a schedule so I never have to manually update the file when new products are added (which is regularly).

The part that I cannot do is making the macro auto select OK when the dialogue box pops up.

The code for the text to columns is:

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array 12 _
, 1), Array(13, 1)), TrailingMinusNumbers:=True

Anyone know how to select OK in the pop up box in VBA?

Thanks
Dave

 

[Smitty]

You can use Application.DisplayAlerts = False at the beginning of you code. Just make sure to set it back to True at the end.

And note that it's rarely necessary to select objects in order to work with them, so generally when you see "Select" followed by "Selection" you can eliminate both statements:

Columns("A:A").TextToColumns...

HTH,

 

[davidcoad]

Thanks, works great.

btw, thanks also for the coding tip. I like neat code

Dave