So I am having trouble with visual basic. I've scoured several forums (although someone might link me to another thread on this one) and have not been able to find a solution to my problem. I need to stop the code from running for an amount of time that varies based on how long it takes several .bat files which the excel macro calls to finish running. The macro simply uses setenvironmentvariables using a large series of for loops/if statements to change the variables "filename" and "savename". I need it to pause until each iteration of the .bat file finishes running or an alternate suggestion. I am already using the sleep function however some of these .bats take 10 seconds to run while others take about 50
You can either use the 'Do Events' statement or personally I would break down the function into smaller functions, when 1 .bat file is read, load the following function and so on.
Example of second solution?? I don't quite follow what you mean. I am unable to post my code up here, but its fairly simple. It uses the for/if system to generate several variables, then calls a module that defines filename and savename using the generated variables and then calls the appropriate .bat file.... These .bat files initiate a .exe program which reads in "Filename", makes some calculations, and then outputs "savename" to a .xls file. That is what takes a varying amount of time. I apologize for the vagueness on the first post.
Ok, so what you need to do is to get the file size of each .bat file. While it is sending the data over to an excel sheet, count each size send, let a label and progress bar show the progress so the user know what is happening. You can not "Pause" the sending to excel, if you want to transfer the data over. Again, rather let the user see that something is happening while they go and make some coffee. Also see that you are not using variants as declarations, because they tend to eat away all your resources.
First search online for some sample code to get filesizes in vb6, there are plenty - "Get size of a file in VB6" returns plenty.
Second your code will look something like -
Dim MyTotalBatSize As Byte Dim MyBatFileSize As Byte Dim xBat As Integer, xProg As Integer 'Get the .bat file sizes her prgBar.Max = MyTotalBatSize lblBat.Caption = "0 Bytes of " & MyTotalBatSize & " send" xProg = 0 Do While MyTotalBatSize < MyTotalBatSize For xBat = 0 To MyBatFileSize If xBat => MyBatFileSize Then 'Load the next bat file here GoTo NextBatFile 'rewrite the code in the GoTo NextBatFile so we can work on the loading and countdown of the next etc. Else xProg = xProg + 1 prgBar.Value = prgBat.Value + 1 lblBat.Caption = xProg & " Bytes of " & MyTotalSize & " send" 'Resume your normal code to transfer to excel here... End If Next xBat Loop
This is not fool proof, but only an idea which I think you are looking for. It would have clarified a lot if you could post some kind of code so we can see what exactly you are trying to achieve, especially your declarations and the if, loop, next statements.