locked
Macro written in VBA to Update PowerPivot Connection with newest .txt in folder RRS feed

  • Question

  • Hello,

    I have a .txt report that needs to be imported into PivotTables in an Excel file each day. There are Pivot tables connected to the PowerPivot data connection.  I know to to manually update the data connection using the "Data Source Settings" tab in Power Pivot.

    I need VBA code that will automatically select and import the newest .txt file from a specific folder.

    I have tried recording a macro to do this and it did not work. Below is the VBA code that was the result of coding the macro:

    Sub Changing_Data_Source()
    '
    ' Changing_Data_Source Macro
    '

    '
        Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
        Columns("A:A").Select
        ActiveWorkbook.RefreshAll
    End Sub

    Thank you for your help

    Wednesday, March 4, 2020 4:27 PM