Since a two weeks i try to generate an automated excel workflow, which involves an external workbook containing (some) data, which has to be copied to yet another external workbook. The program i try to write intermediates this proces (and has some additional functions, which isn't relevant dor this particular procedure).
In this automated prcedure, Field-data has to be copied every 15 minutes from the (always newly updated) data-source workbook (with a constant filename), to the target-workbook. And i try to accomplish this all without opening and closing these workbooks over and over again (even not in the background).
So far i managed to set up an ADODB.connection with the source-workbook and it works perfectly to copy my data in a class-collection, without opening this workbook in the background.
I use ADOX.Catalog to append a new field every 15 minutes in the target Workbook.
However, something these procedure works perfectly, and other times it generates a corrupted file, causeing errors in my procedure, because of the inability to make a propper connection with the target workbook.
In the code above i denoted where the problem is being generated.
I hope you guys have some suggestions or idea's to overcome this problem.
Thank you in advance!!