그래픽으로 표시할 수 없는 Excel의 외부 데이터 쿼리에 매개 변수를 추가하는 방법은 무엇입니까?

그래픽으로 표시할 수 없는 Excel의 외부 데이터 쿼리에 매개 변수를 추가하는 방법은 무엇입니까?

저는 MS Excel 제품을 자주 사용합니다.Get External Data데이터베이스에 대해 쿼리를 실행하고 Excel에 잘 표시하는 간단한 보고서를 만듭니다.필터링 및 피벗 테이블과 같은 Excel의 뛰어난 기능과 사용자에게 친숙한 인터페이스는 이에 매우 적합합니다.그러나 Microsoft Query의 한 가지 제한 사항은 그래픽으로 표시할 수 없는 쿼리에 매개 변수를 추가할 수 없으므로 쓸 수 있는 SQL이 상당히 제한된다는 것입니다.

"그래픽으로 표시할 수 없는 쿼리에는 매개 변수가 허용되지 않습니다." 오류에 대한 해결책이 있습니까?

SQL Server 쿼리용 Excel 인터페이스에서는 사용자 지정 매개 변수를 사용할 수 없습니다.이 문제를 해결하는 방법은 일반 Microsoft 쿼리를 만든 다음 매개 변수를 추가한 다음 매개 변수로 분류된 쿼리를 연결의 속성에 붙여넣는 것입니다.다음은 Excel 2010에 대한 자세한 단계입니다.

1. 엑셀 열기
2. 데이터 탭으로 이동
3. From Other Sources 버튼에서 From Microsoft Query를 선택합니다.
4. "데이터 원본 선택" 창이 나타납니다.데이터 소스를 선택하고 확인을 클릭합니다.
5. 쿼리 마법사
1. 열을 선택합니다. 창이 나타납니다.목표는 일반 쿼리를 만드는 것입니다.작은 테이블에서 하나의 열을 선택하는 것을 추천합니다.
2. 데이터 필터링: 다음을 클릭하십시오.
3. 정렬 순서: 다음을 클릭하십시오.
4. 마침: 마침을 클릭합니다.
6. 데이터 가져오기 창이 나타납니다.
1. 속성...을 클릭합니다.단추를 채우다
1. 정의 탭을 선택합니다.
2. 명령 텍스트: 섹션에 Excel 매개 변수가 포함된 WHERE 절을 추가합니다.지금 원하는 모든 매개 변수를 추가하는 것이 중요합니다.예를 들어 두 개의 매개 변수가 필요한 경우 다음을 추가할 수 있습니다.
   1 = ? 및 2 = ?
3. 확인을 클릭하여 "데이터 가져오기" 창으로 돌아갑니다.
2. 피벗 테이블 보고서 선택
3. 확인 클릭
7. 각 매개 변수에 대한 매개 변수 값을 입력하라는 메시지가 표시됩니다.
8. 파라미터를 입력하면 피벗 테이블에 표시됩니다.
9. 데이터 탭으로 돌아가 연결 속성 단추를 클릭합니다.
1. 정의 탭을 클릭합니다.
2. 명령 텍스트: 섹션에서 이전에 정의한 것과 동일한 수의 매개 변수로 원하는 실제 SQL 조회에 붙여넣습니다.
3. 매개변수...를 클릭합니다.단추를 채우다
1. 각 파라미터의 프롬프트 값을 입력합니다.
2. 확인 클릭
4. 확인을 클릭하여 속성 창을 닫습니다.
10. 축하합니다. 이제 매개 변수가 있습니다.

간편한 해결 방법(VBA 필요 없음)

1. 표를 마우스 오른쪽 단추로 클릭하고 "표" 컨텍스트 메뉴를 확장한 후 "외부 데이터 속성"을 선택합니다.
2. "연결 속성"을 클릭합니다(도구 설명에만 레이블이 표시됨)
3. 이동 탭 "정의"

여기서 매개 변수를 원하는 위치에 '?'를 추가하여 SQL을 직접 편집합니다.잔소리를 안 듣는 것만 빼면 예전과 똑같이 작동합니다.

