Estrazione dati dal sito dinamico

[davide2574]

Salve a tutti

Mi sono iscritto qui perché ho trovato le cose interessanti per estrazioni dati che da molto tempo cercavo di capire.
Ho letto appunto questa discussione:

viewtopic.php?t=101069

cosi ho incollato e cambiando l'indirizzo del link e inserito il foglio per aggiornare nello stesso foglio invece di altro foglio



Sub GetTabbb()
'
myURL = "http://stats.betradar.com/s4/?clientid=1271&language=en#2_4,3_37,22_2,5_11202,9_fixtures,231_full,23_3"
Set ie = CreateObject("InternetExplorer.Application")

'Sheets("Foglio1").Select
'Range("A:C").Clear

With ie
.navigate myURL
.Visible = True
Do While .Busy: DoEvents: Loop 'Attesa not busy
Do While .readyState <> 4: DoEvents: Loop 'Attesa documento
End With
'
myStart = Timer 'attesa addizionale
Do
DoEvents
If Timer > myStart + 2 Or Timer < myStart Then Exit Do
Loop

'Leggi le tabelle, su un nuovo foglio
Sheets("Ger2").Select
'Cells.Clear
Set mycoll = ie.document.getElementsByTagName("TABLE")
For Each myItm In mycoll
For Each trtr In myItm.Rows
For Each tdtd In trtr.Cells
Cells(I + 1, J + 1) = tdtd.innerText
J = J + 1
Next tdtd
I = I + 1: J = 0
Next trtr
I = I + 1
Next myItm

Stop 'Vedi testo

'Chiusura IE
ie.Quit
Set ie = Nothing
End Sub


Mi restituisce tutti i dati ma solo il problema invece di darmi questi risultati

2:0 2:2 1:0 5:2

ma mi da questo

02:00 02:02 01:00 05:02

ho provato a modificare nel formato ma il problema se devo estrarre con la stringa.estrai e mi restituisce sempre quel time 02:00
Poi un'altra cosa , se devo inserire i diversi campionati nei fogli devo mettere i codici vba per ogni foglio che ha il suo sito con statistica di un campionato?

X Antonhy

ti ringrazio molto per avermi risposto, ecco ci provo a scrivere qui. Nella mia speranza che ci riesco a scrivere senza problema.
Cmq volevo dire che li faccio aggiornamento il giorno dopo e non ogni minuto.
Grazie mille.
Ciao

Davide

[Anthony47]

Ciao Davide, benvenuto nel forum.

