Hello,
I try to update an Excel file to update a named range.
This Excel file will be used by users. They will update data in Excel and I wanted that some value are dynamic. (Value not calculed in EasyMorph but live calculated in Excel).
Formula are computed and created in EasyMorph. But when I open Excel File, I see formula and I must go to cell dans press “enter” to have cell computed.
In this example, “Stock” “mai-21” is computed after push “enter”.
How to make Excel interpret formulas sent by EasyMorph automatically?
You will need PowerShell scripting in EasyMorph to trigger either a VBA macro in Excel that would update necessary cells, or use the COM-automation API in PowerShell for that (if it’s available).
See also this topic: Running Excel Macro in EasyMorph Project
1 Like
Thanks.
I don’t have time to test now but I think that this solution works.
Finally, it doesn’t work.
VBA code fails to initiate interpretation of formulas. VBA considers text to be a constant.
Do you have a solution ?
Can you post your VBA / PowerShell script?
The size of the formulas was far too long. Using shorter formulas works.
I have not yet been able to test the call of the VBA by a powershell but the VBA alone in the file works.
I have several issues that I cannot resolve. These errors are present even though I have not yet run a macro.
- Excel file stay open by EasyMorph
After using this action, users have warning that file is in use and can only be open in read only.
I must close EasyMorph to release file and sometime, file are not released. If I run from EasyMorph server, I must restart server to get full access to Excel File. File is clone of another Excel file without error.
-
Error when open Excel file after EasyMorph write in file.
Quick translate : "there is an error in data"
-
Run macro from EasyMorph
$excel = new-object -comobject excel.application
$workbook = $excel.workbooks.open("{Filename}")
$excel.Run("MyMacro", "ThisWorkbook.Mise_en_forme")
$workbook.close()
$excel.quit()
I think that 1 and 2 are linked.
Excel file work before EasyMorph write into.
I can't send morph file because some SQL request is used to compute data and you can get these data.
Written data : 1609 lines, 21 columns in named range. EasyMorph extend size of this range.
Edit: Write range or sheet raise same errors.
Hello @Florent,
- The Export to Excel file action releases the modified file when the action completes.
If the file may stay locked after closing EasyMorph Desktop, most likely, some other software keeps the lock on the file. Do you have an antivirus software running?
-
It seems that you are replacing data on a worksheet that contains formulas. It’s better to export data to a separate worksheet that doesn’t contain formulas and then reference that worksheet from another worksheet.
-
Yes, the possible source of that error is the error, described in 2
-
I will ask to your IT.
-
I don’t replace data with formula. I have created an empty range to get data from EasyMorph.
Formula in other worksheets reference this range. Some cells from EasyMorph are formula to have dynamic form with manually data changed by users.
I research and test over the next few days and will come back to notify you of the progress.
I was able to correct points 1 and 2. I emptied the data so that the initial table was empty and I make a copy and modify the copy. However, the data is read from the source file. In this case, I no longer have a problem with the Excel file.
For point 3, I no longer have this error but I have changed 2 lines and I have another error :
$excel = new-object -comobject excel.application
$workbook = $excel.workbooks.open("{Filename}")
$excel.Run("ThisWorkbook.Mise_en_forme")
$workbook.close($True)
$excel.quit()
If I run powershell script outside EasyMorph, all work.
With EasyMorph, I have this error :
Error: La référence d'objet n'est pas définie à une instance d'un objet.
Source: action "PowerShell", module "Run Macro", table "Run macro"
But even with error, VBA was launch and all data in Excel are ok.
I can ignore error but I prefere to not have error.
Here is a test case which reproduces these 2 issues.
Some data to test are already in sandbox. Morph and xlsm must be in same folder.
Step to reproduce :
- Run manually all actions in “Data Computed”. Open Excel : All works.
- Run manually all actions in “Run macro” : Error with VBA
- Open Excel : Data in sheet SOP are formatted
- Disable “iterate” in “run macro”
- Run all actions in “data Computed(2)” : No error
- Open Excel file : Several errors with data lost
Sometimes Excel are also in use and you can open only in read only.
Test-Excel.morph (7,7 Ko)
Test-Excel.xlsm (25,0 Ko)
@Florent, the data in the Sandbox action are not saved to a project file. And I can’t replicate the issue without the actual data. Can you please post a DSET file with the data that should be pasted to the Sandbox action?
morph are updated to use dset files.
Test-Excel.morph (7,9 Ko)
Test-Excel1.dset (3,6 Ko)
Test-Excel2.dset (4,1 Ko)
Test-Excel3.dset (2,4 Ko)
@Florent, thank you for the files.
The execution of the VBA script leads to the creation of formulas on the SOP sheet. This, in turn, leads to the creation of the calcChain.xml file in the Workbook package. And that file contains references to the formula cells.
When the export in the “Data Computed(2)” table is executed, it overwrites formulas with text values. And references in the calcChain.xml become invalid. Hence the error message about file corruption when you open it with Excel.
The actual data and formulas are not affected by this issue. You can just resave that XLSX file in Excel and the error message will not be displayed again. Or you can try to resave it with another VBA script.
The Export into Excel file action should not be used to replace data on a sheet that contains formulas. The idiomatic approach here is to export data to a separate sheet and have formulas from other sheets to reference the exported data.
Now it’s clear. Step to update this sheet :
- Unzip Excel file
- Delete file .\xl\calcChain.xml
- Zip folder with file extension “.xlsm”
It’s work.
Only the error message remains when running VBA script.
Do you have any details on what does not work while script works when executed by user in Excel?
Thanks.
That error looks like a bug from our side. We are going to fix it and publish a fixed version.
Version 4.7.1.13 work well.
Server Version published on website doesn’t show version. Is it the same version or an older version that is downloadable?
The current Server version is the same as Desktop - 4.7.1. The build number can be different for Desktop and Server.
With desktop build 12, VBA in Excel doesn’t work well.
With build 13, it’s fixed.
Last server build fix this issue ?
