Effectuer des requêtes SQL sur une Table Excel dans un classeur avec la Macro VBA

j'essaie de faire une macro excel qui me donnera la fonction suivante dans Excel:

=SQL("SELECT heading_1 FROM Table1 WHERE heading_2='foo'")

me permettant de rechercher (et peut-être même d'insérer) des données dans les Tables de mon classeur en utilisant des requêtes SQL.

c'est Ce que j'ai fait jusqu'à présent:

Sub SQL()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * FROM [Sheet1$A1:G3]"

rs.Open strSQL, cn

Debug.Print rs.GetString

End Sub

mon script fonctionne comme un charme avec des gammes codées comme celle du snippet ci-dessus. Il fonctionne également très bien avec static nommé ranges.

cependant, il ne fonctionnera pas avec des gammes nommées dynamiques ou des noms de TABLE qui est le plus important pour moi.

le plus proche que j'ai trouvé d'une réponse est ce type souffrant de la même affliction: http://www.ozgrid.com/forum/showthread.php?t=72973

aider quelqu'un?

Modifier

j'ai cuit jusqu'à présent, je peux ensuite utiliser ce nom dans Mes requêtes SQL. La limitation est que j'ai besoin de savoir sur quelle feuille, les tableaux sont. Pouvons-nous faire quelque chose à ce sujet?

Function getAddress()

    myAddress = Replace(Sheets("Sheet1").Range("Table1").address, "$", "")
    myAddress = "[Sheet1$" & myAddress & "]"

    getAddress = myAddress

End Function

Merci!

25
demandé sur Joan-Diego Rodriguez 2013-11-03 20:53:02

7 réponses

une chose que vous pouvez être capable de faire est d'obtenir l'adresse de la plage nommée dynamique, et l'utiliser comme entrée dans votre chaîne SQL. Quelque chose comme:

Sheets("shtName").range("namedRangeName").Address

Qui va cracher une chaîne d'adresse, quelque chose comme $A:$A

Edit:

comme je l'ai dit dans mon commentaire ci-dessous, vous pouvez dynamiquement obtenir l'adresse COMPLÈTE (y compris le nom de la feuille) et soit l'utiliser directement ou analyser le nom de la feuille à utiliser ultérieurement:

ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal

qui donne une chaîne comme =Sheet1!$C:$C . Donc pour votre exemple de code ci-dessus, votre instruction SQL pourrait être

strRangeAddress = Mid(ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal,2)

strSQL = "SELECT * FROM [strRangeAddress]"
12
répondu Jake Bathman 2013-11-04 15:26:05
Public Function GetRange(ByVal sListName As String) As String

Dim oListObject As ListObject
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook

For Each ws In wb.Sheets
    For Each oListObject In ws.ListObjects
        If oListObject.Name = sListName Then
            GetRange = "[" & ws.Name & "$" & Replace(oListObject.Range.Address, "$", "") & "]"
        Exit Function
        End If
    Next oListObject
Next ws


End Function

dans votre SQL utilisez-le comme ceci

sSQL = "Select * from " & GetRange("NameOfTable") & ""
8
répondu Johan Kreszner 2015-10-20 11:20:31

S'appuyant sur la routine de Joan-Diego Rodriguez avec L'approche de Jordi et une partie du code de Jacek Kotowski - cette fonction convertit n'importe quel nom de table pour le cahier actif en une adresse utilisable pour les requêtes SQL.

Note à MikeL: L'ajout de "[#All]" inclut des titres qui évitent les problèmes que vous avez signalés.

Function getAddress(byVal sTableName as String) as String 

    With Range(sTableName & "[#All]")
        getAddress= "[" & .Parent.Name & "$" & .Address(False, False) & "]"
    End With

End Function
3
répondu Craig Hatmaker 2017-07-18 18:02:13

je suis un débutant bricolage sur le code de quelqu'un d'autre alors s'il vous plaît être indulgent et corriger davantage mes erreurs. J'ai essayé votre code et j'ai joué avec L'aide de VBA ce qui suit a fonctionné avec moi:

Function currAddressTest(dataRangeTest As Range) As String

    currAddressTest = ActiveSheet.Name & "$" & dataRangeTest.Address(False, False)

End Function

lorsque je sélectionne l'argument source de données pour ma fonction, il est transformé en format Sheet1$A1:G3. Si excel le change en Tableau1 [#All] référence dans ma formule, la fonction fonctionne toujours correctement

Je l'ai ensuite utilisé dans votre fonction (essayé de jouer et Ajouter un autre argument à injecter à où...

Function SQL(dataRange As Range, CritA As String)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim currAddress As String



currAddress = ActiveSheet.Name & "$" & dataRange.Address(False, False)

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon


strSQL = "SELECT * FROM [" & currAddress & "]" & _
         "WHERE [A] =  '" & CritA & "'  " & _
         "ORDER BY 1 ASC"

rs.Open strSQL, cn

SQL = rs.GetString

End Function

J'espère que votre fonction se développe davantage, je la trouve très utile. Have a nice day!

2
répondu Jacek Kotowski 2015-03-05 13:55:21

juste répondre à la deuxième partie de votre question sur l'obtention du nom de la feuille où une table est:

Dim name as String

name = Range("Table1").Worksheet.Name

Edit:

pour rendre les choses plus claires: quelqu'un a suggéré que D'utiliser la portée sur un objet de feuille. Dans ce cas, vous n'avez pas besoin; La gamme où vit la table peut être obtenue en utilisant le nom de la table; ce nom est disponible tout au long du livre. Donc, appeler Range seul fonctionne bien.

1
répondu Jordi 2016-11-22 20:25:39

Hi récemment examiné dans ce et avait des problèmes référencement de la table nommée (objet liste) dans excel

si vous placez un suffixe '$' sur le nom de la table tout est bien dans le monde

Sub testSQL()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    ' Declare variables
    strFile = ThisWorkbook.FullName

    ' construct connection string
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

    ' create connection and recordset objects
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    ' open connection
    cn.Open strCon

    ' construct SQL query
    strSQL = "SELECT * FROM [TableName$] where [ColumnHeader] = 'wibble';"

    ' execute SQL query
    rs.Open strSQL, cn

    Debug.Print rs.GetString

    ' close connection
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub
-1
répondu MikeL 2017-04-10 12:43:27

a trouvé ça et ça a marché pour moi.

strSQL = "SELECT * FROM DataTable" 

'où DataTable est la plage nommée

Comment puis-je exécuter des instructions SQL sur une plage nommée dans une feuille excel?

-2
répondu fultonec 2017-05-23 10:31:22