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|7|TechTeam Test Newsletter|9|2|2|2
29|4|Women's Health News|2|0|0|0
57|2|Good growing kids|671|1|14|1
146|6|Student Health Zone|0|0|0|0
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