QueryTables, (en svår fråga)

[Monshi]

Ok, nu sitter jag här med QueryTables igen, webbfrågor.

 

De är buggiga, jag vet, men kanske någon kan finna en väg runt denna bugg?

 

Jo, jag försöker hämta hem en större mängd data, 13 områden om jag ska vara exakt. Ni som känner mig vet nog vad jag försöker hämta för data

 

För att hålla ned storleken på arbetsboken och även göra det enklare med formlerna vill jag att dessa frågor laddas på samma område på bladet. (Har tänkt extrahera datat jag vill ha och sedan kopiera värdena till ett annat utrymme i boken.)

 

Givetvis tänker jag då omedelbart på möjligheten att i VBA ändra adressen QueryTable hämtar sina data från med:

qt.EditWebPage = "http://www.svenskaspel.se/includes/xmlelements/XMLmatchinfo.asp?matchID=93227"

och sedan köra en refresh:

qt.Refresh

 

där qt är min QueryTable.

 

Problemet är att qt hämtar sina data från den gamla adressen, inte den nya. Om jag emellan dessa två rader skriver

MsgBox qt.EditWebPage

så får jag upp den nya adressen, men inte hämtar den data därifrån. Enda sättet jag funnit att "aktivera" den nya adressen är genom att öppna dialogen "Redigera fråga", där står nya adressen i adresseraden, och importera.

 

Det borde räcka med EditWebPage tycker jag.

 

Någon som vet vad jag ska testa härnäst? Funderar på att radera frågan och skriva det en ny med en ny adress kanske... mmmh, men det uppstår då kanske lite andra problem med formlerna jag tänkt använda... Tips mottages med glädje.

 

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

 

[inlägget ändrat 2005-04-16 15:08:00 av Monshi]

[XL-Dennis]

Hej Monshi,

 

Jag tittade in här på E-forum och så såg jag denna fråga.

 

Vad tror du om följande upplägg (utifrån den uppfattning jag har efter att ha läst igenom din post):

 

[color="#0000ff"]Option Explicit[/color]

[color="#0000ff"]Sub[/color] Retrieve_Data_Query_From_Web()
[color="#0000ff"]Dim[/color] wsSheet As Worksheet
[color="#0000ff"]Dim[/color] qt As QueryTable
[color="#0000ff"]Dim[/color] vaURL() As Variant
[color="#0000ff"]Dim[/color] i As Long

[color="#0000ff"]Set[/color] wsSheet = ActiveWorkbook.Worksheets(1)

[color="#006400"]'Här får du addera dina länkar.[/color]
vaURL = VBA.[color="#0000ff"]Array[/color]([GRÅ]"URL;http://biz.yahoo.com/p/advertconameu.html"[/GRÅ], _
                  [GRÅ]"URL;http://biz.yahoo.com/p/advertconameu.html"[/GRÅ], _
                  [GRÅ]"URL;http://biz.yahoo.com/p/advertconameu.html"[/GRÅ])

[b]Application[/b].ScreenUpdating = [color="#0000ff"]False[/color]

[color="#0000ff"]For[/color] i = 0 [color="#0000ff"]To[/color] [color="#0000ff"]UBound[/color](vaURL)
    [color="#0000ff"]Set[/color] qt = wsSheet.QueryTables.Add( _
                     Connection:=vaURL(i), _
                     Destination:=wsSheet.Cells(1, i + 1))
    [color="#0000ff"]With[/color] qt
        .BackgroundQuery = [color="#0000ff"]True[/color]
        .WebFormatting = xlNone
        .WebSelectionType = xlSpecifiedTables
        .WebTables = [GRÅ]"5"[/GRÅ]
        .Refresh BackgroundQuery:=[color="#0000ff"]False[/color]
    [color="#0000ff"]End[/color] [color="#0000ff"]With[/color]
[color="#0000ff"]Next[/color] i

[b]Application[/b].ScreenUpdating = [color="#0000ff"]True[/color]

[color="#0000ff"]End[/color] [color="#0000ff"]Sub[/color]

 

