VBA 매크로를 사용하여 워크북 내의 Excel 테이블에서 SQL 쿼리 수행

VBA 매크로를 사용하여 워크북 내의 Excel 테이블에서 SQL 쿼리 수행

저는 엑셀에서 다음과 같은 기능을 제공할 엑셀 매크로를 만들려고 합니다.

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

SQL 쿼리를 사용하여 워크북의 표에서 데이터를 검색하거나 삽입할 수 있습니다.

지금까지 제가 해온 일은 다음과 같습니다.

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

내 스크립트는 위의 스니펫에 있는 것과 같이 하드 코딩된 범위를 가진 부적처럼 작동합니다.또한 정적 명명된 범위에서도 매우 잘 작동합니다.

그러나 동적 명명된 범위나 저에게 가장 중요한 TABLE NAMES에는 적용되지 않습니다.

제가 찾은 답 중 가장 가까운 것은 같은 고통을 겪고 있는 이 남자입니다. http://www.ozgrid.com/forum/showthread.php?t=72973

누구 도와드릴까요?

편집

지금까지 요리한 결과 이름을 SQL 쿼리에 사용할 수 있습니다.제한 사항은 테이블이 어떤 시트에 있는지 알아야 한다는 것입니다.우리가 그것에 대해 뭔가 할 수 있을까요?

Function getAddress()

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

    getAddress = myAddress

End Function

감사합니다!

한 가지 방법은 동적으로 명명된 범위의 주소를 가져와 SQL 문자열의 입력으로 사용하는 것입니다.다음과 같은 것:

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

주소 문자열을 뱉어낼 수도 있어요, 뭐랄까요.$A$1:$A$8

편집:

아래 내 의견에서 말했듯이 전체 주소(시트 이름 포함)를 동적으로 가져와서 직접 사용하거나 나중에 사용할 수 있도록 시트 이름을 구문 분석할 수 있습니다.

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

그 결과로 다음과 같은 문자열이 생성됩니다.=Sheet1!$C$1:$C$4따라서 위의 코드 예제에서 SQL 문은

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

SQL에서 다음과 같이 사용합니다.

sSQL = "Select * from " & GetRange("NameOfTable") & ""

Jordi의 접근법과 Jacek Kotowski의 코드 일부를 사용한 Joan-Diego Rodriguez의 루틴을 기반으로 - 이 함수는 활성 워크북의 모든 테이블 이름을 SQL 쿼리에 사용할 수 있는 주소로 변환합니다.

MikeL에 대한 주의: "[#All]"을 추가하면 보고한 문제를 방지할 수 있습니다.

Function getAddress(byVal sTableName as String) as String 

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

End Function

저는 다른 사람의 코드를 만지작거리는 초보자이기 때문에 너그럽게 봐주시고 제 오류를 더 수정해주시기 바랍니다.저는 당신의 코드를 사용해보고 VBA 도움말을 사용했습니다. 다음은 저와 함께 작동했습니다.

Function currAddressTest(dataRangeTest As Range) As String

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

End Function

함수에 대해 데이터 소스 인수를 선택하면 Sheet1$A1:G3 형식으로 바뀝니다.excel이 내 수식에서 Table1[#All] 참조로 변경해도 해당 기능은 여전히 제대로 작동합니다.

그런 다음 사용자의 함수에 사용했습니다(재생을 시도하고 다른 인수를 추가하여 WHERE에 주입하려고 했습니다...

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

당신의 기능이 더 발전하기를 바랍니다, 저는 그것이 매우 유용하다고 생각합니다.좋은 하루 보내세요!

표의 위치가 어디인지 시트의 이름을 얻는 것에 대한 질문의 두 번째 부분에 답하는 것입니다.

Dim name as String

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

편집:

좀 더 명확하게 하기 위해, 누군가가 시트 객체에 범위를 사용할 것을 제안했습니다.이 경우 테이블이 있는 범위는 테이블 이름을 사용하여 얻을 수 있으며, 이 이름은 책 전체에서 사용할 수 있습니다.따라서 Range만 호출해도 잘 작동합니다.

여기에 범위를 명명된 범위로 지정하고 쿼리에 사용할 수 있습니다(또는 시트당 단일 테이블이 있는 경우 전체 시트).

따라서 다음을 사용할 수 있습니다.

SELECT * FROM MyNamedRange

OR

SELECT * FROM [Sheet1$]

제가 과거에 했던 것이 모든 테이블을 명명된 범위로 설정하고 쿼리에서 직접 참조하는 것일 때 이러한 답변의 대부분은 주소를 파싱하는 데 도움이 되는 것 같습니다.

아래의 더 복잡한 쿼리 중 하나에서 내가 참조하는 모든 범위는 다음과 같이 명명됩니다.

SELECT Unit_Type AS [Unit Type], COUNT(*) AS [Number of Units], SQFT, Deposit AS [Deposit{$}], Rent AS [Base Rent{$}], SUM(RENT) AS [Base Rent Total{$}], SUM(MR) AS [Market Rent Total{$}] FROM
(
    SELECT f1.Unit_Code, Unit_Type, SQFT, Rent, Deposit, SWITCH(MR IS NULL,Rent,MR IS NOT NULL,MR) AS MR FROM
    (   
        SELECT t2.Unit_Code, t2.Unit_Type, SQFT, Rent, Deposit FROM
        (
            SELECT DISTINCT Unit_Code, Unit_Type
            FROM CommUnits
            WHERE Unit_Code NOT LIKE "%WAIT%" AND Exclude=0
        ) t2
        LEFT JOIN
        (
            SELECT UnitType_Code, SQFT, Rent, Deposit
            FROM ResUnitTypes
        ) t1 ON (t1.UnitType_Code = t2.Unit_Type)
    ) f1
    LEFT JOIN
    (
        SELECT Unit_Code, SUM(Current_Charge) AS MR
        FROM ResUnitAmenities
        WHERE Unit_Code NOT LIKE "%WAIT%"
        GROUP BY Unit_Code
    ) f2 on (f1.Unit_Code = f2.Unit_Code)
)
GROUP BY Unit_Type, SQFT, Deposit, Rent

안녕하세요, 최근에 이것을 조사했는데 엑셀 내에서 명명된 테이블(리스트 객체)을 참조하는 데 문제가 있었습니다.

테이블 이름에 접미사 '$'를 붙이면 모든 것이 잘 됩니다.

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

이것을 발견했고 그것은 나에게 효과가 있었습니다.

strSQL = "SELECT * FROM DataTable" 

'데이터 테이블은 명명된 범위입니다.

엑셀 시트 내의 지정된 범위에서 SQL 문을 실행하려면 어떻게 해야 합니까?

언급URL : https://stackoverflow.com/questions/19755396/performing-sql-queries-on-an-excel-table-within-a-workbook-with-vba-macro