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

6 Years
Discussion Span
Last Post by AndreRet

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.

Edited by Panicbutton: n/a


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.
        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

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.

Edited by AndreRet: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.