Sedan hoppas jag innerligen att MSFT uppdaterar detta verktyg avsevärt i nästa version!

 

 

Mvh

Dennis

 

För tips och tricks med MS Excel se: http://www.xldennis.se

 

[Monshi]

Tack Dennis för ditt försök. Får testa det när jag sitter med aktuella arbetsboken igen. Påminner om vad jag redan försökt fast med en viss "dialektal" skillnad. Har testat med

qt.Connection = "URL;...." och

qt.EditWebPage="..."

 

men inte att skapa om frågorna vid varje körning. Det borde fungera men med den nackdelen att mina formler på arbetsbladet inte kan arbeta med ett statistkt namngivet område.

 

Får testa. Har som sagt egentligen tänkt att enbart ha en fråga för dessa data i arbetsboken. Jag uppdaterar frågan med en ny adress och sedan kör igen. Mellan körningarna har jag tänkt extrahera de data som önskas och placera dem i en snygg/praktisk tabell där det är lätt och snabbt att presentera data ifrån. Dessutom ändras adresserna mellan varje omgång, varje tipsomgång. Arbetsboken blir även ganska stor, frågorna ökar på den cirka 1 MB. Stor? nja, kanske inte förresten... men större än vad den är nu.

 

 

Lite mer om mitt spännande upplägg vad gäller QueryTables:

QueryTables har en tendens att "hänga" sig om inte webbservern i andra änden korrekt terminerar kontakten, dvs säger att "nu har jag sänt allt". Standardtimeouten på en Windowsmaskin, den tid Windows väntar på termineringen, är väl någonstans mellan 10 och 30 minuter?

 

Nåja, ivartfall kunde frågorna få min arbetsbok att stanna upp, tillsynes ha hängt sig. Väntade man tillräckligt länge så kom den tillbaka. Inte bra.

 

Lösning:

En ny klass, en utvidgning av QueryTable. När en fråga som är kopplad till denna klass körs exekveras även en rad med Application.OnTime. Application.OnTime exekverar då en proceduer 30 sekunder senare som kontrollerar om frågan är klar eller ej och terminerar den om den inte är det.

 

Det fula:

Är att proceduren som terminerar ligger utanför klassen. Har inte lyckats få OnTime att vare sig anropa en funktion inom klassen eller sända med argument som identifierar vilken fråga det gäller.

 

 

Sitter för övrigt på Excel2002. Lika usel funktion i senare versioner av Excel?

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

[XL-Dennis]

Hej igen,

 

Det borde fungera men med den nackdelen att mina formler på arbetsbladet inte kan arbeta med ett statistkt namngivet område.

 

Det är oklart vad du menar med detta. Kan du inte använda dig av dynamiska namnområden?

 

Per se är inte Querytable designad för modern data(bas)hantering då den saknar egenskapen Timeout (finns och används flitigt i ADO).

 

Du kan därför överväga att använda dig av en mer databas-anpassad ansats men det beror också på hur och i vilket sammanhang du hämtar data för. Datahämtning från webben är i sig ingen höjdare så överarbeta inte det hela i din strävan att få till en kanon-lösning.

 

Excel 2003 och version 12 bjuder inte på några nyheter när det gäller QueryTable, vilket är ett ytterligare skäl till att flytta över till ADO /ADO.NET

 

Mvh

Dennis

 

För tips och tricks med MS Excel se: http://www.xldennis.se

 

[Monshi]

Det är oklart vad du menar med detta. Kan du inte använda dig av dynamiska namnområden?

ja, allt går att anpassa.

Min enkla idé var dock att ha en uppstättning formler på bladet som jag sedan enkelt, genom namngivna områden, kan hämta datat ifrån med VBA och klistra in i annat statiskt område. Formlerna i sig arbetar då med det namngivna området en fråga skapar. Formlerna kan jag, precis som du säjer, lätt uppdatera med ett nytt område. Det går att lösa.

 

