1

I have been trying to automate excel automation that installs our company addin in excel and then load the library file (library.xlsm that contains the library macro subroutines and functions). After this for each test, I load the corresponding test.xlsm file and execute the macros. All this, I am doing using powershell (V3). My script gets called on one machine and executes on another remote machine.

This is how I install my addin:

kill -processname excel
$Release1RootDir = $workspace + "\Release1"
            $release1Path = Get-ChildItem -Force $release1RootDir
            if($release1Path -eq $Null) {
               echo "Error: No sub-folder found having MyAddin Installer inside "$release1RootDir
            }
            else {
               $release1 = $release1Path.name.replace('_', '.')
               $ExcelAddinInstaller = ($release1Path.FullName + "\MyAddin.msi")
               $ExcelAddinTargetDir = ($Release1Path.FullName)
       $msiexecPath = "msiexec.exe"
       if(Test-Path -Path $ExcelAddinInstaller){
                    echo "Version for MyAddin inside Release1: "$Release1
                    $proc = Start-Process $msiexecPath -ArgumentList /x, `"$ExcelAddinInstaller`", TARGETDIR=$ExcelAddinTargetDir, /quiet, /lvx, "D:\Temp\uninstall.log" -Wait 
            $proc = Start-Process $msiexecPath -ArgumentList /i, `"$ExcelAddinInstaller`", TARGETDIR=$ExcelAddinTargetDir, /quiet, /lvx, "D:\Temp\install.log" -Wait -ErrorAction Stop
                    echo "Installing addin"
                    Start-Process "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\RegAsm.exe" -ArgumentList /tlb, "C:\Users\Serviceadmin\Addin\MyAddin\Release\MyAddin.dll"
       }
               else{
                    echo $ExcelAddinInstaller
               }
            }

PS: I am adding the .dll file since, the helper function in my Macros call my C# code.

However, while opening any of these xlsm files, I get the following error:

Exception calling "Open" with "1" argument(s): "Microsoft Excel cannot access the file 'D:\ABC\XYZ\workspace\library.xlsm'. There are several possible reasons: The file name or path does not exist. The file is being used by another program. The workbook you are trying to save has the same name as a currently open workbook." At C:\Users\Serviceadmin\AppData\Local\Temp\hudson.ps1:94 char:3 + $libraryBook = $excel.workbooks.open("$xlLibraryPath\$xlLibraryFileName"); + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ComMethodTargetInvocation

The above error comes up despite the path of the files being correct. This is how I open my files using powershell:

$excel = new-object -comobject excel.application;
    $excel.visible = $true;
    $libraryBook = $excel.workbooks.open("$xlLibraryPath\$xlLibraryFileName");
    $testWorkbook = $excel.workbooks.open("$testFile")
    $excel.Run("$xlLibraryFileName!Initialize", "$testAct")
    $loginsuccess = $excel.Run("$xlLibraryFileName!Login", "$xlenvironment", "$xlUserName", "$xlPassword");
    if($loginsuccess)
    {
      $excel.Run("PerformTest");
      $excel.Run("$xlLibraryFileName!Logout");
    }
    $testWorkbook.close($false)
    $libraryBook.close($false)
    $excel.quit()

I have verified the following:
1. Excel is installed in the required machine- Yes, Excel 2013
2. The path of the xlsm files- All are present
3. Successful installation of the addin

Is there something that I am missing?

Thanks in advance! :)

Rishi
  • 980
  • 10
  • 21
  • Are you absolutely sure the path is correct? "cannot access the file '\library.xlsm'" sounds like $xlLibraryPath is null? Btw: I generally prefer join-path for combining paths as it helps to avoid errors with missing slashes – whatever Aug 12 '16 at 09:44
  • I have omitted out the full path in the above post...But, yes, it's correct....PS: I have added the path now. – Rishi Aug 12 '16 at 10:04
  • Is that a mapped drive? Does the profile running the script have that drive mapped? – TheMadTechnician Aug 12 '16 at 16:56
  • @TheMadTechnician... No, it is not mapped drive. I actually login into the remote machine and perform the required tasks. – Rishi Aug 14 '16 at 15:08

1 Answers1

0

Well, it turns out that I had to add the folder- "Desktop" in each of the following paths. The solution is quite weird but it is now working for me:

C:\Windows\System32\config\systemprofile\Desktop (should be present for 32 bit machine and even for 64 bit)

C:\Windows\SysWOW64\config\systemprofile\Desktop (only for 64 bit machine)

Rishi
  • 980
  • 10
  • 21