Excel-Range XML uses two rows for header

Question

I use this function to get recordsets from Range-Objects without setting up ADO-connections.

Function GetRecordset(rng As Range) As Object    

    Dim xlXML As Object
    Dim rst As 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. I get the expected results if the data starts in Row(1).

However, if there are 1+ Rows above the data, excel assumes i have two header-rows and concatenates them with a blank.

Therefore, my recordset uses Fieldnames like ![Underneath's your header, mate MyField] instead of ![MyField].

More technically speaking, the problem is that rng.Value(xlRangeValueMSPersistXML) returns the concatenated two rows as header and I'm unable to set that to one row.

Interested to hear your thoughts!

EDIT: a workaround might be to replace leading blanks with something like

xlXML.LoadXML Replace(rng.Value(xlRangeValueMSPersistXML), "rs:name="" ", "rs:name=""")

or an iterative

Replace XML, "rs:name=""Cell1 Cell", "rs:name=""Cell2")

for each datafield

Just leave the "extra" header row out of the Range you pass the function - i.e. define your range starting in Row(2)... — Comintern
– Comintern, Commented Jan 4, 2017 at 16:58
Thanks, but ofc I tested this before posting. Range.Value will extend it's range to get a value for AttributeType rs:name="<<Header>>" to up to two Rows upwards. — Martin Dreher
– Martin Dreher, Commented Jan 5, 2017 at 7:59

goto · Accepted Answer · 2017-02-07 12:29:12Z

1

I've been struggling with the same issue. The only way around I've found so far is to just insert another row above the header row and then pass the required range after that.

When filtering I have to append an extra space at the beginning. So

RS.Filter = "[field_header] = " & strSome_text

becomes

RS.Filter = "[ field_header] = " & strSome_text

as it appears Excel is appending it. I am trying Trim:

xlXML.LoadXML Trim(rngInputRange.value(xlRangeValueMSPersistXML))

with no joy. A couple of my headers have spaces so using Replace makes things much more ugly.

Adding the extra row and appending the space to the beginning of the field name means that I can filter on field names with spaces as I could normally with the header row on row 1, so although a bit dirty, this is a good enough workaround for me for now.

edited Feb 7, 2017 at 12:29

goto

8,21310 gold badges50 silver badges60 bronze badges

answered Feb 7, 2017 at 12:03

BoffWx

1064 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Dreher Over a year ago

Try Debug.Print rngInputRange.value(xlRangeValueMSPersistXML). The XML code shows us whyTrim won't work here. A more specific Replace will likely solve the problem, if the row above the header row is blank: xlXML.LoadXML Replace(rngInputRange.Value(xlRangeValueMSPersistXML), "rs:name="" ", "rs:name="""). Technically, one could dynamically replace the rs:name="Cell1 Cell2" with rs:name="Cell2" regardless of Cell1's content

Collectives™ on Stack Overflow

Excel-Range XML uses two rows for header

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related