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!
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]"
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") & ""
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
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!
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.
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
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?