代码之家  ›  专栏  ›  技术社区  ›  amr osama

Excel:运行时错误13类型不匹配

  •  0
  • amr osama  · 技术社区  · 15 年前

    在这段代码的末尾,我遇到了运行时错误'13',即使我试图在注释的代码行之间切换

    Public Property Get cnnString() As String
    'old: strcnnString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Me.DBFolderName & ";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"
    'strcnnString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Me.DBFolderName & ";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"
    'new code before edits: strcnnString = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & folder_name & ";Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False "
    strcnnString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Me.DBFolderName & ";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"
    cnnString = strcnnString
    End Property
    

    Private strcnnString As String
    Private folder_name As String
    
    Public Property Get DBFolderName() As String
     DBFolderName = folder_name
    End Property
    
    Public Property Let DBFolderName(ByVal newvalue As String)
    folder_name = newvalue
    End Property
    
    'calling the property which rais the error
      With ActiveSheet.QueryTables.Add(Connection:=Array(Me.cnnString), Destination:=Range("A1"))
    

    有什么解决办法吗?

    2 回复  |  直到 15 年前
        1
  •  0
  •   Fionnuala    15 年前

    http://www.connectionstrings.com/excel-2007

    或者尝试:

    strcnnString = "Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Me.DBFolderName & ";"
    

    编辑再评论

    我不太确定我是否理解你,但也许你的意思是:

    strcnnString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" &  Me.DBFolderName & ";"
    

    编辑更多注释

     ActiveSheet.QueryTables.Add Connection:=cnnString, Destination:=Range("A1")
    
        2
  •  1
  •   Andrew    15 年前

    Add可以接受一些东西,但我不确定数组是否是其中之一,您可以尝试只传递连接字符串而不将其放在数组中吗。