locked
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.ClearSheet()
    	$excelDoc.visible = $true
    	$excelDoc.workbooks.Add()
    	#$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
    $doc.SaveAs($filename)
    $doc.Close()
    

    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