excelpowershellpowerquery

Changing Data Query Source File Property

I am attempting to change the Source property of a query in an Excel file via PowerShell. I have found the Connections properties, but am unable to locate the specific property that contains the Source reference to the filename.

Example of the current configuration: current filename information

We want the filename to be able to be respective of the current datetime, so there's a need to change the File.Contents FQPN on a daily basis. I am familiar with replacing a property's data, I just can't find the location where this is stored.

I have also checked the properties within QueryTable on the sheets as well as the properties of Connections in the Workbook, but I am still missing where this Source is available to modify.

Small sample, wherein the missing section I am not clear about is marked as # comments, prior to refreshing all data sources.

Add-Type -ASSEMBLY "Microsoft.Office.Interop.Excel" | Out-Null
$xlObject = New-Object -ComObject Excel.Application
$fileOpen = 'C:\TEST\PATH\DATAREFRESHFILE.XLSX'
$wbObject = $xlObject.Workbooks.Open($fileOpen)
$shDataSh = $wbObject.Worksheets.Item('Data')

#here we would locate the aforementioned string and adjust the 'Source' filename prior to doing a refresh

$wbObject.RefreshAll()
Solution

  • Rather than change the source of the query from the outside you can write the M code so it uses the current date, or a date relative to the current date.

    This will use the current date:

    = Excel.Workbook(File.Contents("C:\TEST\PATH\FILE " & Date.ToText(Date.From(DateTime.LocalNow()),"yyyy-MM-dd") & ".xlsx"), null, true)

    This will look at yesterdays date:

    = Excel.Workbook(File.Contents("C:\TEST\PATH\FILE " & Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),-1),"yyyy-MM-dd") & ".xlsx"), null, true)

    Some further reading:
    https://learn.microsoft.com/en-us/powerquery-m/date-adddays
    https://learn.microsoft.com/en-us/powerquery-m/date-totext
    https://learn.microsoft.com/en-us/powerquery-m/date-from
    https://learn.microsoft.com/en-us/powerquery-m/datetime-localnow

    https://learn.microsoft.com/en-us/powerquery-m/datetime-functions