{"id":1763,"date":"2020-08-05T15:45:48","date_gmt":"2020-08-05T23:45:48","guid":{"rendered":"http:\/\/www.auval.com.mx\/notas\/?p=1763"},"modified":"2022-04-05T01:22:24","modified_gmt":"2022-04-05T01:22:24","slug":"macro-para-actualizar-refrescar-power-query-y-power-pivot-en-excel","status":"publish","type":"post","link":"https:\/\/auval.com.mx\/blog\/2020\/08\/05\/macro-para-actualizar-refrescar-power-query-y-power-pivot-en-excel\/","title":{"rendered":"Macro para actualizar (refrescar) Power Query y Power Pivot en Excel."},"content":{"rendered":"\n<p>Aqu\u00ed les dejo este c\u00f3digo que sirve para actualizar Power Query y Power Pivot ordenadamente en Excel. Funciona con consultas (queries) hechas en ingl\u00e9s o en espa\u00f1ol.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Option Explicit\n' Tomado de https:\/\/stackoverflow.com\/questions\/36902975\/auto-updating-power-query-connection-via-vba\nSub Actualizar()\n    Dim Conn As WorkbookConnection\n    Dim Cname As String\n    \n    ' Actualiza las queries.\n    For Each Conn In ActiveWorkbook.Connections\n        If Left(Conn.Name, 8) = \"Query - \" Or Left(Conn.Name, 8) = \"Consulta\" Then\n            Cname = Conn.Name\n            With ActiveWorkbook.Connections(Cname).OLEDBConnection\n                .BackgroundQuery = False\n                .Refresh\n            End With\n        End If\n    Next\n    \n    ' Actualiza el modelo de datos (Power Pivot)\n    ThisWorkbook.Model.Refresh\n    \n    MsgBox \"Listo\"\nEnd Sub\n<\/code><\/pre>\n\n\n\n<p>Y de pil\u00f3n, aqu\u00ed est\u00e1 c\u00f3mo pedir un nombre de archivo en Excel.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub AbrirArchivo()\n' Empiezan por definir el rango RutaAlArchivo. Puede ser Hoja1!A2 o darle un nombre a esa celda.\n    Dim Ruta\n    Ruta = Application.GetOpenFilename(\"Archivos de Excel (<em>.xls<\/em>), <em>.xls<\/em>\", , \"Archivo de algo\")\n    If Not (Ruta = False) Then\n        Range(\"Hoja1!A2\") = Ruta\n    End If\nEnd Sub<\/pre>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Aqu\u00ed les dejo este c\u00f3digo que sirve para actualizar Power Query y Power Pivot ordenadamente en Excel. Funciona con consultas (queries) hechas en ingl\u00e9s o en espa\u00f1ol. Y de pil\u00f3n, aqu\u00ed est\u00e1 c\u00f3mo pedir un nombre de archivo en Excel. &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"more-link\" href=\"https:\/\/auval.com.mx\/blog\/2020\/08\/05\/macro-para-actualizar-refrescar-power-query-y-power-pivot-en-excel\/\"> <span class=\"screen-reader-text\">Macro para actualizar (refrescar) Power Query y Power Pivot en Excel.<\/span> Leer m\u00e1s \u00bb<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1763","post","type-post","status-publish","format-standard","hentry","category-office"],"_links":{"self":[{"href":"https:\/\/auval.com.mx\/blog\/wp-json\/wp\/v2\/posts\/1763","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/auval.com.mx\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/auval.com.mx\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/auval.com.mx\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/auval.com.mx\/blog\/wp-json\/wp\/v2\/comments?post=1763"}],"version-history":[{"count":1,"href":"https:\/\/auval.com.mx\/blog\/wp-json\/wp\/v2\/posts\/1763\/revisions"}],"predecessor-version":[{"id":1798,"href":"https:\/\/auval.com.mx\/blog\/wp-json\/wp\/v2\/posts\/1763\/revisions\/1798"}],"wp:attachment":[{"href":"https:\/\/auval.com.mx\/blog\/wp-json\/wp\/v2\/media?parent=1763"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/auval.com.mx\/blog\/wp-json\/wp\/v2\/categories?post=1763"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/auval.com.mx\/blog\/wp-json\/wp\/v2\/tags?post=1763"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}