OLE DB query and QueryTables object

Hi,

there is a sheet containing external data from an OLE DB query.
I need to catch the „after refresh“ event and this works only for the QueryTables object, as far as I know.

Unfortunately there is no QueryTable object on this sheet, a simple „MsgBox Sheets("MySheet").QueryTables.Count“ returns „0“.

Root of my issue is the requirement to have a working Hyperlink()-formula inside the queryied data.
The formula is created correctly by SQL Server, but will allways be displayed as text in the excel sheet.
A small VBA routine is supposed to transform cell content from text to formula. This should happen after every refreshment.

Thanks in advance for any help.

Bests,
Matze

Thanks for reply,
but it returns an error, number 1004

Yes, the query delivers proper results.
In online help I found something about an object OLEDBConnection.

Funny to see there a two ribbons "Queries" and "Connections".
The linked views are listed under Queries

Yes, this is where I come from.

Found a solution for that in stackoverflow, but it does not work
beacause on Workbook_Open() no Querytable can be found:


______________________________________________________________________________________
Class Modul

Private WithEvents MyQueryTable As QueryTable

Private Sub MyQueryTable_AfterRefresh(ByVal Success As Boolean)
If Success Then
MsgBox "done!"
Else
MsgBox "kaboom!"
End If
End Sub

Friend Sub HookUpQueryTable(qt As QueryTable)
Set MyQueryTable = qt
End Sub

______________________________________________________________________________________
Workbook

Dim c As Class1

Private Sub Workbook_Open()

On Error Resume Next

Set c = New Class1
c.HookUpQueryTable Sheets("Verladungen").QueryTables(1)

End Sub

Thank you very much.

It was a great help!