Per impedire che Excel interpreti stringhe quali "2:0" come orari (e altre interpretazioni qualora ci sia lo slash (/) o il punto (.) o altri caratteri che per Excel hanno un significato peculiare, dovrai formattare l'intero foglio Ger2 (o almeno le colonne che sai saranno usate per i dati importati) come Testo.

Ciao

[davide2574]

Grazie Anthony per la risposta.
MA se devo impostare come Testo cioè selezionare tutto il foglio ed a impostare come Testo. Ma se li importo sicuramente tornerà come prima. Devo provare.
Il problema è che se Testo e diventa più complicato a fare le formule di calcolo ed anche impossibile ad usare con stringa.estrai.
Per quanto riguarda l'aggiornamento di ogni foglio che ha un suo campionato. é possibile aggiornare tutti i fogli con il tasto F5 oppure ogni foglio devo fare con F5? Perchè ho inserito i codici ogni foglio.

[Anthony47]

La macro importa stringhe di testo, non formati; e' excel che di fronte a una stringa "2:0" lo interpreta come Orario; ma non lo fara' se hai detto che la cella e' Testo.
Quanto all'uso dei valori importati in altre formule devi stare attento a che cosa viene importato; es 2.3 forse sul sito voleva essere il valore 2,3 mentre 2,123.11 probabilmente voleva essere 2123,11. Quindi devi pulire i caratteri inutili (es la "virgola" come separatore delle migliaia) e sostituire i caratteri che hanno una equivalenza (tipo il "punto" con la "virgola" dei decimali); per questo userai, la funzione Sostituisci.

Nella maggioranza dei casi, stringhe cosi' modificate sono correttamente usabili in operazioni numeriche successive.
Esempio data la stringa "2-3" di risultato di una partita in B20, puoi calcolare la differenza reti con la formula

Codice: Seleziona tutto

=SOSTITUISCI(SINISTRA(B20;2);"-";"")-SOSTITUISCI(DESTRA(B20;2);"-";"")


pur essendo i due termini delle stringhe (ma interpretabili come numeri)

In altri casi devi esplicitamente costringere Excel a cambiare le stringhe in numeri; ad esempio se vuoi trasformare quel risultato in 1-x-2 lo potrai fare con la formula

Codice: Seleziona tutto

=SE(1*SOSTITUISCI(SINISTRA(B20;2);"-";"")>1*SOSTITUISCI(DESTRA(B20;2);"-";"");1;SE(1*SOSTITUISCI(SINISTRA(B20;2);"-";"")=1*SOSTITUISCI(DESTRA(B20;2);"-";"");"x";2))


In questa formula 1*(una stringa) serve per convertire forzatamente il valore rappresentato dalla stringa nel valore numerico stesso, perche' il simbolo ">" lavora anche sulle stringhe utilizzando pero' un confronto alfabetico invece che numerico (per cui "12" viene prima di "2")
Ambedue queste formule lavorano bene con contenuti del tipo 2-3, 02-03, 2 - 3, 10-0, 10 - 0, 5-10.

Quanto alla Stringa.Estrai, e' una funzione che opera sui testi, quindi avendo formattato il foglio come "Testo" dovresti avere la vita smplificata.

Comunque faccio mio il motto di un amico del forum che si firma "provare e riprovare e' l'unico modo per imparare"

Quanto all'aggiornamento del contenuto, F5 non avvia nessuna macro quindi con F5 non produrrai nessun aggiornamento.
A scelta:
1) aggiorni il contenuto "del foglio" tutte le volte che lo attivi; per questo:
--tasto dx sul tab col nome foglio, scegli Visualizza codice
--incolli questa macro nel frame vuoto che viene aperto:
Private Sub Worksheet_Activate()
Call LaMacroChImportaLaPagina
End Sub
2) aggiorni "i fogli" tutte le volte che apri il file
--da Excel premi Alt-F11 per aprire l'editor delle macro
--localizzi sul frame Progetto-VBAProgetto a sinistra il modulo ThisWorkbook (Questa-Cartella_di_Lavoro), fai doppioclick per espanderlo e ci inserisci questo codice
Private Sub Workbook_Open()
Call LaMacroChImportaLaPagina1
Call LaMacroChImportaLaPagina2
'etc etc
End Sub
3) considerando il tempo necessario all'aggiornamento delle tabelle, puo' essere infine conveniente inserire un pulsante su ogni foglio e associargli LaMacroChImportaLaPagina; cosi' quando ti sembra che sia il caso di aggiornare la pagina premi il pulsante e aspetti il completamento della macro.

Ciao

[davide2574]

grazie Antonhy sei stato molto gentile a spiegare.
Ma il problema che ho notato che quando si importa i dati nel formato di cella passa automaticamente da Generale a Personalizzato su hh:mm

cosi le tue formule non riesce a funzionare a causa quel formato.

PEr l'aggiornamento devo provare quando sarò a casa dal lavoro.

[Anthony47]

Non ho capito se ha provato a impostare il foglio come Testo prima di eseguire la macro, per impedire che Excel scelga il formato delle informazioni che si va a importare.

Ciao

[davide2574]

si ho provato il testo nelle celle prima di fare la macro e ha funzionato ma se
nella cella C2 0:1 D2 1:1 ed vorrei estrarre nella cella F2 0 G2 1 H2 1 I2 1 cosi via

ho provato ad usare la stringa.estrai ma non funziona. perchè la stringa.estrai non riconosce il formato testo mentre generale o numeri si. forse avrò sbagliato qualcosa.

[Anthony47]

ho provato ad usare la stringa.estrai ma non funziona. perchè la stringa.estrai non riconosce il formato testo mentre generale o numeri si

La funzione Stringa.Estrai per definizione lavora su testi, quindi il discorso non regge.
Dicci con quali formule provi a fare quelle estrazioni.

Ciao

[davide2574]

Ecco come potrai vedere le immagini che prima ho fatto inserire "testo" in tutte celle e poi importato e poi ho scritto =stringa.estrai vedi quello evidenziato verde e avrebbe dovuto apparire numero 9 e invece non lavora e rimane aperto la formula.



come vedi la formula che ho inserito e dovrebbe apparire numero 9 e invece è rimasto visibile la formula non mi fa estrarre.

[Anthony47]

Ti ho portato fuori strada...
Perche' una cella possa contenere una formula quella cella NON DEVE essere formattata Testo.
Quindi se le tabelle importate occupano mettiamo le colonne A:Q allora solo queste colonne devono essere formattate Testo, e le altre devono rimanere "Generale"

Ciao

[davide2574]

ho già provato a mettere anche il formato Generale nelle colonne dalla M alla P e poi inizio ad importare quando ha finito di importare il formato si è modificato da solo e spostato dalla Generale alla Data *14/03/2001 cosi sballa tutti i numeri.