Om du tar en titt på adressen om jag givit i första inlägget, en adress Eforum som vanligt har delat upp i småbitar, ser du att det är en ganska ordnad struktur på datat, XML-data, men med mycken redundant och ointressant information.

 

Du kan därför överväga att använda dig av en mer databas-anpassad ansats men det beror också på hur och i vilket sammanhang du hämtar data för.

Ytterligare en (meningslös) funktion i mitt tipsark.. Hämtar XML-filer med match- och laginformation från Svenska Spel. Kanske du har en annan idé om hur jag ska traversera dem och extrahera datat?

 

ADO har jag ej tittat på och har ingen kunskap om och kanske lite väl att dyka ned i det bara för detta. Vet inte hur mycket arbete dessa nya funktioner är värda.

 

 

 

hehe, nästa steg för mig med detta ark är för övrigt att ordna så Excel kan ladda upp filer till Svenska spel också. Det kan bli en rejäl nöt att knäcka kan jag tro, ivartfall för mig. Har ett brev (hemma) där de beskriver hur detta ska gå till... Om du vill hjälpa till kan jag återge vad de skriver. Du kan enkelt säga möjlig, svårt eller omöjligt. Misstänker att svaret kommer ligga kring svårt/omöjligt.

 

tackar för svaren. Alltid trevligt att se dig här. Och faktiskt trevligt att du lämnar de enkla frågorna till mig, BI, MH mfl...

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

 

[inlägget ändrat 2005-04-19 13:49:16 av Monshi]

[XL-Dennis]

Hej igen!

 

Hämtade hem data utifrån adressen viket gick snabbt. Utöver redudant data erhålls också XML-överflödig information. Enklast är som du gör idag, dvs hämta data och städa upp i Excel.

 

Alternativet är att skapa ett XML-schema som styr hur data ska visas och vad som ska visas men innan en acceptabel lösning erhålls så har några timmar åtgått och håret ändrat färg...

 

Du kan enkelt säga möjlig, svårt eller omöjligt. Misstänker att svaret kommer ligga kring svårt/omöjligt.

 

Nä, med litet hjälp och en genomtänkt plan går det mesta .

 

tackar för svaren. Alltid trevligt att se dig här. Och faktiskt trevligt att du lämnar de enkla frågorna till mig, BI, MH mfl...

 

Ni gör ett bra jobb här på E-forum och dessutom har Ni successivt höjt Er kunskapsnivå och skicklighet så det är roligt att besöka E-forum och se denna utveckling.

 

Ni förtjänar ett stort tack och uppskattning från alla besökare till E-forum:thumbsup:.

 

Efter 20 år med Excel är det inte lika roligt längre så jag har börjat lägga tid och kraft på VB.NET / XML / ADO.NET och SQL Server 2000 / 2005, där jag gladeligen klassificerar mig som nybörjare (nästan)!

 

 

Mvh

Dennis

 

För tips och tricks med MS Excel se: http://www.xldennis.se

 

[Monshi]

Hämtade hem data utifrån adressen viket gick snabbt. Utöver redudant data erhålls också XML-överflödig information. Enklast är som du gör idag, dvs hämta data och städa upp i Excel.

 

japp, det går snabbt och fungerar för det mesta. Tyvärr strular Webb-querys lite då och då vilket kräver den extra termineringen om ex.v. internetkontakt saknas eller försvinner.

När Excel importerar datat blir det en ganska enkel struktur på arbetsbladet. Excel tar varje XML-post och ger det en kolumn med elementets namn som rubrik rad. Detta ger ett ormåde som det är enkelt att söka på och med ett enhetligt utseende mellan olika lag/matcher.

 

Återkommer med några meningar ur det Word-dokument Svenska Spel sände mig när jag har tillgång till det (ikväll eller imorgon).

 

Ska testa vidare med Querytables när jag har tiden nästa gång. Synd att du inte kom med en superlösning som gör det möjligt att ange en ny adress till en existerande fråga. Kommandot finns i VBA men det fungerar inte. Suck. På något sätt borde det gå att komma runt och få det att fungera.

 

 

