I am not sure why there is an error in the test document in the first place, but I was running the macro while having the documents on 1 folder up from the source files, I did not understand that having the documents in a different folder could be a problem. So I created a new test file and it works properly.

However, I run multiple tests on the original file which has multiple connections (by having it as well on the same folder) and for some reason the macro does not work. Also, there are no errors produced this time (so far).

I am not sure how to debug it, any ideas are welcome, but unfortunately I cannot edit the file to the point where I can upload it on the web.

I will keep trying and if somehow I figure out the problem I will let you know.