是否可以将excel查询连接用作adodb.connection源 [英] Is it possible to use an excel query connection as an adodb.connection source

问题描述

美好的一天，



我已将数据导入到我的excel数据模型中，并且可以使用以下方法将其导入到工作表中：

Good day all,

I have imported data into my excel datamodel and have been able to import that back into a sheet using :

Application.CutCopyMode = False
    Application.DisplayAlerts = False
    
    Dim tbl As ListObject
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets.Add
    
    connectionName = fieldName & "sConnection"
    
    With ws.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook.Connections("Query - 
        TRENDS_GroupData"), Destination:=Range("$A$1")).TableObject
        .WorkbookConnection.OLEDBConnection.CommandText = Array( _
            "Evaluate" & Chr(13) & "" & Chr(10) & "Calculatetable" & Chr(13) & "" & 
             Chr(10) & "(DISTINCT(UNION(" & Chr(13) & "" & Chr(10) & 
             "Distinct(Trends_MyCoData[" & fieldName & "])," & Chr(13) & "" & Chr(10) 
             & "Distinct(Trends_GroupData[" & fieldName & "])" & Chr(13) & "" & 
             Chr(10) & ")))")
        .WorkbookConnection.OLEDBConnection.CommandType = xlCmdDAX
        .RowNumbers = False
        .PreserveFormatting = True
        .RefreshStyle = 1
        .AdjustColumnWidth = True
        .ListObject.DisplayName = fieldName
    End With
    
    Set tbl = ActiveSheet.ListObjects(1)
    tbl.Refresh

在另一个工作簿中，我使用ADODB.Connection查询表在带有表的工作表上使用：

In another workbook I use an ADODB.Connection to query a table on a worksheet with a table using :

Public Function CreateSQLConnection() As Object
   
    '---Connecting to the Data Source---
    Set CreateSQLConnection = CreateObject("ADODB.Connection")
    With CreateSQLConnection
        .Provider = "Microsoft.Mashup.OleDb.1"
        .ConnectionString = "Data Source=ActiveWorkbook.Connections(""Query - 
         TRENDS_GroupData"");" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
        .Open
    End With

End Function

这些选项一直很好用，但是我现在已经很多了更大的数据集，并想知道是否有可能使用Connection作为ADODB.Connection的源，因为我到目前为止没有成功。



最接近的我让它工作弹出以下消息：

无法更新。数据库或对象是只读的。



总而言之，我的最终目标是能够使用SQL查询数据模型，如果有其他方法来代替上述方法我会很感激指导。



任何想法都会受到高度赞赏。



我的尝试：



将模式更改为Read或adRead

将提供者更改为JET或Mashup

一些排列不同设置。

These to options have been working great however I have now come accross a much larger dataset and was wondering if it is possible to use the Connection as a source for the ADODB.Connection as I have so far met with no success.

The closest I have come to getting it to work pops up the following message :
"Cannot update. Database or object is read-only."

All in all my end goal is to be able to query the data model using SQL, if there is another way to do it instead of the above I would appreciate the guidance.

Any thoughts would be highly appreciated.

What I have tried:

Changing the mode to Read or adRead
Changing the provider to JET or Mashup
A few permutations of different settings.

推荐答案

A

1））。TableObject
.WorkbookConnection。 OLEDBConnection.CommandText = Array（_
评估& Chr（ 13 ）& & Chr（ 10 ）& Calculatetable& Chr （ 13 ）& & ;
Chr（ 10 ）& （DISTINCT（UNION（& Chr（ 13 ）& & Chr（ 10 ）&
Distinct（Trends_MyCoData [& fieldName& ]），& Chr（ 13 ）& & Chr（ 10 ）
& Distinct（Trends_GroupData [& fieldName& ]）& Chr（ 13 ）& &
Chr（ 10 ）& ））））
.WorkbookConnection.OLEDBConnection.CommandType = xlCmdDAX
.RowNumbers = False
.PreserveFormatting = True
.RefreshStyle = 1
.AdjustColumnWidth = True
.ListObject.DisplayName = fieldName
结束 使用

设置 tbl = ActiveSheet.ListObjects（ 1 ）
tbl.Refresh

1")).TableObject .WorkbookConnection.OLEDBConnection.CommandText = Array( _ "Evaluate" & Chr(13) & "" & Chr(10) & "Calculatetable" & Chr(13) & "" & Chr(10) & "(DISTINCT(UNION(" & Chr(13) & "" & Chr(10) & "Distinct(Trends_MyCoData[" & fieldName & "])," & Chr(13) & "" & Chr(10) & "Distinct(Trends_GroupData[" & fieldName & "])" & Chr(13) & "" & Chr(10) & ")))") .WorkbookConnection.OLEDBConnection.CommandType = xlCmdDAX .RowNumbers = False .PreserveFormatting = True .RefreshStyle = 1 .AdjustColumnWidth = True .ListObject.DisplayName = fieldName End With Set tbl = ActiveSheet.ListObjects(1) tbl.Refresh

在另一个工作簿中，我使用ADODB.Connection查询表在带有表的工作表上使用：

In another workbook I use an ADODB.Connection to query a table on a worksheet with a table using :

Public Function CreateSQLConnection() As Object
   
    '---Connecting to the Data Source---
    Set CreateSQLConnection = CreateObject("ADODB.Connection")
    With CreateSQLConnection
        .Provider = "Microsoft.Mashup.OleDb.1"
        .ConnectionString = "Data Source=ActiveWorkbook.Connections(""Query - 
         TRENDS_GroupData"");" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
        .Open
    End With

End Function

这些选项一直很好用，但是我现在已经很多了更大的数据集，并想知道是否有可能使用Connection作为ADODB.Connection的源，因为我到目前为止没有成功。



最接近的我让它工作弹出以下消息：

无法更新。数据库或对象是只读的。



总而言之，我的最终目标是能够使用SQL查询数据模型，如果有其他方法来代替上述方法我会很感激指导。



任何想法都会受到高度赞赏。



我的尝试：



将模式更改为Read或adRead

将提供者更改为JET或Mashup

一些排列不同的设置。

These to options have been working great however I have now come accross a much larger dataset and was wondering if it is possible to use the Connection as a source for the ADODB.Connection as I have so far met with no success.

The closest I have come to getting it to work pops up the following message :
"Cannot update. Database or object is read-only."

All in all my end goal is to be able to query the data model using SQL, if there is another way to do it instead of the above I would appreciate the guidance.

Any thoughts would be highly appreciated.

What I have tried:

Changing the mode to Read or adRead
Changing the provider to JET or Mashup
A few permutations of different settings.

这篇关于是否可以将excel查询连接用作adodb.connection源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！