Kan visserligen acceptera att skapa en ny fråga varje gång data ska hämtas.

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

[XL-Dennis]

Hej Monshi,

 

EditWebPage verkar endast fylla funktionen att tilldela textboxen för Webbquery ett värde men ingeting annat. Det du kan försöka komplettera din EditWebPage ansats är att spara arbetsboken innan du exekverar denna del av koden.

 

Superlösningar finns det gott om! Det gäller bara att hitta dem

 

Ser framemot del 2 av denna fråga.

 

 

Mvh

Dennis

 

För tips och tricks med MS Excel se: http://www.xldennis.se

 

[Monshi]

Mmh, spara emelllan...mmh, låter lite omständigt. BORDE finnas en lösning på detta. MS borde ha märkt detta problem... En klar bugg!

Nä, då föredrar jag nog att återskapa frågan varje gång den exekveras isådanafall.

Skapa och hämta data utan att spara frågan, extrahera data, skapa igen osv.

Blir nog till helgen som jag testar detta.

 

Del 2 - Ok, den kommer ikväll.

 

Edit:

Ok, ska väl läsa hjälpen en gång till:

Remarks

 

The EditWebPage property returns Null if not set. The EditWebPage property is only meaningful if the query type is Web or OLE.

 

If the EditWebPage is not null then ignore the WebTables property for refreshing. As a result an XML query and the WebTable property refers to the table in the original Web page and should only be used in the edit case to pre-populate the Web Query dialog box.

Här står det visst, om jag tolkar det rätt, att EditWebPage inte kan göra något meningsfullt..

 

Fast, mmh, jag får kanske försöka en gång till med:

QueryTable.Connection

Den borde ju fungera, tror jag...

Returns or sets a string that contains one of the following:.... a URL that enables Microsoft Excel to connect to a Web data source

 

Fast jag misslyckades när jag försökte i helgen...

 

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

 

[inlägget ändrat 2005-04-20 08:38:26 av Monshi]

 

[inlägget ändrat 2005-04-20 08:48:09 av Monshi]

[Monshi]

Inser att den information om möjligheten att sända upp egna rader som jag har är ganska kortfattad. Trodde det var med. Kanske får sända iväg ett brev och fråga de ansvariga om lite vidare specifikationer. När jag fick denna info var tjänsten även ganska ny. Nåja, det går kanske säga om det är möjligt eller utifrån denna information:

Den nya tjänsten att kunna skicka upp egnarader direkt utan att gå via en fil är en lösning där applikationen gör en HTTP-POST med en XML-fil till en viss URL hos oss. I det XML-svar som kommer tillbaka finns information samt en länk dit man ska gå för att kvittera raderna.

 

Säger det dig något?

 

XML-fil - ja det är möjligt.

HTTP-post - aldrig undersökt eller ens någon aning om hur.

XML-svar - gäller alltså ett svar på HTTP-POSTen...

 

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

[XL-Dennis]

Hej,

 

Det var väl i kortaste laget...

 

#1 XML-fil

Det går att skapa en s k rowset-baserad XML-fil och det finns ett exempel på min sida.

 

#2 HTTP & Post

Här visas ett exempel men min erfarenhet säger att du får vara beredd på att jobba vidare med det beroende på utfallet vid dina test:

 

[color="#0000ff"]Option Explicit[/color]

[color="#0000ff"]Sub[/color] Post_XMLHTTP()
[color="#006400"]'Referens till Microsoft XML x.x via Verktyg | Referenser...[/color]
[color="#006400"]'Här används version 5.0[/color]
[color="#0000ff"]Dim[/color] objXML As MSXML2.DOMDocument50
[color="#0000ff"]Dim[/color] objHTTP As MSXML2.XMLHTTP50

[color="#0000ff"]Dim[/color] stFileName As [color="#0000ff"]String[/color]

stFileName = [GRÅ]"c:\Tips.xml"[/GRÅ]

[color="#0000ff"]On Error[/color] GoTo Error_Handling

