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! :)
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)