Wait to file modification

Hey Knime community,

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!

Any ideas? suggestions?

Hi @Alejandroc -

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.

Hi @ScottF

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

Blockquote

Private Sub Workbook_Open()

Application.GoTo (ActiveWorkbook.Sheets(“default”).Range(“E2”))

ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
Range("E2").Select

Last_Row = ActiveWorkbook.Worksheets("default").Cells(Rows.Count, 1).End(xlUp).Row

Selection.AutoFill Destination:=Range("E2:E" & Last_Row), Type:=xlFillDefault

ActiveWorkbook.Close SaveChanges:=True

End Sub

Blockquote

The file looks like this after you run the workflow (calculation in column E):
image

KNIME_Wait to file modification.knwf (14.9 KB)

I hope this helps to understand better the issue. If you have any alternative ideas/suggestions, I’m open to hear them. Thanks!

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?

On top of Scott comments, I just wondering why you need vba. you can do the calculation in KNIME.

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.

https://www.knime.com/nodeguide/control-structures/loops

Thanks @mlauber71 I will give a try tonight and let you know if it works.

1 Like

Also, you may consider the way like the one below

2 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.