How to open dynamically-named Excel file with PS RRS feed

  • Question

  • I have a script that gathers PC information, then (is supposed to) displays the info it gathered in an Excel spreadsheet, but I can't get the script to open the file. I've tried hardcoding the filename, but the best I get is an empty Excel window to open. I have not dealt with Excel and PS, so I could use some help. Here is what I have so far:

    function Show-RefreshData ($refreshdata)
    	$schemaFinal = create-schemaFinal $refreshdata
    	$date = Get-Date -Format MM.dd.yyy.hh.mm.ss
    	#$filename = ".\Output\RefreshData_Test.xlsx"
    	$refreshdata | select-object $schemaFinal | Export-XLSX -Path ".\Output\RefreshData_$(Get-Date -Format dd.mm.yy.hh.mm.ss).xlsx"
    	$excelDoc = New-Object -ComObject excel.application
    	$excelDoc.visible = $true
    	#$workbook = $excelDoc.Workbooks.Open($filename)

    The last line is commented out because I'm not sure how to reference the same file created with the Get-Date cmdlet. I know I'm close. I just need a little hint. The data gets pulled from a SQLite db, btw.

    Thursday, September 7, 2017 3:06 PM

All replies

  • Here is how to create a new XL document.

    $xl = New-Object -ComObject excel.application
    $xl.visible = $true
    $doc = $xl.workbooks.Add()
    # work with new XL document

    For more expert assistance post in Excel developer forum to learn how to program Excel.


    Thursday, September 7, 2017 3:59 PM
  • Thanks for the info - I'll try that. My other related question is - How can I open a file (Excel in this example) when the filename is dynamically generated, as in Get-Date as part of the filename? My script creates an Excel doc and names it based on the Get-Date cmdlet, and I want to be able to open that file and display it for the user.
    Friday, September 8, 2017 2:09 PM
  • Unfortunately Excel and PowerShell have not yet been given telepathic capabilities. Perhaps you could make your file name clear by saving it somewhere and referring to it.


    Friday, September 8, 2017 2:12 PM
  • That's what I'm trying to do, but I haven't been able to save the Get-Date value. It just keeps saying it can't find the file, and I'm sure it's because the date/time changes every time the Get-Date gets called. I guess I could create a temp file, then open that one. The point is so that the user doesn't have to manually open the file to make edits.
    Friday, September 8, 2017 2:51 PM
  • [datetime]::Today.ToString('yyyy-MM-dd')

    Will not change all day.


    Friday, September 8, 2017 3:07 PM