Please help Progression Bar

[ramnaidu]

Hello All

Hope you are doing good, I have a very limited knowledge with Excel and VBA, so please help me on below.

I have macro which runs SQL query and bring the data back to excel, I want to see the progression bar or time or any other thing which shows that the macro is still running. My macro is as follows, please do the needful.

Sub Get_M_AgentDaily()
'
' Dim theQueryText As String
Dim theQueryTextcomp As String
Dim datestamp As String
Dim current_date As Date

ThisWorkbook.Activate
Sheets("SQL").Activate


Sheets("SQL").Select
Range("B3").Select

theQueryText = ""

While ActiveCell.Value2 <> ""
theQueryText = theQueryText + ActiveCell.Value2
ActiveCell.Offset(1, 0).Select
Wend

Sheets("AgentDaily").Select
Range("A5").Select
Range("A5:IV65536").ClearContents

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=CNWSQLH004P01" & ";APP=" & Fname & ";DATABASE=TAMI;Trusted_Connection=yes;" _
, Destination:=Range("A5"))
.Sql = theQueryText
.Refresh BackgroundQuery:=False

End With

End Sub

 

[Luke M]

You could change the last bit to something like this:

'Display a message at bottom of XL
Application.StatusBar = "Running Query. Please wait..."
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=CNWSQLH004P01" & ";APP=" & Fname & ";DATABASE=TAMI;Trusted_Connection=yes;" _
, Destination:=Range("A5"))
.Sql = theQueryText
.Refresh BackgroundQuery:=False

End With
'Clear the Status bar
Application.StatusBar = False
'Alert user
MsgBox "All done!"

 

[ramnaidu]

Thanks a lot Luke M, this one is so simple and it cleared my problem. You are a star.

Can you please help me on the below as well if possible.

1 - How to keep an error message in the same way if query didnt run properly

2 - Today morning I have seen a thread in google, some one has has inserted a progression bar like a GIF while Macro is running, I have tried the same by creating userforms, modules etc, but it didnt worked. I have attached the link (http://www.learnexcelmacro.com/wp/2012/02/progressbar-in-excel-vba/) for your reference, where should I insert my code to happen in the same way.

As I have 50 sql queries running one after the other I want to show them some form of progression bar showing the queries all still working or else my managers is thinking that the spreadsheet has been hanged.

Thanks in advance for your help.

Sub Get_M_AgentDaily()
'' Dim theQueryText As StringDim theQueryTextcomp As String
Dim datestamp As String
Dim current_date As Date

ThisWorkbook.Activate
Sheets("SQL").Activate


Sheets("SQL").Select
Range("B3").Select

theQueryText = ""

While ActiveCell.Value2 <> ""
theQueryText = theQueryText + ActiveCell.Value2
ActiveCell.Offset(1, 0).Select
Wend

Sheets("AgentDaily").Select
Range("A5").Select
Range("A5:IV65536").ClearContents

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=CNWSQLH004P01" & ";APP=" & Fname & ";DATABASE=TAMI;Trusted_Connection=yes;" _
, Destination:=Range("A5"))
.Sql = theQueryText
.Refresh BackgroundQuery:=False

End With

End Sub

 

[Luke M]

How would you detect that the refresh "failed"?

All of the code example in the link would be installed in a regular module (from the VBE, Insert - Module). All of them use the idea of keeping track of the increments in your progress. For you, you said that you had 50 queries. Do you call them with a loop somehow, or are they all individually named?

 

[ramnaidu]

How would you detect that the refresh "failed"?

All of the code example in the link would be installed in a regular module (from the VBE, Insert - Module). All of them use the idea of keeping track of the increments in your progress. For you, you said that you had 50 queries. Do you call them with a loop somehow, or are they all individually named?

Hi Luke

All the queries have been named individually, I have written 50 different macros to run the queries and then I have written one more macro to run all the 50 macros. Hope this helps

 

[Luke M]

In that case, you're in for a bit of writing.
Before each of the 50 macro calls, you would have to update your status bar. I'd opt for the single line, something like

Dim i as Long
i = 0
'These next two lines get copied
i=i+1
Application.StatusBar = "Now running query " & i & " of 50..."

Copy the last two lines before each macro call.

 

[ramnaidu]

In that case, you're in for a bit of writing.
Before each of the 50 macro calls, you would have to update your status bar. I'd opt for the single line, something like

Dim i as Long
i = 0
'These next two lines get copied
i=i+1
Application.StatusBar = "Now running query " & i & " of 50..."

Copy the last two lines before each macro call.

Excellent... Luke this is what I want. Reallyu appreciate your time
Many thanks