[color="#006400"]'Instansierar objekten.[/color]
[color="#0000ff"]Set[/color] objXML = [color="#0000ff"]New[/color] MSXML2.DOMDocument50
[color="#0000ff"]Set[/color] objHTTP = [color="#0000ff"]New[/color] MSXML2.XMLHTTP50

[color="#006400"]'Läser in fil.[/color]
objXML.Load (stFileName)

[color="#006400"]'Sänder filen och hämtar status.[/color]
[color="#0000ff"]With[/color] objHTTP
    .Open [GRÅ]"POST"[/GRÅ], [GRÅ]"https://www.doman.se/upload.asp"[/GRÅ], [color="#0000ff"]False[/color]
    .setRequestHeader [GRÅ]"Content-Type"[/GRÅ], [GRÅ]"text/xml"[/GRÅ]
    .send objXML.XML
    [color="#0000ff"]If[/color] .Status = 200 [color="#0000ff"]Then[/color]
        [color="#0000ff"]MsgBox[/color] [GRÅ]"Klart!"[/GRÅ], vbInformation
    [color="#0000ff"]Else[/color]
        [color="#0000ff"]MsgBox[/color] [GRÅ]"Ett fel har inträffat: "[/GRÅ] & .statusText, vbInformation
    [color="#0000ff"]End[/color] [color="#0000ff"]If[/color]
[color="#0000ff"]End[/color] [color="#0000ff"]With[/color]

ExitSub:
[color="#0000ff"]Set[/color] objHTTP = [color="#0000ff"]Nothing[/color]
[color="#0000ff"]Set[/color] objXML = [color="#0000ff"]Nothing[/color]
[color="#0000ff"]Exit[/color] [color="#0000ff"]Sub[/color]

Error_Handling:
[color="#0000ff"]MsgBox[/color] [GRÅ]"Ett fel har inträffat :"[/GRÅ] & Err.Description & [GRÅ]" "[/GRÅ] & Err.Number, vbCritical
Resume ExitSub
[color="#0000ff"]End[/color] [color="#0000ff"]Sub[/color]

 

#3 XML-svar

Får du en fil som en bekräftelse?

 

 

Mvh

Dennis

 

För tips och tricks med MS Excel se: http://www.xldennis.se

 

[Monshi]

Tackar för förslaget. Jag har ju ännu inte testat, eller ens försökt, implementera denna funktionalitet. Har sänt iväg ett brev till Svenska Spel där jag ber om lite mer detaljerad funktionsbeskrivning. Måste ju veta hur XML-filen ska se ut, vilken adress jag ska skicka den till samt vad jag får för något tillbaka. Misstänker även att jag måste få en "egen" port/inloggning hos Svenska Spel som identifierar mitt program.

 

Tackar för grundkoden. Jag lovar inget snabbt resultat av detta, arket är ett projekt som tar sin tid och saker får mogna. Men under helgen ska vi se om jag inte kan ge det några timmar.

 

Jag lovar att återkomma med mer data eller med resultat.

 

Tackar

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

[Monshi]

Dåligt med svar från Svenska Spel, har tyvärr ingen mer information om möjligheten att ladda upp egna rader/system...

 

Hoppas det svarar snart.

 

Men - jag har fått ordning QueryTable frågorna ivartfall. Metoden

qt.Connection = "URL;...."

 

fungerar. Anledningen till att den inte fungerade för mig förut var att jag körde frågan i bakhrunden och min VBA-kod försökte ställa om, Webbfrågans adress medans den körde.

 

Så nu har jag en Webbfråga som sekventiellt, men hjälp av VBA, kan hämta data från olika adresser. Fungerar bra.

 

Nu är bara problemet det tat ta hand om all denna information och presentera på något enkelt vis.

 

 

/T

 

Even when we know we´ll never find the answers, we have to keep on asking questions.

 

[XL-Dennis]

Hej,

 

Det går åt rätt håll

 

 

Mvh

Dennis

 

För tips och tricks med MS Excel se: http://www.xldennis.se