[Anthony47]

Devi formattare Testo le colonne che saranno occupate dalle tabelle importate e invece Generale per quelle colonne che non sono interessate dall'importazione e che vuoi usare per contenere formule. Tieni presente che "Generale" significa "nessuna formattazione preimpostata", quindi se poi nella cella formattata "Generale" metti una data Excel la visualizzera' come una data; se metti un orario Excel lo visualizzera' come orario.

Per prova ho usato la macro pubblicata nel tuo primo messaggio.
Facendo una importazione da sito il risultato e' come da immagine:

upload
Cioe' invece dei punteggi tipo 0:1 1:1 mi riporta degli orari, come si vede anche nella barra della formula.

Ho formattato le colonne A:F come Testo e ho rilanciato la macro; ho ottenuto questo risultato:

free image hosting
Le celle ora sono formattate Testo, e in G2-H2 ho estratto i singoli punteggi usando rispettivamente le formule

Codice: Seleziona tutto

=SINISTRA(C2;1)

Codice: Seleziona tutto

=DESTRA(C2;1)

(sono formule solo dimostrative, la formula finale deve essere piu' complessa per gestire ad esempio risultati >9 e altre indicazioni che talvolta vedo nelle caselle)

Se usi un'altra macro pubblicane il codice e vedremo se al suo interno ci sono istruzioni che alterano il formato.

Ciao

[davide2574]

Ciao Anthony

scusami se non ti ho risposto perché ero impegnato.
Allora ti ringrazio molto per avermi spiegato. E poi ho provato a cercare ugualmente se c'era anche il codice più semplice nel vba e ho trovato questo sito che è molto interessante e potrebbe interessare a qualcuno che cerca.

Function EXTRACTELEMENT(Txt, n, Separator) As String
EXTRACTELEMENT = Split(Application.Trim(Txt), Separator)(n - 1)
End Function

nel sito : http://spreadsheetpage.com/index.php/ti ... xcel_2010/

per quanto riguarda l'aggiornamento, invece di aprire la cartella e si parte aggiornamento automatico che hai suggerito:
Private Sub Workbook_Open()
Call LaMacroChImportaLaPagina1
Call LaMacroChImportaLaPagina2
'etc etc
End Sub

Se invece voglio fare il manuale con l'aggiornamento quando voglio basta un clik:


Application.Goto Reference:="Aggiorna"

'questi due ho usato con query data di excel
Range("Z3").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

Range("DB1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
'questo ho usato per estrazione dati in vba
Worksheet("Foglio1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False


Risposta = MsgBox("Aggiornamento effettuato")
End Sub



questo codice:
Worksheet("Foglio1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

è adatto a quei codici per estrazione?
Ho usato due diversi estrazioni con dataquery e codice vba
Grazie ancora.
Ciao

Davide

[Anthony47]

La funzione EXTRACTELEMENT che hai pubblicato puo' servire per fare da vba la separazione del contenuto delle celle, una volta che il contenuto delle celle e' corretto; utile se le componenti sono tante.

Quanto all'aggiornamento dei dati estratti dal sito, se usi la web query potrai usare istruzioni del tipo

Codice: Seleziona tutto

Selection.QueryTable.Refresh BackgroundQuery:=False

Se invece usi una macro allora necessariamente devi avviare la macro. Oltre ai modi che ti avevo descritto (macro di Worksheet_Activate o di Workbook_Open) puoi usare anche altri metodi; es: Pulsante con associata la macro; tasti di scelta rapida associati alla macro; personalizzazione della barra di accesso rapido o del menu multifunzione di Excel; e certamente altri ancora.

Ciao

[davide2574]

Ciao Antonhy

Sono un dilettante su vba. Allego il file cosi almeno mi chiarisce le cose delle tue correzioni e dove ho sbagliato.
Come vedi che ogni foglio ha un suo codice

Sub GetTabbb()
'
myURL = "http://www.nhl.com/stats/team?reportType=game&report=teamsummary&season=20152016&gameType=2&aggregate=1&gameLocation=H"
Set ie = CreateObject("InternetExplorer.Application") ..........

Vorrei sapere se è possibile fare con questo codice che si può assegnare ogni foglio con indirizzo diverso del sito per poter evitare una confusione ed anche aggiornare per tutti i fogli in un solo clik manuale con icona? altrimenti sarei costretto ad aggiornare uno alla volta ogni foglio e anche per ogni queryweb
Allego il file.
Grazie e scusami tanto per il disturbo.
Ciao

Davide


File link

http://www.filedropper.com/hockeynhlexcel

[Anthony47]

Purtroppo io non so che cosa vuoi fare, quindi mi limito a interventi di buon senso riandando a te l'attuazione di modifiche analoghe per ottenere risultati analoghi ma diversi.
1) Prima considerazione: hai inserito una serie di macro nei fogli di codice dei vari fogli; in questo fogli vanno pero' inserite solo le macro relative ad eventi del singolo foglio, e le macro standard vanno in Moduli standard (tipo Modulo1, in cui hai inserito la Sub AggiornaNHL).
2) Seconda considerazione: piuttosto che ripetere lo stesso codice in N macro, conviene ovviamente avere un codice comune alle N macro e richiamare questo codice con i dati specifici della singola macro.
3) Parli di WebQuery da aggiornare; se tu sai dove sono localizzate puoi usare le istruzioni inserite nella tua Sub AggiornaNHL (tipo Range("Z1").Select / Selection.QueryTable.Refresh BackgroundQuery:=False).
Oppure con una macro come questa aggiorni tutte le query inserite in tutti i fogli del tuo file:

Codice: Seleziona tutto

Sub QTRefreshAll()
For i = 1 To Worksheets.Count
    Sheets(i).Select
    mmm = ""
    aaa = ActiveSheet.QueryTables.Count
    For j = 1 To aaa
        mmm = mmm & ActiveSheet.QueryTables(j).Destination.Address & "_"
        ActiveSheet.QueryTables(j).Refresh BackgroundQuery:=False
    Next j
    Debug.Print i & " - " & aaa & mmm
Next i
End Sub

Tornando alla considerazione 2), allora invece della Sub GetTabbb standard possiamo usare questa versione "parametrica"

