I have the following file and I need to open it in Excel.

File is pipe delimeted and I need a macro or something to open it in Excel with each field separate. I know the import Wizard does allow us to specify what delimeter is there, but need to automate this in a macro so I dont have to run thru the wizard for each file.
I have fields with a semicolon so couldn't consider a .csv file to start with

e.g input file.
1|1|Express email|57688|1448|40928|2
1|7|TechTeam Test Newsletter|9|2|2|2
29|3|Parenting News|2|0|0|0
29|4|Women's Health News|2|0|0|0
29|5|Doctor-to-Doctor|1|0|0|0
57|2|Good growing kids|671|1|14|1
146|6|Student Health Zone|0|0|0|0

Any assistance is greatly appreciated.
Thanks !!

look into the texttocolumns function
something like


range("A1:A" & Lastrow).TexttoColumns

and then specify the delimiter to your pipe

or else use the workbook.opentext method which will let you automatically work theough the delimeters

hth

David

Thanks David.. I was wondering if there is a way say I can automatically open pipe sep files directly in Excel. e.g you have a .csv that opens comma sep files in each columsn in Excel. Can there be something done on similar lines that recognizes a pipe sep file too? Maybe I am stretching it a bit. Thanks for your feedback though.

i don't think so but im possibly wrong, all that ican think of at the moment are .csv file and .prn files,

the best i think i could do is to simulate it through a button or similar on a worksheet linked to the Application.GetOpenFile method then run the Workbook.OpenText method on the results this would simulate opening the file like a csv. If you wan't me (or anyone else for that matter) to produce some code for you just give me a shout.

you could open multiple workbooks the same way by this method by setting the paths to an array and then looping through each member of the array. I know this method has appeared a couple of times already in this forum, search for application.getopenfile and you should get some examples

Dave