Stuck on Excel Public WithEvents QT AS QueryTable

I have been trying to understand this, but I am not getting it to work.
http://msdn.microsoft.com/en-us/library/office/ff837065.aspx
Public WithEvents qtQueryTable As QueryTable

Sub InitQueryEvent(QT as Object) 
 Set qtQueryTable = QT 
End Sub

Dim clsQueryTable as New ClsModQT 
 
Sub RunInitQTEvent 
 clsQueryTable.InitQueryEvent _ 
 QT:=ActiveSheet.QueryTables(1) 
End Sub

Open in new window

My goal is to use QueryTable_AfterRefresh(Success As Boolean)
http://msdn.microsoft.com/en-us/library/office/ff835922.aspx
Private Sub QueryTable_AfterRefresh(Success As Boolean) 
 If Success Then 
 ' Query completed successfully 
 Else 
 ' Query failed or was cancelled 
 End If 
End Sub

Open in new window


to run another sub Function each time a certain worksheet querytable is updated.

I have found many examples, but I am not getting it.

I assume this is pretty straight forward.
LVL 27
yo_beeDirector of Information TechnologyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

I have attached an example workbook that uses a QueryTable linked to this thread.

The code within the (This)Workbook code module is as follows:

Option Explicit

Dim clsQueryTable                                       As New clsModQT
Sub RunInitQTEvent()
  
  clsQueryTable.InitQueryEvent QT:=Worksheets("Q_28246274").QueryTables(1)

End Sub
Private Sub Workbook_Open()

  Call RunInitQTEvent
  
End Sub

Open in new window


The code within the Class Module, "clsModQT", is as follows:

Option Explicit

Public WithEvents qtQueryTable                          As QueryTable
Sub InitQueryEvent(QT As Object)
 
  Set qtQueryTable = QT

End Sub
Private Sub qtQueryTable_AfterRefresh(ByVal Success As Boolean)

  If Success Then
' Query completed successfully
     MsgBox "Success!"
  Else
' Query failed or was cancelled
     MsgBox "Failed"
  End If

End Sub

Open in new window



You can see I have simply used two message boxes to display the success or failure of the refresh of the QueryTable.  You can replace these with your code (calling a further routine, if necessary).

BFN,

fp.
Q-28246274.xls
0
yo_beeDirector of Information TechnologyAuthor Commented:
I am getting a runtime error on my excel workbook.
Img1
I have two Queries
AccountsPayable
AccountsPayable1

Could that be the cause?
0
[ fanpages ]IT Services ConsultantCommented:
Possibly... although quoting the actual error text may help diagnosis.

I do note, however, that you changed the worksheet name (from the example I provided).  Is the name you have used correct for your workbook?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

yo_beeDirector of Information TechnologyAuthor Commented:
Yours works perfectly, but when I try and apply the code to my xlsm that is when I get the error. I will post more details later this evening. Thanks for your prompt replies.
0
[ fanpages ]IT Services ConsultantCommented:
OK.  Thanks for letting me know.
0
yo_beeDirector of Information TechnologyAuthor Commented:
here is the error
SUBSCRIPT Out Of Range.
img2
0
[ fanpages ]IT Services ConsultantCommented:
A "Subscript out of range" error could be one of two possibilities (as discussed above):

a) The worksheet name is incorrect; [Images].  The case used is not important ("IMAGES" is the same as "Images", & that is the same as "ImAgEs", & so on), but check that there are no leading (prefix) spaces ("  Images"), &/or trailing (suffix) spaces ("Images  "), in the name of the worksheet.

b) There are not two QueryTables defined on the [Images] worksheet, so use of QueryTables(2) would be outside of the range (one, maximum) of the number of QueryTables available.  Try replacing with QueryTables(1) & trying again.  If that works, then you know the reference to the worksheet name ("Images") is correct.
0
Rory ArchibaldCommented:
If you are using an xlsm file, it is likely that you have a Table based on your query, in which case you need to access the Querytable as a property of the Listobject using something like:

clsQueryTable.InitQueryEvent QT:=Worksheets("Images").Listobjects(2).QueryTable

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yo_beeDirector of Information TechnologyAuthor Commented:
Thanks for all the help.
It was the last code snippet you posted that resolved the issue.

So with a combo of the original code and the last code it worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.