vba - Excel-Range XML uses two rows for header -

i use this function recordsets range-objects without setting ado-connections.

function getrecordset(rng range) object          dim xlxml object     dim rst object      set rst = createobject("adodb.recordset")     set xlxml = createobject("msxml2.domdocument")      xlxml.loadxml rng.value(xlrangevaluemspersistxml)      rst.open xlxml      set getrecordset = rst  end function 

i typically use one header row. expected results if data starts in row(1).

however, if there 1+ rows above data, excel assumes have two header-rows , concatenates them blank.

therefore, recordset uses fieldnames ![underneath's header, mate myfield] instead of ![myfield].

more technically speaking, problem rng.value(xlrangevaluemspersistxml) returns concatenated 2 rows header , i'm unable set 1 row.

interested hear thoughts!

edit: workaround might replace leading blanks like

xlxml.loadxml replace(rng.value(xlrangevaluemspersistxml), "rs:name="" ", "rs:name=""") 

or iterative

replace xml, "rs:name=""cell1 cell", "rs:name=""cell2") 

for each datafield

i've been struggling same issue. way around i've found far insert row above header row , pass required range after that.

when filtering have append space @ beginning.

rs.filter = "[field_header] = " & strsome_text 

becomes

rs.filter = "[ field_header] = " & strsome_text 

as appears excel appending it. trying trim:

xlxml.loadxml trim(rnginputrange.value(xlrangevaluemspersistxml)) 

with no joy. couple of headers have spaces using replace makes things more ugly.

adding row , appending space beginning of field name means can filter on field names spaces header row on row 1, although bit dirty, enough workaround me now.