Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Parse Delimited String to Rows in VBA

  1. #1
    Join Date
    27th May 2010
    Posts
    9

    Parse Delimited String to Rows in VBA

    Hi there,

    I'm sure there's an answer to this that I simply don't know the search terms to discover, so my apologies in advance if it's a dumb question.

    I currently have a function which grabs a csv file from a variable URL, saves it to a temporary directory and then imports it neatly and delimited into a new sheet. If possible though, I'd love to just do the whole process in VBA and skip the saving and opening steps.

    I have got as far as xmlhttp-ing the csv into a string, which is working perfectly. My dilemma now is that I haven't the foggiest idea how to parse that string (delimited by spaces) into rows. The closest I've come to an answer is automating Text to Columns on my string and then transposing the resulting columns to rows, but that just seems clunky, and I'm wondering if there's a more elegant method I can deploy.

    Strings that will be passed to this function are never more than 365 entries long, but can be any value less than that.

    Any pointers, solutions or general guidance would be much much appreciated!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st March 2010
    Location
    God's Own Country
    Posts
    3,612

    Re: Parse Delimited String to Rows in VBA

    Explore Split() function and arrays

  3. #3
    Join Date
    27th May 2010
    Posts
    9

    Re: Parse Delimited String to Rows in VBA

    thanks for the heads up - I need to learn how to use arrays, of which I remain shamefully ignorant.

    One question on that front - I'm assuming that given the variable number of entries per string, I'll be working with dynamic arrays. Do I need ultimately to ReDim this to a particular number of entries (ie 1 to 365) or can it remain unspecified?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    1st March 2010
    Location
    God's Own Country
    Posts
    3,612

    Re: Parse Delimited String to Rows in VBA

    No you shouldn't be doing a ReDim there. The array would expand based on how many elements are passed by the Split function.

  5. #5
    Join Date
    27th May 2010
    Posts
    9

    Re: Parse Delimited String to Rows in VBA

    fantastic - I shall get to work.

    Thank you for the incredibly swift response

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    8th March 2010
    Posts
    1,627

    Re: Parse Delimited String to Rows in VBA

    example:
    cells(1,1).resize(ubound(split("textstring",vbcrLf))+1)=worksheetfunction.transpose(split("textstring",vbcrLf))
    columns(1).TexttoColumns , ......

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Parse Delimited PDF Data Into Worksheet
    By Justin.W.Wolcott in forum EXCEL HELP
    Replies: 3
    Last Post: February 11th, 2008, 23:14
  2. Parse Number From Data String
    By R&D in forum EXCEL HELP
    Replies: 4
    Last Post: January 19th, 2008, 07:37
  3. Parse A Delimited String
    By ravenswood in forum EXCEL HELP
    Replies: 2
    Last Post: January 31st, 2007, 02:38
  4. Parse a String into multiple fields
    By SilverSequence in forum Excel and/or Access Help
    Replies: 5
    Last Post: September 11th, 2006, 23:47
  5. Replies: 5
    Last Post: January 4th, 2006, 17:16

Bookmarks

Posting Permissions

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