PDA

View Full Version : Solved: Detect when VBA code has finished



stanl
03-19-2012, 03:38 AM
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.

Trebor76
03-19-2012, 03:46 PM
Hi stanl,

Not too sure if this helps but perhaps the flag could be based on the number of Excel instances open as there will be one less when the code has finished?

Perhaps another possibility would be to send yourself an email when the code has finished.

HTH

Robert

Jan Karel Pieterse
03-19-2012, 11:25 PM
I guess if you can't address the Excel application instance from your native code, havoing the VBA code write a text file is a simple workaround. What happens at the end of the VBA code, does it quit Excel?

GTO
03-20-2012, 12:11 AM
Greetings Stan,

Just a thought, and I'm well out of my league here, but I am assuming the calling app is getting Excel to do all this, but doesn't "hear" Excel? Anyways, just a thought, but maybe a msg from Excel to the app in DDE?

Mark

stanl
03-20-2012, 09:24 AM
What happens at the end of the VBA code, does it quit Excel?

No. basically my main app opens excel and loads a .csv that might look like

test,hello,1:2:3:4 & 5 & 6, test1,hello1,f1:t2,simple & such

[it is really tab-delimited, I used commas here] - and the vba macro code is then loaded to process this into more readable columns in another tab, the main app then reads that second tab into a db. For a small .csv this is not an issue, but with large files the main app needs some sort of mutex to know when vba code is finished. I have used a semphore, i.e. have the vba code write "done" to done.txt, then the main app simply looks for the existence of done.txt - I was just hoping there was a COM event or property like IE's readystate the main app could use rather than going the file route.

Jan Karel Pieterse
03-20-2012, 09:42 AM
Well, what is possible probably depends on your programming environment and code I suppose. Without knowing that I'd go for the semaphore since it works.

shrivallabha
03-20-2012, 10:14 AM
Is this of any use? [Use the Windows Script Host Shell object PopUp method]:
http://www.tushar-mehta.com/publish_train/xl_vba_cases/1023_display_message_for_a_specific_duration.shtml

stanl
03-21-2012, 10:16 AM
Well, what is possible probably depends on your programming environment and code I suppose. Without knowing that I'd go for the semaphore since it works.

Yes. Actually just writing "DONE" in a A1 of a third tab works fine. I really wanted to avoid file i/o or anything like a pop-up that just interupts program flow. Excel just loads and transforms the .csv - nothing is written to an .xlsx file. The calling app handles closing Excel, updating the db and cleaning up the original file. I ended up having the calling app loop, checking for "DONE" in Sheet3/A1 every 5 seconds.

This was a classic case of throwing the baby out with the bath water. The VBA code already worked, so no use re-writing it line by line in the calling app in order to transform the .csv w/out Excel. Pretty useful technique as well.