1

I have an excel file with 4 sheets. Each sheet contains a pivot table that connects to an Oracle database. I need all pivot tables to update automatically at 4 am. To do this I have a macro. The problem is the macro asks me to select a data source manually. Is there a way the macro select the data source automatically?

When I execute the macro, it shows me this window 4 times:

enter image description here

And then:

enter image description here

I need the macro select the one that is marked (PRODUCCION DWH).

This is the macro:

Sub Actualiza_Reporte()

    Dim pt As PivotTable    
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables      
            pt.RefreshTable

        Next pt
    Next ws

    ThisWorkbook.Save   ' Guardamos el archivo
    ThisWorkbook.Close  ' Para cerrar el archivo
    Application.Quit    ' Para cerrar Excel
End Sub

I need to update the 4 sheets automatically, without asking me to select the data source.

Thanks and regards.

Miles Fett
  • 711
  • 4
  • 17
Ricardo Carrera
  • 113
  • 2
  • 10

1 Answers1

0

Besides using macros, you could also do it with power query which is included in Excel 2016 and higher by default:

enter image description here

Select Data, then New Query, then Databases, then Oracle and create a connection to Oracle.

Then you only need to set the refreshing intervall like this:

enter image description here

Click Data again, then Connections, then Settings, then Update after X minutes.

If you want control the exact time of the oracle power query update, just configure this macro:

Sub RefreshQuery()
Dim con As WorkbookConnection
Dim Cname As String

For Each con In ActiveWorkbook.Connections
    If Left(con.name, 8) = "Query - " Then
    Cname = con.name
        With ActiveWorkbook.Connections(Cname).OLEDBConnection
            .BackgroundQuery = False  'or true, up to you
            .Refresh
        End With
    End If
Next
End Sub

Code found here: https://stackoverflow.com/a/38653892/11971785

Andreas
  • 8,694
  • 3
  • 14
  • 38
  • I have Microsoft Excel 2013. Is there a way using only macro? I'm not an excel expert and it would be fine if the macro select the data source automatically. – Ricardo Carrera Sep 04 '19 at 21:26
  • @RicardoCarrera, try first to simply connect to oracle. The following link might be helpful: https://community.oracle.com/thread/679324 once you have established a connect, we can work on it. – Andreas Sep 04 '19 at 21:40
  • I don't need to connect to Oracle in the macro. If I press Accept and OK in the windows that pops up (image from the question), it will refresh the data. But what I need is the macro select the data source PRODUCCION DWH without ask. – Ricardo Carrera Sep 04 '19 at 22:23
  • @RicardoCarrera, yes, because Excel "does not know" where the source is. To make it know where to find the connection one way would to first create a direct connection via VBA or via Power Query as mentioned in my answer. If you dont want to connect via VBA you can download power query as Add-Inn for Excel 2013, but as far as I know you need to specify the connection somewhere, otherwise Excel will always ask for it. – Andreas Sep 04 '19 at 22:27