I’m trying to run a workflow that has a quick stop in the middle to run a vba code from a third party company in Excel and then it comes back to Knime.
This is how I set up the workflow (see below screenshot). It was working at the beginning but now it’s not. Not sure if there’s a different way to make a workflow wait until a file is modified.
The first wait node is set up to “Creation”, the second one to “Modification” (where I’m getting stuck) and the third one to “1 second” (just to give it time save and close). The file that’s being exported (excel appender) is the same file that’s being read after (excel reader).
The excel appender node is set up to open after execution which in turns triggers the vba macro once the file is open. The macro is set up to run, save and close automatically.
So just to recap, I’m running a workflow that exports data to excel --> excel runs a vba script, saves it, and closes it automatically --> Knime reads that same file after it’s done.
Any alternative suggestions/ideas are welcome as well. Thx for your help!
Do you have a workflow example with some dummy data that you could upload that demonstrates the problem? You mention that it was “working at the beginning but now it’s not” - do you have an idea about what might have changed?
I gather that maybe the issue is that the Wait node isn’t detecting whatever changes are being made to a file by the VBA macro. Is it possible that sometimes, due to particular conditions, that changes aren’t made and that’s why it hangs?
Without additional detail I’m just guessing at possibilities I’m afraid.
Thanks for replying to my post. Yes, it was working before I updated my Knime to 4.2.0, after that, it stopped working for some reason.
I have attached a workflow example similar to what I’m trying to do. Unfortunately I cannot attached the excel .xlsm file because it’s not authorized for this forum, however, here’s a simple code doing some math to recreate my intentions, you will have to open a new spreadsheet and paste this in the VBA developer (in the workbook, not a module):
I have the same issue that you do, which is that the middle node for modification is stuck waiting for the created XLSX file to change.
But what about it is supposed to change, and how is that change to take place? There are no nodes between the first and second wait nodes. Shouldn’t there be something that triggers the change to the file at that point?
So @ScottF, the VBA is using an app that calculates import and export tariffs and taxes for pretty much any city/country in the world, and that’s why I can’t use Knime for those calculations.
So I need Knime to export city and country names to excel so the macro can calculate those taxes and return a dollar amount for each city combination. The change is supposed to be triggered by saving and closing the file after the macro is finished. After the taxes are calculated, I want to read that same file and continue working in Knime.
I know it’s annoying having to go back to excel in the meddle of the workflow, but I have no other option at this point. (The App does not have web services version with an API Key available at the moment either).
@Alejandroc I am not really sure if such a construction would work. Open Excel file, trigger VBA thru the opening of the file, let VBA run and close the file automatically and save it. You said this worked before.
A scenario that might have a chance is this:
in the workflow define a timestamp and give that to a variable
append a separate sheet to excel with two columns one with the time stamp one empty
the VBA script at the end reads the separate sheet and copies the time stamp to the second column, saves and closes the file
in KNIME in the meantime there is a loop that would fail if the file could not be opened because Excel is still on it (Try Catch)
if it succeeds it would read the separate sheet and would compare the two columns.
if they match it will proceed (meaning the VBA was successful), if not the loop will start again
This would involve some playing around with the loops, a function until or you break the loop once the condition is met.
This would maybe not be the most stable setting but it might work. I have googled: There are some R and Python packages that promise to execute VBA within the code that one could try and integrate in KNIME nodes for R and Python but I have found nothing particularly convincing.