Ramon TAN

Forum Replies Created

Viewing 15 posts - 1 through 15 (of 138 total)
  • Author
    Posts
  • Participant
    Topics: 86
    Replies: 138
    Points: 676
    Rank: Major Contributor

    Thank you very much Mr. AndySvints.  I tried several fixes, e.g.:

    (1)  Export-Excel (details skipped) … -TitleBackgroundColor ‘10092543’

    (2) Export-Excel (details skipped) ,,,, -TitlebackgroundColor $t

    where  [System.Drawing.Color]$t = ‘10092543’

    and a few others miore or less in the same vein (out of desperation), and they all failed.  It seems that even a [string] type is not considered valid by Export-Excel in PS 7.  But in PS 5.1 just

    Export-Excel  .... -TitleBackgroundColor 10092543
    
    

    works fine.

     

     

    Participant
    Topics: 86
    Replies: 138
    Points: 676
    Rank: Major Contributor

    Hello Mr. Maurer,

    Many thanks for your quick response.  Yes, I installed that version of ImportExcel.

     

    Participant
    Topics: 86
    Replies: 138
    Points: 676
    Rank: Major Contributor

    Hello AndySvints,

    Many thanks for your response.  I have Office 2019 Home & Student ed. on my laptop.  No I do not have any other Office versions installed.  However, I have VISIO 2007 installed and with it Office 2007 SP3.  This could not be the reason my PS 5.1 script is failing because the VISIO and the Office 2007 SP3 were installed more than a 2 months ago, and the script was still working after that installation.  For what it’s worth, I  checked the EXCEL assembly (which I was told by another blog / source) may be the reason:

    GAC Version Location
    ---     -------    --------
    False v2.0.50727  C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Micr…
    
    

    Thanks for the Stack OV reference.  I hope it leads me to the root cause.
     

     

    Participant
    Topics: 86
    Replies: 138
    Points: 676
    Rank: Major Contributor

    Thanks Mr Maurer.

    (1) Range() fails when I put in the ($row,$column).Value line of code — the error message suggests I am performing an invalid operation on a ParametrizedProperty.

    (2) The following link suggests using the Add-Type -AssemblyName approach:

    https://stackoverflow.com/questions/59045043/powershell-6-doesnt-find-excel-interop-powershell-5-1-does

    I tried it various times, first with -AssemblyName, then with -Path and -LiteralPath, but it didn;t work.

    In any case, my “solution” (if it can be called one!) is to just stay with PS 5.1 since it works there.

    Thanks again for your tips and guidance.

     

    Participant
    Topics: 86
    Replies: 138
    Points: 676
    Rank: Major Contributor

    Dear Mr Maurer,

    Many thanks for your responses.  I am right now overseas (and will be for a while due to Covid19) and the Internet and telephone infrastructure is very poor.  I saw your messages briefly by email and couldn’t even scroll down to read the rest.  I’ve attempted half a dozen Logins in the last 48 hours and always ended up failing in various degrees.  Now (12 AM, Manila time, April22) I finally got in safely and haven’t been cut off yet.  SO I am quickly printed all your messages before I get cut off.  I will revert with feedback after I try all your suggestions.  My sincerest gratitude for your inputs and kind consideration.

     

    Participant
    Topics: 86
    Replies: 138
    Points: 676
    Rank: Major Contributor

    Hello Mr Maurer,

    I have a stripped down version of the script that you asked for, in order that your efforts are not wasted.

    I fully understand your most recent reply, and know fully well that in PS7 which uses .Net Core, my stated problem cannot be solved, if only because PS 5.1 returns a COM Object Type different from the one returned under PS 7.

    As a layman/novice I cannot understand why Microsoft would want to issue a new Powershell that is “less than” the functionality of an older version.  I am sure there will be profound explanations in the various blogs out there, if I’m lucky enough to find them. I am clearly missing something fundamental.

    The script that reproduces that same error is shown below. The script reads an EXCEL template file (.xlsx). I cannot find a way to attach this file so my reply/post. It seems the PS Forum does not have such a feature.

    #
    # Sample script involving EXCEL ComObject in PS 7.
    #
    # ------------------------------------------------------------
    # Location of Excel template file
    #
    $InpExcel = 'C:\Users\xxt35\Documents\Excel_Template.xlsx'
    
    # ------------------------------------------------------------
    # Preparations for Excel output file
    # (Worksheet is password protected, pw='abc')
    #
    $Excel = New-Object -Comobject Excel.Application
    $Excel.Visible = $True
    $Excel.DisplayAlerts = $False
    $book = $Excel.Workbooks.Open($InpExcel)
    $sheet = $book.Worksheets.Item('PPWKS')
    $sheet.Unprotect('abc')
    
    # -------------------------------------------------
    #    Blank out target data rows & columns
    #
    $sheet.Range("B3:B3").ClearContents | Out-Null
    $sheet.Range("B4:B4").ClearContents | Out-Null
    $sheet.Range("B6:B10").ClearContents | Out-Null
    $sheet.Range("C6:C10").ClearContents | Out-Null
    $sheet.Range("D6:D10").ClearContents | Out-Null
    $sheet.Range("E6:E10").ClearContents | Out-Null
    
    # -------------------------------------------------
    #   Data to be inserted into Excel file
    #
    $List_of_Employees = @{"John"=10; "Bob"=20; "Tom"=30; "Roy"=40}
    
    # -------------------------------------------------
    #   Insert the data into the Excel template file
    #   One employee per row, starting in Row 6.
    #
    $row = 6
    foreach ($employee in $List_of_Employees.Keys) {
        $sheet.Cells($row,2).Value = $employee
        $sheet.Cells($row,4).Value = $List_of_Employees.Item($employee)
        $row++
    }
    
    # -------------------------------------------------
    # Save result to a new output file
    
    $SaveTo = 'C:\Users\xxt35\Documents\Excel_WithData.xlsx'
    If (Test-path -path $SaveTo) {Remove-item $SaveTo -force}
    $sheet.Protect('abc')
    $sheet.SaveAs($SaveTo,51)
    $Excel.Workbooks.Close()
    $Excel.Quit()
    
    

     

    • This reply was modified 2 months, 3 weeks ago by Ramon TAN.
    Participant
    Topics: 86
    Replies: 138
    Points: 676
    Rank: Major Contributor

    I have simplified the original script to its “bare essentials” so your efforts will not be wasted. Insofar as the error message I am getting is concerned, the following stripped down version works in PS 5.1 but fails on PS 7.  I fully understand your most recent explanation, there is no way this script can work or be made to work in PS 7 because the Object Type returned in PS5.1 is not the same as that in PS7.  I suppose this is due to .Net Core in PS7.

    There is an Excel template file that is part of the script that the script reads in.  I cannot see a mechanism in this forum to attach a file.  Thanking you for your assistance and kind consideration.  The code (about 55 lines) :

    <#
    Sample script involving EXCEL ComObject in PS 7.
    #>
    # ------------------------------------------------------------
    # Location of Excel template file
    #
    $InpExcel = 'C:\Users\xxt35\Documents\Excel_Template.xlsx'
    
    # ------------------------------------------------------------
    # Preparations for Excel output file
    # (Worksheet is password protected, pw='abc')
    #
    $Excel = New-Object -Comobject Excel.Application
    $Excel.Visible = $True
    $Excel.DisplayAlerts = $False
    $book = $Excel.Workbooks.Open($InpExcel)
    $sheet = $book.Worksheets.Item('PPWKS')
    $sheet.Unprotect('abc')
    
    # -------------------------------------------------
    # Blank out target data rows & columns
    #
    $sheet.Range("B3:B3").ClearContents | Out-Null
    $sheet.Range("B4:B4").ClearContents | Out-Null
    $sheet.Range("B6:B10").ClearContents | Out-Null
    $sheet.Range("C6:C10").ClearContents | Out-Null
    $sheet.Range("D6:D10").ClearContents | Out-Null
    $sheet.Range("E6:E10").ClearContents | Out-Null
    
    # -------------------------------------------------
    # Data to be inserted into Excel file
    #
    $List_of_Employees = @{"John"=10; "Bob"=20; "Tom"=30; "Roy"=40}
    
    # -------------------------------------------------
    # Insert the data into the Excel template file
    # One employee per row, starting in Row 6.
    #
    $row = 6
    foreach ($employee in $List_of_Employees.Keys) {
        $sheet.Cells($row,2).Value = $employee
        $sheet.Cells($row,4).Value = $List_of_Employees.Item($employee)
        $row++
    }
    
    # -------------------------------------------------
    # Save result to a new output file
    
    $SaveTo = 'C:\Users\xxt35\Documents\Excel_WithData.xlsx'
    If (Test-path -path $SaveTo) {Remove-item $SaveTo -force}
    $sheet.Protect('abc')
    $sheet.SaveAs($SaveTo,51)
    $Excel.Workbooks.Close()
    $Excel.Quit()
    
    

     

    • This reply was modified 2 months, 3 weeks ago by Ramon TAN.
    Participant
    Topics: 86
    Replies: 138
    Points: 676
    Rank: Major Contributor

    Hello Mr Maurer,

    I executed the following command:

    PS C:\Users\MYacct\Documents > import-module importexcel -UseWindowsPowershell | install-module
    

    and got a warning, which did not lead to Install-module execution:

    WARNING: Module importexcel is loaded in Windows PowerShell using WinPSCompatSession remoting session;
    please note that all input and output of commands from this module will be deserialized objects.
    If you want to load this module into PowerShell Core please use 'Import-Module -SkipEditionCheck' syntax.
    

    Rather than do it right away as suggested in the warning:  i.e., run with the ‘-SkipEditionCheck’ parameter, I thought it safer to check with you before proceeding.  Would appreciate your comments and guidance.

    Many thanks.

    Participant
    Topics: 86
    Replies: 138
    Points: 676
    Rank: Major Contributor

    Thank you Mr Maurer. much appreciated.

    Participant
    Topics: 86
    Replies: 138
    Points: 676
    Rank: Major Contributor

    Yes, on PS 7, the same error message comes up whether running as Admin or as non-Admin.

    In PS 5.1, the error never occurs whether running as Administrator or not.

    Thanks again for your attention and kind consideration.

    Participant
    Topics: 86
    Replies: 138
    Points: 676
    Rank: Major Contributor

    Yes, both were run as Administrator. (I have gotten into the habit of always running as Administrator, but before I sent this reply, I re-ran both again, as Administrator, just to be sure).

    Much thanks for your response.

    Participant
    Topics: 86
    Replies: 138
    Points: 676
    Rank: Major Contributor

    Many thanks Mr Maurer, for your answer/solution with thought-provoking examples. While I was struggling with the GetType() within Get-member, I came up with the ff:

    b$ = "a string"
    b$ | Get-member | Out-string -Stream | Select-string "Typename:" -Noemphasis
    

    Obviously, my mindset was set on strings rather than objects.

    Thanks again for your response.

    in reply to: Missing EXCEL [Enum]s in PS7 #212175
    Participant
    Topics: 86
    Replies: 138
    Points: 676
    Rank: Major Contributor

    Yes, I cut-and-pasted several times, but it seems I missed the “tail end” portion … anyway, finally did it the primitive way: retyped the whole thing (!) … I am testing my screen with respect to Powershell.org webesit … no, I did not zoom, but will try out you suggestions first … Much thanks again for all the help.

    in reply to: Missing EXCEL [Enum]s in PS7 #212127
    Participant
    Topics: 86
    Replies: 138
    Points: 676
    Rank: Major Contributor

    Thanks very much Olaf — it now works!  Just typing that long string was a struggle … Also, I am suddenly “losing” the last few characters when reading Powershell.org posts and replies.  My laptop (15″) screen is already maximized.  Thanks Stuart!

    in reply to: Missing EXCEL [Enum]s in PS7 #212112
    Participant
    Topics: 86
    Replies: 138
    Points: 676
    Rank: Major Contributor

    Thank you Mr Stuart Squibb for your response.  I tried the line just before the Enums and got the message:

    Resource Unavailable: Program 'Microsoft.Office.Interop.Excel.dll' failed to run.
    No application is associated with the specified file for this operation. At line: 1 char: 1
    +C:\WINDOWS\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0
    
    

    My PC is running Windows 10 64-bit, and Powershell 7 (1.43.1), free edition.  I also have Office 2019 which has EXCEL application with it.  Would be grateful for any tips/guidance.  Thanks.

Viewing 15 posts - 1 through 15 (of 138 total)