[SQL] VBA 매크로 통합 문서에서 엑셀 표에 SQL 쿼리를 수행

VBA 매크로 통합 문서에서 엑셀 표에 SQL 쿼리를 수행

나는 나에게 Excel에서 다음과 같은 기능을 제공합니다 엑셀 매크로를 만들려고 노력하고 있습니다 :

=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

내 스크립트는 위의 코드 조각의 하나로서 하드 코드 범위와 매력처럼 작동합니다. 또한 정적 명명 된 범위와 매우 잘 작동합니다.

그러나, 그것은 나에게 가장 중요한 중 하나 동적 명명 된 범위 또는 테이블 이름 작동하지 않습니다.

내가 대답의 발견 가장 가까운 같은 고통에서이 사람의 고통입니다 : 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

감사!

해결법

1. ==============================

   1.당신이 할 수 있습니다 한가지는 당신의 SQL 문자열의 입력으로 동적 명명 된 범위의 주소 및 사용을 얻을 수 있습니다. 뭔가 같은 :

   당신이 할 수 있습니다 한가지는 당신의 SQL 문자열의 입력으로 동적 명명 된 범위의 주소 및 사용을 얻을 수 있습니다. 뭔가 같은 :

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

   어떤, 같은 것을 주소 문자열을 뱉어 $ A $ 1 : $ A $ (8)

   편집하다:

   내가 아래에있는 내 의견에 말했듯이, 동적 (시트 이름을 포함) 전체 주소를 얻고 직접 사용하거나 나중에 사용하기 위해 시트 이름을 구문 분석 할 수 있습니다 :

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

   = Sheet1에 같은 문자열에서 어떤 결과 1 $ C $! $ C $ 4. 그래서 위의 코드 예를 들어, SQL 문은 수

   strRangeAddress = Mid(ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal,2)
   
   strSQL = "SELECT * FROM [strRangeAddress]"
   

2. ==============================

   2.

   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") & ""
   

3. ==============================

   3.조안 - 디에고 로드리게스의 조르디의 접근 방식과 일상과 제이 섹 Kotowski는의 코드의 일부에 구축 -이 기능은 SQL 쿼리 가능한 주소로 현재 통합 문서에 대한 모든 테이블 이름을 변환합니다.

   조안 - 디에고 로드리게스의 조르디의 접근 방식과 일상과 제이 섹 Kotowski는의 코드의 일부에 구축 -이 기능은 SQL 쿼리 가능한 주소로 현재 통합 문서에 대한 모든 테이블 이름을 변환합니다.

   미켈로 참고 :의 추가 "[이 #all]는"당신이보고 된 문제를 피할 제목을 포함합니다.

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

4. ==============================

   4.나는 초보자는 너무 관대하시기 바랍니다 추가 내 오류를 수정 다른 사람의 코드를 땜질입니다. 난 당신의 코드를 시도하고 다음은 나와 함께 일한 VBA의 도움으로 연주 :

   나는 초보자는 너무 관대하시기 바랍니다 추가 내 오류를 수정 다른 사람의 코드를 땜질입니다. 난 당신의 코드를 시도하고 다음은 나와 함께 일한 VBA의 도움으로 연주 :

   Function currAddressTest(dataRangeTest As Range) As String
   
       currAddressTest = ActiveSheet.Name & "$" & dataRangeTest.Address(False, False)
   
   End Function
   

   내 기능에 대한 데이터 소스 인수를 선택하면, 그것은 Sheet1의 $ A1로 전환된다 G3 형식을. 엑셀 내 공식에 표 [이 #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
   

   함수가 더 발전 희망, 나는 그것이 매우 유용하게 찾을 수 있습니다. 좋은 하루 되세요!

5. ==============================

   5.그냥 테이블 인 시트의 이름을 얻기에 관하여 질문의 두 번째 부분에 대답 :

   그냥 테이블 인 시트의 이름을 얻기에 관하여 질문의 두 번째 부분에 대답 :

   Dim name as String
   
   name = Range("Table1").Worksheet.Name
   

   편집하다:

   일을 더 명확하게하려면 누군가가 시트 개체의 범위를 사용하는 것이 좋습니다. 이 경우에, 당신은 필요가 없습니다; 테이블 생활이 테이블의 이름을 사용하여 얻을 수있는 범위; 이 이름은 책 전반에 걸쳐 사용할 수 있습니다. 그래서 혼자 범위를 호출하면 잘 작동합니다.

6. ==============================

   6.안녕하세요 최근에이를 바라 보 고 엑셀 내에서 문제 명명 된 테이블 (목록 개체)를 참조했다

   안녕하세요 최근에이를 바라 보 고 엑셀 내에서 문제 명명 된 테이블 (목록 개체)를 참조했다

   당신이 테이블 이름에 접미사 '$'배치하면 전 세계에 잘

   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
   

7. ==============================

   7.이를 발견하고 나를 위해 일했다.

   이를 발견하고 나를 위해 일했다.

   strSQL = "SELECT * FROM DataTable" 
   

   'DataTable을 명명 된 범위는 어디

   어떻게 엑셀 시트 내에서 명명 된 범위에 SQL 문을 실행할 수 있습니까?

from https://stackoverflow.com/questions/19755396/performing-sql-queries-on-an-excel-table-within-a-workbook-with-vba-macro by cc-by-sa and MIT license