VBA에서 전원 쿼리를 자동화하는 방법

VBA에서 전원 쿼리를 자동화하는 방법

1번 시트에 데이터가 있습니다.보통 전원 쿼리로 이동하여 변환을 수행한 후 닫고 기존 시트 2에 로드합니다.

VBA를 사용하여 자동화하고 싶습니다.VBA에서는 전원 쿼리를 자동으로 실행하여 변환을 시트 2에 채울 수 있습니다.

매크로 레코더에서는 스텝을 기록할 수 없는 것 같습니다.그리고 이것을 하는 것에 대한 온라인은 많지 않다.

간단한 코드 시도 중:

Sub LoadToWorksheetOnly()

'Sub LoadToWorksheetOnly(query As WorkbookQuery, currentSheet As Worksheet)
    ' The usual VBA code to create ListObject with a Query Table
    ' The interface is not new, but looks how simple is the conneciton string of Power Query:
    ' "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name
     
    query = Sheets("Sheet6").Range("A1").value 'here is where my query from power query is. I put the text from power query avanced editor in another sheet cell.
    currentSheet = ActiveSheet.Name
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _
        , Destination:=Sheets("target").Range("$A$1")).QueryTable
        .CommandType = xlCmdDefault
        .CommandText = Array("SELECT * FROM [" & query.Name & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .Refresh BackgroundQuery:=False
    End With
     
End Sub

새로운 시트에 수동으로 로드하려고 할 때의 문제는 다음과 같습니다.

VBA는 PowerQuery 자동화에 매우 적합하며 반복 작업에 특히 효율적입니다.요령은 먼저 PowerQuery에서 필요한 쿼리를 작성한 후 Advanced Editor를 사용하여 M을 캡처하는 것입니다.문서를 복사하여 워크북의 셀 또는 별도의 텍스트 파일에 저장합니다.

이 방법은 Gil Raviv에 의해 자세히 설명되어 있습니다.편의상 워크북 대신 텍스트 파일에 M을 저장하고 다음과 같이 로드합니다.

Function LoadTextFile(FullFileName As String) As String
  With CreateObject("Scripting.FileSystemObject")
    LoadTextFile = .OpenTextFile(FullFileName, 1).readall
  End With 
End Function

텍스트 파일의 장점은 엑셀로부터 독립되어 많은 워크북에서 재사용할 수 있다는 것입니다.

여기 몇 가지 M:

let
// load the reference file (variables are shown in capitals;  
// variable values are replaced with strings from the excel control workbook)
    Source = Excel.Workbook(File.Contents(PATH_AND_NAME), null, true),
    ImportSheet = Source{[Item=SHEET_NAME,Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(ImportSheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ACCOUNT", type text}})
in
    #"Changed Type"

(워크북 또는 텍스트 파일에서) VBA에 로드된 후, 예를 들어 자리 표시자 단어를 대체하거나 M 명령 이름을 사용하여 예를 들어, 예를 들어, 예를 들어, M을 VBA 내에서 편집할 수 있습니다.

    ' create the M script to read the M file that will do the import
        M_Script = LoadTextFile(M_Source)
        
    ' insert the path
        M_Script = Replace(M_Script, "PATH_AND_NAME", """" & qSource & """") 
    
    ' insert the worksheet name
        If wksName <> "" Then M_Script = Replace(M_Script, "SHEET_NAME", """" & wksName & """")
        

다음 단계는 쿼리를 로드하는 것입니다.이 작업은 Gil에 의해 다음과 같이 기술된 기술을 사용하여 수행합니다.

Dim qry As WorkbookQuery

If DoesQueryExist(qName) Then 
    ' Deleting the query 
    Set qry = ThisWorkbook.Queries(qName) 
    qry.Delete 
End If 
           
Set qry = w.queries.Add(qName, M_Script, qSource)
                      
' We check if data should be loaded to Data Model 
shouldLoadToDataModel = ThisWorkbook.Worksheets(1).Cells(13, "D") 
 
' We check if data should be loaded to worksheet 
shouldLoadToWorksheet = ThisWorkbook.Worksheets(1).Cells(13, "E") 
 
If shouldLoadToWorksheet Then 
    ' We add a new worksheet with the same name as the Power Query query 
    Set currentSheet = Sheets.Add(After:=ActiveSheet) 
    currentSheet.Name = qName 
 
    If Not shouldLoadToDataModel Then 
        ' Let's load to worksheet only 
        LoadToWorksheetOnly qry, currentSheet 
    Else 
        ' Let's load to worksheet and Data Model 
        LoadToWorksheetAndModel qry, currentSheet 
    End If 
ElseIf shouldLoadToDataModel Then 
    ' No need to load to worksheet, only Data Model 
    LoadToDataModel qry 
End If 

LoadToDataModel 함수는 다음과 같습니다.

Option Explicit
Function LoadToDataModel(w As Workbook, query As WorkbookQuery, error As Integer) As Boolean
On Error GoTo Load_Error
    ' This code loads the query to the Data Model
    w.Connections.Add2 "Query - " & query.Name, _
        "Connection to the '" & query.Name & "' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _
        , """" & query.Name & """", 6, True, False

    LoadToDataModel = True
    
Load_Exit:
    Exit Function
    
Load_Error:
    LoadToDataModel = False
    error = Err.Number
    Resume Load_Exit
End Function

LoadToWorksheetOnly의 기능(웨이백 머신 덕분에):

Sub LoadToWorksheetOnly(query As WorkbookQuery, currentSheet As Worksheet) 
    ' The usual VBA code to create ListObject with a Query Table 
    ' The interface is not new, but looks how simple is the conneciton string of Power Query: 
    ' "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name 
     
    With currentSheet.ListObjects.Add(SourceType:=0, Source:= _ 
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _ 
        , Destination:=Range("$A$1")).QueryTable 
        .CommandType = xlCmdDefault 
        .CommandText = Array("SELECT * FROM [" & query.Name & "]") 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .BackgroundQuery = True 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .PreserveColumnInfo = False 
        .Refresh BackgroundQuery:=False 
    End With 
     
End Sub 

Gil의 코드를 사용하면 데이터를 데이터 모형이나 워크시트로 가져올 수 있습니다.연산에는 두 번째 연산자가 필요하며 방법을 따를 경우 변환된 데이터가 워크시트에 표시되어야 합니다.

VBA가 아닌 빌트인 툴을 사용하여 쿼리를 설정하는 것이 더 쉬워집니다.할 수 없는 것은 아닙니다. 적어도 한 명의 응답자가 제 요점을 오해한 것 같기 때문에 데이터를 다른 곳에 복사하여 M 언어 오류를 추적하는 것보다 빌트하고 최적화한 툴을 사용하는 것이 훨씬 더 쉬울 뿐입니다.그런 다음 VBA를 통해 쿼리를 실행할 수 있습니다.

파일, 폴더, 웹, 데이터베이스의 파일 루프 등 적절한 방법으로 데이터를 로드합니다.리스트는 계속됩니다.외부 소스에서 가져올 수도 있고 내부에서 로드할 수도 있습니다.외부 소스에서 로드하는 방법에 대한 자세한 내용은 여기를 참조하십시오.

소스를 보호하고 로드하면 변환 단계를 수행할 수 있는 쿼리 편집기가 나타납니다.

요점은 UI를 사용하여 단계를 수행할 때 M 코드가 백그라운드로 작성되어 소스 형식이나 위치를 변경하지 않는 한 재사용 가능한 쿼리의 기반이 된다는 것입니다.이 경우 UI에서 적절한 데이터 소스를 쉽게 업데이트할 수 있습니다.

이 경우 단계를 수행하고 원하는 대로 조회가 있으면 닫은 후 sheet2로 로드합니다.

이 단계에서 이 설정을 처음 수행할 때 닫힘 및 로드 대상으로 시트 2를 선택합니다.

NB: 기존 시트를 선택할 때 시트 2가 이미 존재하는지 확인하고 제안된 범위 앞에서 시트 2!를 수동으로 편집할 수 있습니다.

---

이 모든 것을 코드로 계속 재생성하려고 하기 때문에 문제가 발생하고 있습니다.

하지 마십시오. UI를 사용하여 설정한 후 sheet2에 로드합니다.이후 쿼리 에디터를 열어 스텝을 편집하거나 쿼리를 새로 고쳐 기존 sheet2에 새로운/새로고침된 데이터를 로드합니다.

---

쿼리를 새로 고치는 데 사용할 수 있는 방법 중 일부는 다음과 같습니다.

「VBA/」 「Sheet2」 「VBA/」 「Sheet2」 「Sheet2」 「VBA」( 「Sheet2」):Sheet2.Calculate,ThisWorkbook.RefreshAll데이터 탭의 워크북 새로 고침 버튼을 수동으로 누릅니다(이것들은 모두 오버킬입니다).

보다 구체적인 방법:

시트 2의 쿼리 테이블에 대한 VBA:

ThisWorkbook.Worksheets("Sheet2").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False   

상기의 내용을 적절한 표 등으로 변경해 주세요.

쿼리 테이블 자체를 오른쪽 클릭하고 새로 고침을 선택합니다.

워크북 쿼리 창의 오른쪽에 있는 새로 고침 버튼을 클릭합니다(녹색 동그라미 화살표가 있는 아이콘).

---

Ken Pulls VBA 방식(나에서 편집)

Option Explicit
Public Sub UpdatePowerQueries()
    ' Macro to update my Power Query script(s)

    Dim lTest As Long, cn As WorkbookConnection
    On Error Resume Next
    For Each cn In ThisWorkbook.Connections
        lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
        If Err.Number <> 0 Then
            Err.Clear
            Exit For
        End If
        If lTest > 0 Then cn.Refresh
    Next cn
    On Error GoTo 0
End Sub

---

VBA를 통해 이 모든 작업을 수행할 필요가 없습니다.VBA를 사용하면 데이터를 소스로 처리한 PowerQuery에 액세스할 수 있는 까다로운 데이터 조작이 있을 수 있습니다.처리 루틴을 호출하고 위에 나열된 VBA 명령 방식 중 하나를 사용하는 서브루틴을 사용하면 로트 전체를 실행할 수 있습니다.방법이 더 있고 시간이 있을 때 추가하도록 하겠습니다.

---

계산:

PowerQuery 출력에 따라 계산이 달라지는 경우 다음 4가지 확실한 즉시 옵션이 있습니다.

1. 가능한 경우 PowerQuery에 이러한 계산을 추가합니다.계산된 열, 사용자 정의 함수 등을 지원합니다.
2. PowerQuery 출력을 데이터 모델에 추가하고 데이터 모델을 사용하여 계산된 필드를 포함한 계산을 수행합니다.이를 통해 시간 인텔리전스 기능에도 액세스할 수 있습니다.
3. 새로 고침 시 범위가 변경될 경우 VBA를 사용하여 시트 2의 해당 영역에 계산을 추가합니다.
4. 새로 고침 시 범위가 변경되지 않으면 수식을 무시하기만 하면 됩니다.

이 대화는 조금 늦었지만, 포뮬러 속성을 편집하여 VBA에서 Power Query를 업데이트하는 것은 상당히 쉬웠습니다.

" " " 를 합니다.let..in그럼 을 새로 고칩니다.

Dim pqFormula as String
pqFormula = "let..in"

Dim pqName as String
pqName = "<Name of the connection>"

' Update the formula for the specific power query.
ThisWorkbook.queries(pqName).Formula = pqFormula

' Refresh the connection
ThisWorkbook.Connections("Query - " & pqName).Refresh

그 접속에 놓여 있는 모든 테이블은 즉시 갱신됩니다.

안부 전해요,

언급URL : https://stackoverflow.com/questions/51386600/how-to-automate-a-power-query-in-vba