Codice: Seleziona tutto

Sub GetTabbbSub(ByVal myURL As String)
'Va Chiamata passandogli l'URL da leggere
'myURL = "http://www.nhl.com/stats/team?reportType=game&report=teamsummary&season=20152016&gameType=2&aggregate=1&gameLocation=H"
Set ie = CreateObject("InternetExplorer.Application")
   
With ie
    .navigate myURL
    .Visible = True
    Do While .Busy: DoEvents: Loop    'Attesa not busy
    Do While .readyState <> 4: DoEvents: Loop 'Attesa documento
End With
'
myStart = Timer  'attesa addizionale
Do
    DoEvents
    If Timer > myStart + 2 Or Timer < myStart Then Exit Do
Loop

'Leggi le tabelle SUL FOGLIO ATTIVO
''Sheets("Classifica Home").Select
''Cells.Clear
Set mycoll = ie.document.getElementsByTagName("TABLE")
For Each myItm In mycoll
    For Each trtr In myItm.Rows
        For Each tdtd In trtr.Cells
            Cells(i + 1, j + 1) = tdtd.innerText
            j = j + 1
        Next tdtd
        i = i + 1: j = 0
    Next trtr
i = i + 1
Next myItm

''Stop     'Vedi testo

'Chiusura IE
ie.Quit
Set ie = Nothing
End Sub


La metterai in un Modulo standard del vba, che avrai prima creato con Menu /Inserisci /Modulo

Poi richiamerai questa nuova sub "passandogli" l'url dell'importazione da fare; come in questo esempio:

Codice: Seleziona tutto

Sub Agg_Classifica_Home()
    Sheets("Classifica Home").Select
    Cells.ClearContents
    Call GetTabbbSub("http://www.nhl.com/stats/team?reportType=game&report=teamsummary&season=20152016&gameType=2&aggregate=1&gameLocation=H")
End Sub


Questa la puoi mettere nello stesso Modulo in cui e' posizionata la Sub GetTabbbSub, o anche in altro modulo standard, e serve per aggiornare il contenuto del foglio Classifica Home. Funzionalmente essa sostitusce la precedente Sub GetTabbb che hai messo nel modulo di codice del foglio Classifica Home; che quindi va eliminata.

Analogamente quest'altra serve per aggiornare il contenuto del foglio Schedule, in sostituzione della precedente Sub GetTabbb che hai messo nel modulo di codice del foglio Schedule, che quindi va eliminata:

Codice: Seleziona tutto

Sub Agg_Schedule()
    Sheets("Schedule").Select
    Cells.ClearContents
    Call GetTabbbSub("http://stats.betradar.com/s4/?clientid=1271&language=en#2_4,3_37,22_2,5_11202,9_fixtures,231_full,23_3")
End Sub


Non sara' difficile creare altre Sub Agg_NomeFoglio che richiamano la nuova GetTabbbSub passandole l'url del sito da cui bisogna scaricare le tabelle; in particolare in sostituzione delle macro contenute nei moduli di codice dei fogli ClassifcaRoard e ClassificaGenerale, che vanno tutte eliminate.

