如何使用VBA ADODB连接连接封闭的工作簿，以命名为字符\

发布于 2025-01-30 16:49:19 字数 919 浏览 4 评论 0原文

我有一个带有命名范围的工作簿。它的名称为a \ b。

我想使用ADODB连接和记录集来获取命名范围的值。为了做到这一点，我需要首先使用该命名范围打开记录集。

如果其名称不包括\，则此方法在其他命名范围上正常工作。

Public Sub test()
    Dim Path As String
    Path = "D:\New.xlsx"
    Dim cn As New ADODB.Connection
    cn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1"";"
    Dim rst As New ADODB.Recordset
    Dim namedRange As String
    namedRange = "A\B"
    'namedRange = Replace(namedRange, "\", ".")
    With rst
        .Open Source:="SELECT * FROM [" & namedRange & "]", _
              ActiveConnection:=cn, _
              CursorType:=adOpenStatic, _
              LockType:=adLockReadOnly, _
              Options:=adCmdText
    End With
End Sub

我试图用其他字符替换\，以测试它是否由非法字符引起。我测试的字符是〜！@＃$％^＆amp;*（）_+ - = {} | []

：

如果有人可以告诉我如何完成，这是非常感谢

。

原文

I have a workbook with a named range. Its name is A\B.

I would like to use Adodb connection and recordset to get the value of the named range. In order to do that, I need to first open recordset with that named range.

This method works fine on other named range if their names do not include \.

Public Sub test()
    Dim Path As String
    Path = "D:\New.xlsx"
    Dim cn As New ADODB.Connection
    cn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1"";"
    Dim rst As New ADODB.Recordset
    Dim namedRange As String
    namedRange = "A\B"
    'namedRange = Replace(namedRange, "\", ".")
    With rst
        .Open Source:="SELECT * FROM [" & namedRange & "]", _
              ActiveConnection:=cn, _
              CursorType:=adOpenStatic, _
              LockType:=adLockReadOnly, _
              Options:=adCmdText
    End With
End Sub

I tried to replace \ with other characters to test if it is caused by illegal character. The characters I tested are ~!@#$%^&*()_+-={}|[]:";'<>?,./, space and double \.

I know nothing of SQL.

If someone can tell me how it can be done, it is very much appreciated.

Thank you.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

沉溺在你眼里的海 2025-02-06 16:49:19

编辑：抱歉，我找到了Microsoft的参考文献，清楚地说： https://lealen.microsoft.com/en-us/office/troubleshoot/troubleshoot/access/error-usish-usis-s-special-characters

adodb driver使用msaccess driver因此，您发现的任何规则适用于您的情况。如果您查看标题为问题3 所引用斜线字符的部分，则答案是“解决此问题，请勿使用特殊字符。”

尝试添加单个单一围绕名称的行情：

With rst
    .Open Source:="SELECT * FROM ['" & namedRange & "']", _
          ActiveConnection:=cn, _
          CursorType:=adOpenStatic, _
          LockType:=adLockReadOnly, _
          Options:=adCmdText
End With

EDIT: I apologize, I found a reference from Microsoft that clearly says, "don't do that": https://learn.microsoft.com/en-us/office/troubleshoot/access/error-using-special-characters

ADODB driver is using MSAccess libraries so any rules you find for MSAccess apply to your situation. If you look at the section titled Problem 3 where the slash character is cited, the answer is "To work around this problem, do not use special characters."

Try adding single quotes around the name:

With rst
    .Open Source:="SELECT * FROM ['" & namedRange & "']", _
          ActiveConnection:=cn, _
          CursorType:=adOpenStatic, _
          LockType:=adLockReadOnly, _
          Options:=adCmdText
End With

回复收藏 0 原文

~没有更多了~