I have a situation where I load a large .csv file into a worksheet then reparse the columns into another sheet - the data from the 2nd sheet is used to populate a db table.
To do this I create an Excel Instance, load the data into Sheet1, add macro code from a .bas file into the workbook, execute the code (which puts transformed data into sheet 2), grab the data from sheet 2, then close the 'temp' workbook (no need to save it). Essentially Excel acts as middleware. The macro code would print out to about 17 pages, is specific to Excel so no use re-writing for my calling app. The vba code can take some time to execute.
My question is: How do I know when the VBA code is complete? The calling program will be busy doing other things. Putting up a Message Box in Excel is not a good solution. Having the vba code write out a semaphore file 'done.txt' is possible (then have the calling program loop, checking for the exisitence of that done.txt), but I would rather check for an event or other indicator, but have been unable to find one.