La Sub GetTabbbSub fa solo l'importazione delle tabelle del sito indirizzato sul foglio attivo; quindi il foglio interessato deve essere reso attivo dalla macro che la richiama, come pure altre operazioni propedeutiche all'importazione delle tabelle o posteriori all'importazione.
Nell'esempio della Sub Agg_Classifica_Home, ho prima selezionato il foglio (.Select), poi ne ho cancellato il contenuto (.ClearContents, che non cancella la formattazione).
Se vuoi eseguire altre operazioni dopo l'importazione allora devi aggiungere le relative istruzioni dopo la Call GetTabbbSub.
Nelle tue varie Sub GetTabbb ci sono, verso la fine, istruzioni errate (es Selection.NumberFormat = "#:#") o inutili (es xCell.Value = xCell.Value) che io ho eliminato nella nuova Sub GetTabbbSub.

Quando eseguire gli aggiornamenti?
Non lo so... dipende dalla frequenza con cui cambiano i dati e dalla durata dell'aggiornamento.
Se vuoi eseguirli tutti all'apertura del file, allora inserisci nel modulo ThisWorkbook /QuestaCartellaDiLavoro del vba una macro come questa, che "richiami" (istruzione Call) le varie macro presenti nel file incaricate di aggiornare le varie parti dei fogli:

Codice: Seleziona tutto

Private Sub Workbook_Open()
Call Agg_Classifica_Home
Call Agg_Schedule
Call Agg_AltroFoglio       
Call Agg_AltroFoglioAncora
'etc etc
'etc etc
'
Call QTRefreshAll
End Sub


Spero che ci siano informazioni e spunti utili al tuo scopo.

Ciao

[davide2574]

Ciao Antonhy

non so come ringraziarti. E' perfetto, vorrei sapere è possibile che se inserisco


Sub Agg_Classifica_Home()
Sheets("Classifica Home").Select
Range("A1").Select
Cells.ClearContents
Call GetTabbbSub("http://www.nhl.com/stats/team?reportType=game&report=teamsummary&season=20152016&gameType=2&aggregate=1&gameLocation=H")
End Sub

Sub Agg_Classifica_Home()
Sheets("Classifica Home").Select
Range("T1").Select
Cells.ClearContents
Call GetTabbbSub("http://www.nhl.com/stats/team?reportType=game&report=teamsummary&season=20152016&gameType=2&aggregate=1&gameLocation=H")
End Sub


cioè importazione con due siti diversi nello stesso foglio ma nelle due celle diverse. E' giusto come impostazione che ho inserito rosso?

[Anthony47]

Non puoi avere due macro con lo stesso nome.
Inoltre la GetTabbbSub importa le tabelle presenti a partire dalla colonna A. Infine devi tener presente che la riga Cells.ClearContents pulisce il contenuto (non la formattazione) di tutte le celle del foglio, quindi se lavori su un foglio dove hai gia' importato un tot di tabelle devi omettere una seconda Cells.ClearContents.
Quindi, se vuoi importare da un url su colonna T e successive e da un altro url in colonna A, dovrai usare (ad esempio):

Codice: Seleziona tutto

Sub Agg_Classifica_Home()
Sheets("Classifica Home").Select
Cells.ClearContents     'Azzera il foglio
'Importa da col A da primo Url:
Call GetTabbbSub("http://www.nhl.com/stats/team?reportType=game&report=teamsummary&season=20152016&gameType=2&aggregate=1&gameLocation=H")
'Sposta la prima importazione a col T e successive:
Columns("A:U").Insert Shift:=xlToRight
'Importa da colonna A da secondo Url:
Call GetTabbbSub("http://www.nhl.com/stats/team?reportType=game&report=teamsummary&season=20152016&gameType=2&aggregate=1&gameLocation=H")
End Sub


Ciao

[davide2574]

Ciao Antonhy

accidenti mi sono accorto che ho sbagliato ad inserire due stessi indirizzi ma io volevo dire:

http://www.nhl.com/stats/team?reportTyp ... Location=H


http://www.nhl.com/stats/team?reportTyp ... Location=R


quindi non cambia niente oppure cambia perchè ultima lettera è diversa quindi si potrebbe fare due macro per due diversi siti?
grazie
ciao

davide

[Anthony47]

Spero che tu abbia gia' provato: l'ultimo codice che ti avevo dato serve per importare in una stessa pagina le tabelle da due Url; il primo Url importa su colona T, il secondo su colonna A.
Quindi cambia gli url a piacere, anzi "come ti servono".

Ciao