Saltar al contenido

¿Cómo automatizar una consulta de energía en VBA?

Solución:

No estoy de acuerdo con la premisa de la respuesta anterior; VBA es absolutamente adecuado para automatizar PowerQuery y es particularmente eficiente para trabajos repetitivos. El truco consiste en crear primero la consulta que necesita en PowerQuery, luego usar el Editor avanzado para capturar el M. Cópielo y guárdelo, ya sea en una celda del libro de trabajo o en un archivo de texto separado.

Gil Raviv describe en detalle el método. Para mayor comodidad, guardo mi M en archivos de texto en lugar del libro de trabajo y lo cargo con:

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

Lo bueno de los archivos de texto es que son independientes de Excel y pueden ser reutilizados por muchos libros de trabajo.

Aquí hay algo de 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"

Una vez cargado en VBA (ya sea desde el libro de trabajo o el archivo de texto), la M se puede editar dentro de VBA, por ejemplo, sustituyendo palabras de marcador de posición, o usando los nombres de los comandos M para ubicar y alterar líneas si es necesario, p.

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

El siguiente paso es cargar la consulta. Hago esto usando la técnica descrita por Gil de la siguiente manera:

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 

El código de Gil permite importar datos al modelo de datos o a una hoja de trabajo. El OP requiere el segundo, y si se sigue el método, los datos transformados deberían aparecer en la hoja de trabajo.

WIP:

Entonces, ¿cómo escribir esto para que sea suficiente? La conclusión es que debe configurar su consulta utilizando las herramientas integradas, no VBA. Carga sus datos a través del método apropiado, que puede ser desde un archivo, repitiendo archivos en una carpeta, web, base de datos … la lista continúa. Puede importar desde fuentes externas así como cargar desde internas. Eche un vistazo aquí para obtener más información sobre la carga desde fuentes externas.

Una vez que haya asegurado su fuente y esté cargada, se le presentará el editor de consultas donde puede realizar sus pasos de transformación.

El punto es que a medida que realiza sus pasos utilizando la interfaz de usuario, el código M se escribe en segundo plano y forma la base de una consulta reutilizable siempre que no cambie el formato de origen o la ubicación.

En su caso, cuando haya realizado sus pasos y tenga una consulta como desee, cierre y cargue en sheet2.

En este paso, la primera vez que lo configure, seleccionará la hoja 2 como su destino de cierre y carga:

Cerrar y cargar

NB: Cuando seleccione la hoja existente, asegúrese de que la Hoja 2 ya exista y de que puede editar la Hoja2 manualmente. delante del rango sugerido.


Estás experimentando problemas porque sigues intentando recrear todo esto con código.

No lo hagas. Configúrelo usando la interfaz de usuario y cárguelo en sheet2. A partir de ese momento, abra el editor de consultas para editar los pasos y / o actualice la consulta para cargar la hoja existente2 con datos nuevos / actualizados.


Algunos de los métodos disponibles para actualizar su consulta:

La consulta se actualizará mediante VBA / Actualizaciones manuales en la hoja en la que reside (Hoja2) o en el libro de trabajo en sí, por ejemplo Sheet2.Calculate , ThisWorkbook.RefreshAll, presionando manualmente el botón actualizar libro de trabajo en la pestaña de datos (todos estos son realmente exagerados)

Actualizar todas las pestañas

Métodos más específicos:

VBA para la tabla de consulta en la hoja 2:

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

Cambie lo anterior a la tabla apropiada, etc.

Haga clic derecho en la propia tabla de consultas y seleccione actualizar:

Actualizar

Haga clic en el botón actualizar en la ventana de consultas del libro de trabajo en el lado derecho de la consulta en cuestión (icono con flechas circulares verdes)

Actualizar


The Ken Pulls VBA way (edición menor de mí)

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

No debería haber ninguna necesidad real de que hagas todo este trabajo a través de VBA. Es posible que tenga una manipulación de datos complicada con la que se sienta más cómodo con VBA y luego tenga acceso a PowerQuery que procesó los datos como fuente. Puede disparar todo al tener una subrutina que llame a la rutina de procesamiento y luego use uno de los métodos de comando de VBA enumerados anteriormente. Hay más métodos y los agregaré cuando tenga más tiempo.


Cálculos:

Si tiene cálculos que dependen de la salida de PowerQuery, tiene 4 opciones obvias inmediatas:

  1. Agregue estos cálculos cuando sea posible en PowerQuery. Admite columnas calculadas, funciones definidas por el usuario y mucho más.
  2. Agregue la salida de PowerQuery al modelo de datos y use el modelo de datos para realizar cálculos, incluidos los campos calculados. Esto también le dará acceso a funciones de inteligencia de tiempo.
  3. Use VBA para agregar los cálculos a las áreas apropiadas en la hoja 2 si el rango cambia al actualizar
  4. Si el rango no cambia en la actualización, simplemente elimine las fórmulas.

Un poco tarde para esta conversación, pero me resultó bastante fácil actualizar una Power Query en VBA editando la propiedad Formula.

Primero actualice el let..in fórmula. Luego actualice las conexiones que lo usan

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

Todas las tablas que descansan en esa conexión deben actualizarse de inmediato.

Saludos,

¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)



Utiliza Nuestro Buscador

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *