Excel范围XML使用两行头 [英] Excel-Range XML uses two rows for header

问题描述

我使用这个函数从 Range -Objects获取记录集，而不设置ADO连接。

 函数GetRecordset（rng As Range）As Object 
 
 Dim xlXML As Object 
 Dim rst As Object 
 
设置rst = CreateObject（ADODB.Recordset）
设置xlXML = CreateObject（MSXML2.DOMDocument）
 
 xlXML.LoadXML rng.Value（xlRangeValueMSPersistXML）
 
 rst.Open xlXML 
 
设置GetRecordset = rst 
 
结束函数
  

我通常使用一个标题行。如果数据从 Row（1）开始，我会得到预期的结果。

然而，如果有1+ 行数据之外，excel假设我有两个标题行，并将它们连接成一个空白。

因此，我的记录集使用字段名，如！[Underneath的你的头，配对MyField] 而不是！[MyField]

从技术上说，问题是 rng.Value（xlRangeValueMSPersistXML）返回连接的两个行为标题，我无法将其设置为一行。

有兴趣听到您的想法！

编辑：解决方法可能是用

  xlXML.LoadXML替换前导空格rng.Value（xlRangeValueMSPersistXML），rs：name =，rs：name =）
  

或迭代

 替换XML，rs：name =Cell1 Cell，rs：name = 小区2\" ）
  

每个数据域

解决方案

我一直在努力解决同样的问题。到目前为止，我发现的唯一方法是在标题行上方插入另一行，然后再传递所需的范围。

当过滤时，我必须附加一开始就有一个额外的空间。
所以

  RS.Filter =[field_header] =& strSome_text 
  

成为

 code> RS.Filter =[field_header] =& Excel正在追加它，strSome_text 
  

我正在尝试修剪：

  xlXML.LoadXML修剪（rngInputRange.value （xlRangeValueMSPersistXML））
  

没有快乐。我的几个标题有空格，因此使用替换使事情更加丑陋。

添加额外的行和附加字段名称开头的空格意味着我可以使用空格过滤字段名称，因为我可以正常使用第1行的标题行，因此尽管有点脏，这对我来说是一个很好的解决方法。 p>

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

解决方案

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.

这篇关于Excel范围XML使用两行头的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！