›

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Quick Links

Categories

Script Error

0neinthisworld0neinthisworld
edited November 1 in External Data Files
Hello all,
I have a problem which I'm unable to resolve. My PS was working fine until I installed a new hard drive and a fresh copy of Win10. The working copy of my PS files were fine on my prior Win10 instance, but I encountered errors when I ran the scripts on the refreshed OS.

After trying to troubleshoot it, I decided to start from scratch by downloading the PS zip file and updating the Excel and psconfig files as per usual. But I also encountered the same errors when I ran the scripts. I'm fairly confident that there isn't anything different, but obviously the script is not liking the change.
I'm reaching here to get some help as it's beyond my comprehension and hoping someone can advise me what steps I should take.


edit: The error message is too long for this post, so the other parts are copied into the following posts. I hope that's okay.

C:\PortfolioSlicer\PSDataExtract>powershell -ExecutionPolicy Bypass .\Scripts\GetExcelSrcData.ps1
Exception calling "Open" with "0" argument(s): "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local
machine."
At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:25 char:1
+ $connection.open(); $cmdObject.Connection = $connection; $dr = $cmdOb ...
+ ~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException

Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The
connection's current state is closed."
At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:25 char:58
+ ... ct.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datar ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException

You cannot call a method on a null-valued expression.
At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:27 char:5
+ if (!$dr.Read()) {Write-Host "No config record found" -ForegroundColo ...
+ ~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:28 char:1
+ $f += $dr.item("MinDate").ToString("yyyy-MM-dd").Replace($colSep," ") ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:30 char:1
+ $dr.Close(); $connection.close();
+ ~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Exception calling "Open" with "0" argument(s): "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local
machine."
At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:37 char:1
+ $connection.open(); $cmdObject.Connection = $connection; $dr = $cmdOb ...
+ ~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException

Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The
connection's current state is closed."
At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:37 char:58
+ ... ct.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datar ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException

You cannot call a method on a null-valued expression.
At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:39 char:8
+ While ($dr.Read()) {
+ ~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:47 char:1
+ $dr.Close(); $connection.close();
+ ~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Exception calling "Open" with "0" argument(s): "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local
machine."
At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:55 char:1
+ $connection.open(); $cmdObject.Connection = $connection; $dr = $cmdOb ...
+ ~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException

Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The
connection's current state is closed."
At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:55 char:58
+ ... ct.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datar ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException

Comments

  • 0neinthisworld0neinthisworld
    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:57 char:8
    + While ($dr.Read()) {
    + ~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:66 char:1
    + $dr.Close(); $connection.close();
    + ~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Exception calling "Open" with "0" argument(s): "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local
    machine."
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:73 char:1
    + $connection.open(); $cmdObject.Connection = $connection; $dr = $cmdOb ...
    + ~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

    Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The
    connection's current state is closed."
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:73 char:58
    + ... ct.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datar ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:75 char:8
    + While ($dr.Read()) {
    + ~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:82 char:1
    + $dr.Close(); $connection.close();
    + ~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Exception calling "Open" with "0" argument(s): "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local
    machine."
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:90 char:1
    + $connection.open(); $cmdObject.Connection = $connection; $dr = $cmdOb ...
    + ~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

    Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The
    connection's current state is closed."
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:90 char:58
    + ... ct.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datar ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:92 char:8
    + While ($dr.Read()) {
    + ~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

  • 0neinthisworld0neinthisworld
    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:103 char:1
    + $dr.Close(); $connection.close();
    + ~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Exception calling "Open" with "0" argument(s): "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local
    machine."
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:110 char:1
    + $connection.open(); $cmdObject.Connection = $connection; $dr = $cmdOb ...
    + ~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

    Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The
    connection's current state is closed."
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:110 char:58
    + ... ct.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datar ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:112 char:8
    + While ($dr.Read()) {
    + ~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:119 char:1
    + $dr.Close(); $connection.close();
    + ~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Exception calling "Open" with "0" argument(s): "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local
    machine."
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:127 char:1
    + $connection.open(); $cmdObject.Connection = $connection; $dr = $cmdOb ...
    + ~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

    Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The
    connection's current state is closed."
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:127 char:58
    + ... ct.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datar ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:129 char:8
    + While ($dr.Read()) {
    + ~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:144 char:1
    + $dr.Close(); $connection.close();
    + ~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Exception calling "Open" with "0" argument(s): "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local
    machine."
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:154 char:1
    + $connection.open(); $cmdObject.Connection = $connection; $dr = $cmdOb ...
    + ~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

    Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The
    connection's current state is closed."
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:154 char:58
    + ... ct.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datar ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:156 char:8
    + While ($dr.Read()) {
    + ~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:169 char:1
    + $dr.Close(); $connection.close();
    + ~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Exception calling "Open" with "0" argument(s): "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local
    machine."
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:177 char:1
    + $connection.open(); $cmdObject.Connection = $connection; $dr = $cmdOb ...
    + ~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

    Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The
    connection's current state is closed."
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:177 char:58
    + ... ct.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datar ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:179 char:8
    + While ($dr.Read()) {
    + ~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:197 char:1
    + $dr.Close(); $connection.close();
    + ~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Exception calling "Open" with "0" argument(s): "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local
    machine."
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:230 char:1
    + $connection.open(); $cmdObject.Connection = $connection; $dr = $cmdOb ...
    + ~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

  • 0neinthisworld0neinthisworld
    Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The
    connection's current state is closed."
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:230 char:58
    + ... ct.Connection = $connection; $dr = $cmdObject.ExecuteReader(); #datar ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:232 char:8
    + While ($dr.Read()) {
    + ~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelSrcData.ps1:238 char:1
    + $dr.Close(); $connection.close();
    + ~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    11:48:47 Script: ExtractExcelData . Duration: 0 sec.

    C:\PortfolioSlicer\PSDataExtract>powershell -ExecutionPolicy Bypass .\Scripts\GetExcelCostBasis.ps1
    You cannot call a method on a null-valued expression.
    At C:\PortfolioSlicer\PSDataExtract\Scripts\GetExcelCostBasis.ps1:69 char:1
    + $fTxt = $fTxt.Substring(0, $fTxt.Length-$eol.Length); # Removing last ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    11:48:48 Script: CalcCostBasis . Duration: 0 sec.

    C:\PortfolioSlicer\PSDataExtract>choice /C Y /T 10 /D Y /M "Waiting 10sec before closing"
    Waiting 10sec before closing [Y]?
  • VidasMatelisVidasMatelis
    Hello,

    Likely you are missing Microsoft Access drives that are used to extract data from Excel.
    Please try installing drivers from this link:

    https://web.archive.org/web/20230924130050/https://www.microsoft.com/en-us/download/details.aspx?id=13255

    Please post here if you still have any issues after above install.
  • 0neinthisworld0neinthisworld
    edited November 3
    Hi Vidas, thanks for getting back to me. I tried your suggestion but unfortunately I'm still running into the same error. Other things I have tried since, includes installing the redistribution package version 2016 32-bit, and the Access 2013 32-bit.
    When I installed Office the first time, I installed word, excel and powerpoint. Could there be something wrong with my Office installation? Any other suggestions?
  • VidasMatelisVidasMatelis
    Can you please try downloading from these links:

    32-bit: https://web.archive.org/web/20240214170634if_/https://download.microsoft.com/download/2/4/3/24375141-E08D-4803-AB0E-10F2E3A07AAA/AccessDatabaseEngine.exe

    64-bit: https://web.archive.org/web/20240214170634if_/https://download.microsoft.com/download/2/4/3/24375141-E08D-4803-AB0E-10F2E3A07AAA/AccessDatabaseEngine_X64.exe

    Most likely you will need 64bit version.
    If that does not work, please let me know.
  • 0neinthisworld0neinthisworld
    edited November 4
    It didn't work for me and I tried installing both.
  • VidasMatelisVidasMatelis
    Some time ago @gsbaker had similar issue and he coded script GetExcelSrcData.zip without using Access drivers.
    You can read more here: https://support.portfolioslicer.com/index.php?p=/discussion/727/script-error-running-windows-11-on-arm-based-pc-surface-pro-copilot#latest

    Can you please try @gsbaker solution?
    You should do following:
    1. Rename your script GetExcelSrcData.ps1 to GetExcelSrcData-Old.ps1 (just in case you need to restore back.
    2. Download and unzip file GetExcelSrcData.zip that is attached to this post. Right mouse click on script and "Unblock" it. Copy this GetExcelSrcData.ps1 to your regular PortfolioSlicer script location.
    3. In PowerShell you need to install ImportExcel Module. Here are instructions:

    # To install the ImportExcel module for all users on Windows 11 for ARM, you need to run PowerShell with administrative privileges. Here are the steps:
    # 1. Open PowerShell as Administrator:
    # - Press Win + X and select Windows Terminal (Admin) or Windows PowerShell (Admin).
    # 2. Install the ImportExcel Module:
    # - Run the following command to install the module for all users:
    # Install-Module -Name ImportExcel -Scope AllUsers
    # This command needs to be executed only one time. It installs the ImportExcel module for all users on the system, making it available to any user account.

    4. Now try to run your usual PortfolioSlicer scripts and let us know if it works.
Sign In or Register to comment.