vba - Issue in TextToColumns with xlFixedWidth loosing blanks -

i trying implement way parse many fixed width formats (hundreds of them) simple vba macro.

the software generates these formats provides me neat description of them parsed follows:

fd1 fd2 fd3 fd4 --  --  --  -- 2   3   3   5 aaccc  fuuuuu aahhh  fggggg 55hhh  fvvvvv 55hhh--lvvvvv ppnnn  lvvvvv ppjjj--lddddd 

mock data created make tests reproduce issue.

notice data can or cannot preceded (or succeeded) character, including spaces. wrote following (very naive , non reviewed) code:

sub loopparser()     const sizesrow = 3     const datastart = "a4"      dim tocut     tocut = 0      range(datastart).select      until isempty(activecell)         tocut = activesheet.cells(sizesrow, activecell.column).value          call parseonefield(activecell.address, cint(tocut))         activecell.offset(0, 1).select     loop      range(datastart).select  end sub  sub parseonefield(targetcell, desiredsize integer)     const maxlayout = 10000#      range(targetcell).select     range(selection, selection.end(xldown)).select     selection.texttocolumns _         destination:=range(targetcell), _         datatype:=xlfixedwidth, _         fieldinfo:=array(array(0, xltextformat), _                          array(desiredsize, xltextformat), _                          array(maxlayout, xltextformat) _                          ), _         trailingminusnumbers:=true  end sub 

but has problem: when try parse third columns of data blanks loose (besides using xltextformat (2)) treat them, end this:

fd1 fd2 fd3 fd4 --  --  --  -- 2   3   3   5 aa  ccc fuu uuu aa  hhh fgg ggg 55  hhh fvv vvv 55  hhh --l vvvvv pp  nnn lvv vvv pp  jjj --l ddddd 

when expected result (preserving blanks in third field)

fd1 fd2 fd3 fd4 --  --  --  -- 2   3   3   5 aa  ccc   f uuuuu aa  hhh   f ggggg 55  hhh   f vvvvv 55  hhh --l vvvvv pp  nnn   l vvvvv pp  jjj --l ddddd 

any ideas on how solve this? workaround suffice.

thinking of designing tests grab unused character in given data-set , replace blanks prior change, change character blanks afterwards, feels cheating. :-(

update:

i fell uncomfortable post xlsm file somewhere @sgdva, instead let me give directions on how reproduce exact problem.

start heading this gist on github, find excerpts of code , data, correctly formatted.

then copy/past input data new spreadsheet, hope excel recognize tabs in first tree rows ans able separate names/sizes columns, while keeping next 6 lines simple text.

then copy/paste code brand new excel module, , run against data.

let me know in comments if still have problem.

finally got solution!!

even more naive , dirtier previous code, wanna post here future reference.

sub loopparser()     const sizesrow = 3     const datastart = "a4"     const sizesstart = "a3"      dim cutpoint     dim eachsize(1) integer     dim cutpointsarray() variant     dim currentarraypos integer      cutpoint = 0     currentarraypos = 0      range(sizesstart).select      eachsize(0) = cint(cutpoint)     eachsize(1) = xltextformat      redim preserve cutpointsarray(0 currentarraypos)     cutpointsarray(currentarraypos) = eachsize              currentarraypos = currentarraypos + 1         cutpoint = cutpoint + activesheet.cells(sizesrow, activecell.column).value         eachsize(0) = cint(cutpoint)         eachsize(1) = xltextformat          redim preserve cutpointsarray(0 currentarraypos)         cutpointsarray(currentarraypos) = eachsize         'call parseonefield(activecell.address, cint(cutpoint))         activecell.offset(0, 1).select     loop until isempty(activecell)      range(datastart).select     range(selection, selection.end(xldown)).select     selection.texttocolumns _         destination:=range(datastart), _         datatype:=xlfixedwidth, _         fieldinfo:=cutpointsarray, _         trailingminusnumbers:=true      range(datastart).select  end sub 

the greater changes are:

1. the sizes must summed each cutpoint, instead of array of sizes, have cutpointsarray
2. instead of parsing each column, 1 @ time, parsed sizes , constructed cutpointsarray in 1 loop , used parse data in 1 single step