How to build path and file name for excel sheet from SMB share-CodePudding

I have below share and folders. For every month there will be a folder and there will be year folder prior to that as shown

Last Friday of every month there is excel sheet generated as shown below

End piece in file name "-14-26-48" remain same but prior pieces changes as per year and date. I would like to get this excel sheet path and name build in PowerShell. So how can i code so i get following path without manually entering complete path and filename. whenever i run new code i should get path and file name for last sheet which was generated last Friday of last month.

$oldbk = Import-Excel -Path '\\hcohesity05\cohesity_reports\2022\7\07-29\Cohesity_FETB_Report-2022-07-29-14-26-48.xlsx'

CodePudding user response：

If you just want the file from the last Friday of the previous month the easiest way to find it is not to build the path, but just search a month's folders and filter for files created on a Friday, sort by time created, and get the first one (once sorted the last folder created will be the first item).

$LastMonth = [datetime]::Now.AddMonths(-1).ToString('yyyy\\M')
$TargetFolder = Get-ChildItem (Join-Path '\\hcohesity05\cohesity_reports' $LastMonth) |Where{$_.CreationTime.DayOfWeek -eq 'Friday'}|Sort CreationTime|Select -first 1
$FilePath = (Resolve-Path (Join-Path $TargetFolder.FullName 'Cohesity_FETB_Report-*-14-26-48.xlsx')).Path
$oldbk = Import-Excel -Path $FilePath

CodePudding user response：

You can use the [DateTime] type to help you out here.

# To do this, we create a new datetime, 
# (using the current month and year, but the first day).
$endOfMonth = [datetime]::new(
    [DateTime]::Now.Year,
    [DateTime]::Now.Month,
    1
).AddMonths(1).AddDays(-1)  
# Then we add a month to it and subtract a day
# (thus giving us the last day of the month)

# Now we create a variable for the last Friday
$lastFriday = $endOfMonth
# and we keep moving backward thru the calendar until it's right.
while ($lastFriday.DayOfWeek -ne 'Friday') {
   $lastFriday = $lastFriday.AddDays(-1) 
}

# At this point, we can make the ReportPath, piece by piece
# ($BasePath would be the path until this point)

$ReportPrefix = "Cohesity_FETB_Report"
$ReportSuffix = "14-26-48.xlsx"

$ReportPath = Join-Path $BasePath $lastFriday.Year |
    Join-Path -ChildPath $lastFriday.Month |
    # A Custom format string will be needed for the month/day
    Join-Path -ChildPath $lastFriday.ToString("MM-dd") | 
    Join-Path -ChildPath "$reportPrefix-$($lastFriday.ToString('YYYY-MM-dd'))-$reportSuffix"

$reportPath