VBA Excel 2003 - Method Resresh of object QueryTable failed

[Adalbert]

Zdravim,

vytvoril jsem si VBA makro na tahani informaci z Oracle DB do Excelu 2003.
Respektive nekolik maker, ktere se navzajem volaji a kazde z nich mi taha data na prislusna mista v excelovem sesitu.

Prvni makro taha z DB informace na zaklade promenne v bunce, kterou mohu zadavat rucne a ktere slouzi zaroven jako promenna pro SQL dotaz.

Problem je ten, ze spustim makro treba 10x a vse je OK a po jedenacte nastane err: Method Resresh of object QueryTable failed
Spustim li ho znovu pak err: Application defined or object defined error

Vypnu li cely excel a spustim sesit a makro znovu, opet nekolikrat probehne OK, ale drive nebo pozdeji opet err.
Kod uz jsem prepisoval tolikrat a bez vysledku, ze uz se obracim s prosbou na vas.

Pro ilustraci samozrejme prikladam kod:

Kód: Vybrat vše

Option Explicit


Sub Produkt()

Dim ID_produktu As Range, Vypis_produktu As Range
Dim strConn As String, strSql As String, qtProdukt As QueryTable

Set ID_produktu = Range("C4")
Set Vypis_produktu = Range("C20")



strConn = "ODBC;DRIVER={Oracle in XE};SERVER=server;UID=login;Pwd=password;DBQ=dbq;"
strSql = "select ...."


Application.ScreenUpdating = False

If Len(ID_produktu) = 18 Then


Set qtProdukt = ActiveSheet.QueryTables.Add( _
    Connection:=strConn, _
    Destination:=Vypis_produktu, _
    Sql:=strSql)
    
    With qtProdukt
        .Name = "Produkt"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
            
                If .Refreshing Then
                MsgBox "Aktualizace DB dotazu: prosím čekejte"
                Application.Wait (Now + TimeValue("0:00:03"))
                Else
                .Refresh BackgroundQuery:=False
                End If
    
    End With
   
Else
MsgBox ("Zadané číslo neodpovídá formátu ID_produktu")
End If
    
Set qtProdukt = Nothing
Set Vypis_produktu = Nothing
Set ID_produktu = Nothing
    
Application.Run "workbookname.xls!Module8.modulename"
    
End Sub

[cmuch]

Zkus za polední End IF dát Application.ScreenUpdating = True

Jinak nevím, jen zkouším.

[lubo.]

Chyba je nejspíš v

Kód: Vybrat vše

Set qtProdukt = ActiveSheet.QueryTables.Add( _

Při každém spuštění přidáš jednu tabulku... To by se z toho asi každý po čase zbláznil.

Zkus tabulku po vytvoření pojmenovat a při dalším spuštění zkontroluj, jestli ji už nemáš, pokud ano měl by stačit refresh.

ps.: Taky můžeš nejpvrve staré querytables vymazat

[Adalbert]

to lubo:

je to tak; uz V patek jsem si vsiml, ze velikost exceloveho sesitu mi vesele narusta, prestoze do nej neukladam zadna data z DB (pred skoncenim prace se sesitem vzdy cely sesit mazu).
Takze jsem po trose hledani nalezl, ze mi zustavaji jmena QT nekde v pozadi sesitu.
Pro ucel odmazavanni techto dat v pozadi jsem nasadil podpurne macro:

Kód: Vybrat vše

Sub NoNames()
  For Each n In ActiveWorkbook.Names
    n.Delete
  Next n
End Sub

Spolehlive odstranuje vsechny pojmenovane QT.

Dnes budu testovat stabilitu sesitu.
Kazdopadne vyzkousim i cestu kontroly pojmenovanych QT a reseni prostrednictvim .Resresh

[lubo.]

Spíš bych zkusil něco jako:

Kód: Vybrat vše

Dim qt As QueryTable
For each qt in ActiveSheet.QueryTables
    qt.Delete
Next

Ty jména obsahují i jiné údaje, jejich totální výmaz nemusí být nejlepší nápad.

[Adalbert]

Ano souhlasim, ze totalni vymaz vsech jmen je trochu humpolacky reseni, nicmene pro muj pripad dostacujici.
Zadna jina data do jmen muj sesit neuklada, tedy si to mohu dovolit.
Zaroven se take pripojuji do DB pres QT, ktere se mimo jine spousteji v cyklu For Each .... Next
a do Jmen se mi tedy generuji QT se jmeny, ktere si automaticky excel vygeneruje (pridanim cisla za opakovany QT).
Pokud bych potreboval mazat ze Jmen pouze konkretni QT, musel bych rozsirit kod na odmaz take o cyklus, ktery mi vybere vsechny QT, nejen mnou natvrdo definovane, ale i ty ktere si excel sesit sam zapise s vygenerovanym poradovym cislem.


Kazdopadne po osetrni odmazu QT je kod jiz funkcni a bez padu.