1

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()
3
  • Does it need to be done in Powershell, or would it be acceptable to have the date on a cell in Excel or the current date built directly into the query? Commented Oct 15 at 7:27
  • 1
    Maybe change your Source line to = Excel.Workbook(File.Contents("C:\TEST\PATH\FILE " & Date.ToText(Date.From(DateTime.LocalNow()),"yyyy-MM-dd") & ".xlsx"), null, true) for todays date, or = Excel.Workbook(File.Contents("C:\TEST\PATH\FILE " & Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),-1),"yyyy-MM-dd") & ".xlsx"), null, true) for yesterdays date (change the -1 to change the date). Commented Oct 15 at 10:30
  • 1
    @DarrenBartrup-Cook - Lovely! Please post as an answer, so I can mark as answered and give you the credit you deserve! Commented Oct 15 at 16:06

1 Answer 1

2

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

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.