Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    7

    Unanswered: Read Pipe delimeted file to Excel

    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 !!

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  3. #3
    Join Date
    Jul 2004
    Posts
    7
    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •