Macro Progress Bar

[pr4t3ek]

Hi,
As i have a pretty "complex" macro, which takes around 20-30 minutes to do (honestly)....
I was wondering if there was a way to create a progress bar just showing me how many % it has done so for?

Or even tell me which sub routine it is up to? (but % would probably be better)

And preferably a pop-up box one?.. not one on the status bar because i have stuff updated on the status bar already...
Thanks in advance =]

[shg]

See http://www.andypope.info/vba/pmeter.htm

[pr4t3ek]

i've had a look at the code for a few minutes & i've inserted my code where appropriate but it doesnt seem to work?

I guess i can try a bit later and put some into it but is there a way to just get me to code for the progress bar?

[shg]

Post your code.

[pr4t3ek]

Sub TexttoAccess()

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const ForReading = 1

Dim myDB As String
Dim myTable As String
Dim myfilename As String
Dim myconn As ADODB.Connection
Dim myRS As ADODB.Recordset
Dim myline As Long
Dim mylines As Long

Set myconn = CreateObject("ADODB.Connection")
Set myRS = CreateObject("ADODB.Recordset")

myDB = "C:\Documents and Settings\Pratik\Desktop\Testdb.mdb"
myTable = "tblTest"

myfilename = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt," & _
"CSV Files (*.csv),*.csv")

myconn.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = " & myDB

myRS.Open "SELECT * FROM " & myTable, _
    myconn, adOpenStatic, adLockOptimistic

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set myfile = objFSO.OpenTextFile(myfilename)

myfile.readall
mylines = myfile.Line

Set myfile = objFSO.OpenTextFile(myfilename)

Do Until myfile.AtEndOfStream
    myline = myfile.Line
    mystr = myfile.readline

    Application.StatusBar = "Processing Record to Access Database: " _
            & Format(myline, "#,###") & " of " & Format(mylines, "#,###") & " | " & Round(myline / mylines * 100) & "% Done"

        If myline <> 1 Then
            
            myarray = Split(mystr, "|")
            
            myRS.AddNew
                myRS("case") = myarray(1)
                myRS("cat") = myarray(2)
                myRS("key") = myarray(3)
                myRS("status") = myarray(4)
                myRS("created on") = Replace(myarray(5), ".", "/")
                myRS("PoD ID") = myarray(6)
            myRS.Update
        End If

Loop

myRS.Close
myconn.Close

Application.StatusBar = ""

End Sub

[shg]

What am I missing? Doesn't this line do what you want?

    Application.StatusBar = "Processing Record to Access Database: " & _
                            Format(myline, "#,###") & " of " & _
                            Format(mylines, "#,###") & " | " & _
                            Format(myline / mylines, "0%") & " Done"

[pr4t3ek]

there are other parts to the code which i have not included, which is whole another macro (which i have not finalized yet) hence why i was hoping that you could work with what i've provided you & i'll work around that.

[shg]

I might, but I don't see what you're trying to do. You have a progress indicator in the status bar now that reports progress of the only loop in your code.

i've inserted my code where appropriate but it doesnt seem to work?

What code have you inserted where?

[pr4t3ek]

I opened up the link that you sent me.
I opened up the Progressmeters.xls file..
and i put all the code that i've put up in my previous message in:

Sub DemoProgress1()
'
' Progress Bar
'
    Dim intIndex As Integer
    Dim sngPercent As Single
    Dim intMax As Integer
    
    intMax = 100
    For intIndex = 1 To intMax
        sngPercent = intIndex / intMax
        ProgressStyle1 sngPercent, chkPg1Value.Value
        DoEvents
        '------------------------
        ' Your code would go here
        '------------------------
        Sleep 100
    Next

End Sub

i put my code in:

 '------------------------
        ' Your code would go here
        '------------------------

There is a lot more code, which will not fit into this message. Thank You

[shg]

That's going to execute whatever code you put there 100 times - is that what you want?

The thrust of Andy's examples were to show how to create progress meters -- you have to adapt the code to your application.

Are you trying to show the percent of lines read from the given file?

[pr4t3ek]

no what im after is a progress bar for the whole macro.

Say for instance, from the time i execute my macro, it takes 10 minutes to complete "the whole macro" once, i would like a progress bar telling me the progress of the macro.

Just like the one in my code which shows the progress of the loop, but i want one for the "whole" macro...

[shg]

Then you need to have points in the macro that you can compute relative progress.

[pr4t3ek]

and what does that exactly mean?

I'm sure someone has code which has this, it shouldn't be too complicated?

[Andy Pope]

It's complicated in the fact that you have to determine how far the macro has progressed because you are not reporting a loop only process.

So you need to pick points in your code that reflect progress points in your whole macro.

[pr4t3ek]

and how do i do that exactly?

so can that be done when it changes subroutines maybe?

i've prob got like 12 subroutines so. How many it's done so far divided by total subroutines times 100??

how would i go about implimenting that exactly..