Hoping someone may be able to offer a better approach to what I am doing. Unfortunately the data and code sit on client systems so I can't share them.
I have this long string which was previously a data table and need to split up the values into rows and columns again. The system is heavily locked down so I am limited to using VBA. The best way I can think of is to use regular expressions to identify columns and rows. I've set up my regular expression object and executed against the input string, have all the matches I need which is fine. The problem is that if I do
re = CreateObject("VBScript.RegExp") re.pattern = mypattern re.split(myString)
As far as I can see there is no way to retain the values I am splitting on. In some cases I want to split in the middle of the regex string anyway.
The most promising solution I think is to do
re = CreateObject("VBScript.RegExp") re.pattern = mypattern Set matches = re.execute(myString) for each match in matches: 'do something with match.firstindex
I considered just inserting delimiters and then using split. Unfortunately VBA seems to have no method to insert a character into a string it looks a bit clunky to use the firstindex.
Does anyone have any thoughts on better approaches? Much appreciated if so.