Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
distributed vba project - multiple thread / concurrent file access
Hi,
I know that vba isn't really written to allow this, but it's the only tool I've got available. My monte carlo model takes four hours to run on a single machine. I thought it would be a good idea to split the processing over several machines. I've set the model up so the number of simulations is split up, and each machine takes a set of these and runs with them (each machine takes a 'job'). So far so good. To allocate which machine takes which job I've written the job names in an excel file. The machines poll on this file until it is available. Once they get it and open it they take a job (and set a flag to say they've taken it), close the file and run with the job. I use the following code for the polling (isFileOpen is a function I fonud on the MS support site - similar to John Walkenbachs fileIsOpen()) : *** While (IsFileOpen(wbname)) ' wait a short (random) time (up to ten seconds) waitTime = Rnd() * 10 Debug.Print Now() Application.Wait (Now + TimeValue("0:00:" & waitTime & "")) DoEvents Wend ' ok open the file, take a job etc.... workbook(wbname).open ...... *** Now, I'm really concerned about concurrency issues - since three machines could theoretically all hit the isFileOpen at the same time and attempt to open it (or set the same flags). This would cause any number of difficult to solve problems. Anyone got any ideas? is there a quicker (i.e. atomic) way to check and open a file? can anyone think of someway of using locks on the file? Any advice from someone who's tried similar? Many thanks, Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
distributed vba project - multiple thread / concurrent file access
I would expect the operating system to handle this.
-- Regards, Tom Ogilvy " wrote: Hi, I know that vba isn't really written to allow this, but it's the only tool I've got available. My monte carlo model takes four hours to run on a single machine. I thought it would be a good idea to split the processing over several machines. I've set the model up so the number of simulations is split up, and each machine takes a set of these and runs with them (each machine takes a 'job'). So far so good. To allocate which machine takes which job I've written the job names in an excel file. The machines poll on this file until it is available. Once they get it and open it they take a job (and set a flag to say they've taken it), close the file and run with the job. I use the following code for the polling (isFileOpen is a function I fonud on the MS support site - similar to John Walkenbachs fileIsOpen()) : *** While (IsFileOpen(wbname)) ' wait a short (random) time (up to ten seconds) waitTime = Rnd() * 10 Debug.Print Now() Application.Wait (Now + TimeValue("0:00:" & waitTime & "")) DoEvents Wend ' ok open the file, take a job etc.... workbook(wbname).open ...... *** Now, I'm really concerned about concurrency issues - since three machines could theoretically all hit the isFileOpen at the same time and attempt to open it (or set the same flags). This would cause any number of difficult to solve problems. Anyone got any ideas? is there a quicker (i.e. atomic) way to check and open a file? can anyone think of someway of using locks on the file? Any advice from someone who's tried similar? Many thanks, Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
distributed vba project - multiple thread / concurrent file access
Yeah - I imagine it should do with making sure that only one file can
open another at the same time... the problem I'm thinking off is the following: - imagine index.xls is not open (and held on a network drive somewhere). - two machines test at the same time that it's not open (unlikely). - both machines break out of the while loop. - one machine is able to open the file (pbly not both, as you suggest). - the other machine is left in limbo - can't open the file, can't get back on it's poll :( Not sure if this could happen, though if it could it would be rare? On Jun 14, 2:15 pm, Tom Ogilvy wrote: I would expect the operating system to handle this. -- Regards, Tom Ogilvy " wrote: Hi, I know that vba isn't really written to allow this, but it's the only tool I've got available. My monte carlo model takes four hours to run on a single machine. I thought it would be a good idea to split the processing over several machines. I've set the model up so the number of simulations is split up, and each machine takes a set of these and runs with them (each machine takes a 'job'). So far so good. To allocate which machine takes which job I've written the job names in an excel file. The machines poll on this file until it is available. Once they get it and open it they take a job (and set a flag to say they've taken it), close the file and run with the job. I use the following code for the polling (isFileOpen is a function I fonud on the MS support site - similar to John Walkenbachs fileIsOpen()) : *** While (IsFileOpen(wbname)) ' wait a short (random) time (up to ten seconds) waitTime = Rnd() * 10 Debug.Print Now() Application.Wait (Now + TimeValue("0:00:" & waitTime & "")) DoEvents Wend ' ok open the file, take a job etc.... workbook(wbname).open ...... *** Now, I'm really concerned about concurrency issues - since three machines could theoretically all hit the isFileOpen at the same time and attempt to open it (or set the same flags). This would cause any number of difficult to solve problems. Anyone got any ideas? is there a quicker (i.e. atomic) way to check and open a file? can anyone think of someway of using locks on the file? Any advice from someone who's tried similar? Many thanks, Chris- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
distributed vba project - multiple thread / concurrent file ac
Dim bk as Workbook
Do if not isfileopen( abc) then set bk = nothing On error resume next set bk = workbooks.open("index.xls") On Error goto 0 Else 'pause end if loop while bk is nothing I haven't tried it, but I assume if you can't open the book it will raise an error. -- Regards, Tom Ogilvy " wrote: Yeah - I imagine it should do with making sure that only one file can open another at the same time... the problem I'm thinking off is the following: - imagine index.xls is not open (and held on a network drive somewhere). - two machines test at the same time that it's not open (unlikely). - both machines break out of the while loop. - one machine is able to open the file (pbly not both, as you suggest). - the other machine is left in limbo - can't open the file, can't get back on it's poll :( Not sure if this could happen, though if it could it would be rare? On Jun 14, 2:15 pm, Tom Ogilvy wrote: I would expect the operating system to handle this. -- Regards, Tom Ogilvy " wrote: Hi, I know that vba isn't really written to allow this, but it's the only tool I've got available. My monte carlo model takes four hours to run on a single machine. I thought it would be a good idea to split the processing over several machines. I've set the model up so the number of simulations is split up, and each machine takes a set of these and runs with them (each machine takes a 'job'). So far so good. To allocate which machine takes which job I've written the job names in an excel file. The machines poll on this file until it is available. Once they get it and open it they take a job (and set a flag to say they've taken it), close the file and run with the job. I use the following code for the polling (isFileOpen is a function I fonud on the MS support site - similar to John Walkenbachs fileIsOpen()) : *** While (IsFileOpen(wbname)) ' wait a short (random) time (up to ten seconds) waitTime = Rnd() * 10 Debug.Print Now() Application.Wait (Now + TimeValue("0:00:" & waitTime & "")) DoEvents Wend ' ok open the file, take a job etc.... workbook(wbname).open ...... *** Now, I'm really concerned about concurrency issues - since three machines could theoretically all hit the isFileOpen at the same time and attempt to open it (or set the same flags). This would cause any number of difficult to solve problems. Anyone got any ideas? is there a quicker (i.e. atomic) way to check and open a file? can anyone think of someway of using locks on the file? Any advice from someone who's tried similar? Many thanks, Chris- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
distributed vba project - multiple thread / concurrent file access
Chris,
I have seen a product that enables Excel to spread the workload over available machines, co-ordinating the processing and results. I can't remember its name now, but a Google search on suitable terms may bring it up. No idea on cost or applicability to you. Have you considered using ADO to query/update the WB. Not sure if it would be better, but you would not have the IsOpen problem. NickHK wrote in message ups.com... Hi, I know that vba isn't really written to allow this, but it's the only tool I've got available. My monte carlo model takes four hours to run on a single machine. I thought it would be a good idea to split the processing over several machines. I've set the model up so the number of simulations is split up, and each machine takes a set of these and runs with them (each machine takes a 'job'). So far so good. To allocate which machine takes which job I've written the job names in an excel file. The machines poll on this file until it is available. Once they get it and open it they take a job (and set a flag to say they've taken it), close the file and run with the job. I use the following code for the polling (isFileOpen is a function I fonud on the MS support site - similar to John Walkenbachs fileIsOpen()) : *** While (IsFileOpen(wbname)) ' wait a short (random) time (up to ten seconds) waitTime = Rnd() * 10 Debug.Print Now() Application.Wait (Now + TimeValue("0:00:" & waitTime & "")) DoEvents Wend ' ok open the file, take a job etc.... workbook(wbname).open ...... *** Now, I'm really concerned about concurrency issues - since three machines could theoretically all hit the isFileOpen at the same time and attempt to open it (or set the same flags). This would cause any number of difficult to solve problems. Anyone got any ideas? is there a quicker (i.e. atomic) way to check and open a file? can anyone think of someway of using locks on the file? Any advice from someone who's tried similar? Many thanks, Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
access my vba project | Excel Programming | |||
OLAP Offline Cube - How Many Multiple Concurrent Users | Excel Programming | |||
Cannot save Project file as MS Access file | Excel Discussion (Misc queries) | |||
Assigning the Help 4, *.HLP file for a project programmatically in a protected Project | Excel Programming | |||
NETWORKDAYS in a distributed file | Excel Programming |