wake-up-neo.com

Durchführen von SQL-Abfragen für eine Excel-Tabelle in einer Arbeitsmappe mit VBA-Makro

Ich versuche, ein Excel-Makro zu erstellen, das mir folgende Funktion in Excel gibt:

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

Ich erlaube mir, Daten in den Tabellen meiner Arbeitsmappe mithilfe von SQL-Abfragen zu durchsuchen (und möglicherweise sogar einzufügen).

Das habe ich bisher gemacht:

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

Mein Skript funktioniert wie ein Zauber mit hart codierten Bereichen wie dem oben abgebildeten Ausschnitt. Es funktioniert auch sehr gut mit statisch benannten Bereichen.

Es funktioniert jedoch weder mit dynamischen benannten Bereichen noch mit TABLE NAMES, was mir am wichtigsten ist.

Die engste Antwort, die ich gefunden habe, ist dieser Kerl, der unter dem gleichen Leiden leidet: http://www.ozgrid.com/forum/showthread.php?t=72973

Helfen Sie jemandem?

Bearbeiten

Ich habe dies bisher gekocht, ich kann den resultierenden Namen dann in meinen SQL-Abfragen verwenden. Die Einschränkung ist, dass ich wissen muss, auf welchem ​​Blatt sich die Tabellen befinden. Können wir etwas dagegen tun?

Function getAddress()

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

    getAddress = myAddress

End Function

Vielen Dank!

sqlexcelvbaexcel-vba
27
Joan-Diego Rodriguez

Möglicherweise können Sie dazu die Adresse des dynamischen benannten Bereichs abrufen und als Eingabe in Ihre SQL-Zeichenfolge verwenden. So etwas wie:

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

Was eine Adresszeichenfolge ausspuckt, etwa $A$1:$A$8

Bearbeiten:

Wie ich in meinem Kommentar unten gesagt habe, können Sie die vollständige Adresse (einschließlich des Blattnamens) dynamisch abrufen und entweder direkt verwenden oder den Blattnamen für eine spätere Verwendung parsen:

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

Was ergibt einen String wie =Sheet1!$C$1:$C$4. Für Ihr Codebeispiel oben könnte Ihre SQL-Anweisung stehen

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

strSQL = "SELECT * FROM [strRangeAddress]"
12
Jake Bathman
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

Verwenden Sie es in Ihrer SQL so

sSQL = "Select * from " & GetRange("NameOfTable") & ""
8
Johan Kreszner

Aufbau auf Joan-Diego Rodriguez 'Routine mit Jordis Ansatz und etwas Code von Jacek Kotowski - Diese Funktion konvertiert jeden Tabellennamen für die aktive Arbeitsmappe in eine verwendbare Adresse für SQL-Abfragen. 

Anmerkung zu MikeL: Die Hinzufügung von "[#All]" enthält Überschriften, die die von Ihnen gemeldeten Probleme vermeiden.

Function getAddress(byVal sTableName as String) as String 

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

End Function
4
Craig Hatmaker

Ich bin ein Anfänger, der an dem Code eines anderen Benutzers bastelt. Ich habe Ihren Code ausprobiert und mit der VBA-Hilfe gespielt. Folgendes funktionierte mit mir:

Function currAddressTest(dataRangeTest As Range) As String

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

End Function

Wenn ich ein Datenquellenargument für meine Funktion auswähle, wird es in das Sheet1 $ A1: G3-Format umgewandelt. Wenn Excel es in meiner Formel in Table1 [#All] referenziert, funktioniert die Funktion weiterhin einwandfrei 

Ich habe es dann in Ihrer Funktion verwendet (versucht zu spielen und ein weiteres Argument hinzuzufügen, um WO zu injizieren ...

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

Ich hoffe, Ihre Funktion entwickelt sich weiter, ich finde es sehr nützlich. Einen schönen Tag noch!

2
Jacek Kotowski

Beantworten Sie einfach den zweiten Teil Ihrer Frage, wie Sie den Namen des Blattes erhalten, auf dem sich ein Tisch befindet:

Dim name as String

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

Bearbeiten:

Um es klarer zu machen: jemand schlug vor, Range für ein Sheet-Objekt zu verwenden. In diesem Fall brauchen Sie nicht; der Bereich, in dem die Tabelle lebt, kann unter Verwendung des Namens der Tabelle erhalten werden; Dieser Name steht im gesamten Buch zur Verfügung. Das Aufrufen von Range alleine funktioniert also gut.

1
Jordi

Hi hat sich kürzlich mit diesem Problem befasst und hatte Probleme mit der benannten Tabelle (Listenobjekt) in Excel

wenn Sie ein Suffix '$' auf den Tabellennamen setzen, ist alles in der Welt gut

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
0
MikeL