쿼리 2007 VBA를 할 수 .텍스트 속성.단순히 추가하는 경우?매개 변수를 원하는 위치에서 다음에 데이터를 새로 고칠 때 connections! magic의 값을 묻는 메시지가 표시됩니다.이제 연결 속성을 보면 매개 변수 버튼이 활성화되어 정상적으로 사용할 수 있습니다.

예를 들어 매크로를 작성하고 디버거에서 단계를 밟아 명령을 설정합니다.텍스트를 적절하게 입력합니다.이렇게 하면 매크로를 제거할 수 있습니다. 매크로는 쿼리를 업데이트하기 위한 수단일 뿐입니다.

Sub UpdateQuery
    Dim cn As WorkbookConnection
    Dim odbcCn As ODBCConnection, oledbCn As OLEDBConnection
    For Each cn In ThisWorkbook.Connections
        If cn.Type = xlConnectionTypeODBC Then
            Set odbcCn = cn.ODBCConnection

            ' If you do have multiple connections you would want to modify  
            ' the line below each time you run through the loop.
            odbcCn.CommandText = "select blah from someTable where blah like ?"

        ElseIf cn.Type = xlConnectionTypeOLEDB Then
            Set oledbCn = cn.OLEDBConnection
            oledbCn.CommandText = "select blah from someTable where blah like ?" 
        End If
    Next
End Sub

Excel 2013의 경우 쿼리 텍스트에 "?"와 같은 매개 변수가 포함되어 있더라도 연결 대화 상자에서 "파라미터" 버튼은 사용할 수 없습니다.

다음과 같이 쿼리 텍스트에 매개 변수를 삽입합니다.

declare @sd datetime, @ed datetime
set @sd = '2022-01-01' 
set @ed = '2022-01-31' 

select * 
from dbo.Table1 
where date between @sd and @ed

VBA에 다음을 추가합니다.

Public SQLParams As New Dictionary 'Requred Reference "Microsoft Scripting Runtime"

Sub Button1_Click()
  SQLParams("sd") = "'2022-02-01'"
  SQLParams("ed") = "'2022-02-28'"
      
  UpdateQuery SQLParams
End Sub

'Update params in all Query
Sub UpdateQuery(ByRef SQLParams As Dictionary)
  Dim cn As WorkbookConnection
  Dim odbcCn As ODBCConnection, oledbCn As OLEDBConnection
    
  For Each cn In ThisWorkbook.Connections
    If cn.Type = xlConnectionTypeODBC Then
      Set odbcCn = cn.ODBCConnection
      odbcCn.CommandText = SetParamValues(odbcCn.CommandText, SQLParams)
      odbcCn.Refresh
    ElseIf cn.Type = xlConnectionTypeOLEDB Then
      Set oledbCn = cn.OLEDBConnection
      oledbCn.CommandText = SetParamValues(oledbCn.CommandText, SQLParams)
      oledbCn.Refresh
    End If
  Next
End Sub

Function SetParamValues(SQL As String, ByRef Params As Dictionary) As String
  Dim re As New RegExp, Matches 'Requred Reference "Microsoft VBScript Regular Expressions 5.5"
  
  Dim paramName As Variant, paramValue As String
     
  SetParamValues = SQL
  
  re.IgnoreCase = True
  re.MultiLine = True
  
  For Each paramName In Params.Keys()
    re.Pattern = "(set\s+\@" + paramName + "\s*=\s*)(\'[^\']*\')"
    
    paramValue = Params(paramName)
     
    SetParamValues = re.Replace(SetParamValues, "$1" + paramValue)
  Next 'For Each paramName In Params.Keys()
End Function

예 - 해결 방법은 워크북을 XML 파일(예: 'XML 스프레드시트 2003')에 저장하고 이 파일을 메모장의 텍스트로 편집하는 것입니다!메모장의 "SEARCH" 기능을 사용하여 쿼리 텍스트를 찾고 데이터를 "?"로 변경합니다.

Excel에서 저장 및 열기, 데이터 새로 고침을 시도하면 Excel이 매개 변수에 대해 모니터링됩니다.

언급URL : https://stackoverflow.com/questions/3091908/how-to-add-parameters-to-an-external-data-query-in-excel-which-cant-be-displaye