[Excel] Acquisizione dati Web in realtime

[mpsinf]

Avendo una lista di circa 20 link da dove dover prelevare dei dati tramite "query web" in tempo reale e continuativo, volevo sapere se ci fosse un sistema, tramite comando da macro VBA per farle aggiornare tutte contemporaneamente ?
Considerate che intendevo mettere tutti i link delle query web su un foglio in diverse colonne distanziate per evitare il sovrapporsi (preciso che occupano sempre lo stesso spazio). Oppure avete soluzioni migliori da propormi ?

[Anthony47]

Se vuoi procedere con le Query Web, allora tieni presente che con una istruzione tipo Thisworkbook.RefreshAll puoi comandare il refresh di tutte.
Questo assicura il "contemporaneamente" dell' avvio; trattandosi pero' di eventi asincroni la fine dipendera' da fattori esterni (sito, rete); ti interessa in qualche modo sincronizzare anche il completamento? In questo caso si potra' usare l' evento AfterRefresh, da programmare query per query secondo i suggerimenti descritti nell' help on line del vba alla voce "Utilizzo di eventi con l'oggetto QueryTable".

In quanto a ipotesi alternative, vedo sempre e solo la possibile concorrenza della libreria mshtml.tlb.

Ciao

[mpsinf]

Sono anch'io dell'idea dell'analisi diretta con le libreria mshtml.tlb, ma quello che mia spaventa di più oltre all'analisi delle singole pagine che comunque sono sempre uguali e con il tuo aiuto ho visto di poter risolvere, ci sono alcuni punti che vorrei elencare.
I 20 link da analizzare sono elencati in una colonna aggiornata con nuovi dati quando uno di questi in analisi viene terminato.
Se può essere un problema questi link in colonna ad ogni aggiornamento/refresh possono cambiare la loro posizione.
L'analisi di ogni link prevede l'estrazione di alcun righe che però possono "sfuggire" perché i dati all'interno vengono sostituiti molto rapidamente. La loro archiviazione temporanea dovrà essere fatta su un foglio, separati ma collegati al proprio link e la loro cancellazione potrá essere fatta solo quando si verifica una determinata condizione all'interno del link facilmente individuabile, cancellazione possibile dopo aver lanciato macro giá pronta per l'archiviazione di questi dati raccolti. A questo punto i dati del link chiuso dovranno essere cancellati e il suo posto dovrá essere preso dall'analisi del nuovo link. Pensi che la cosa sia fattibile ?

[Anthony47]

Il fatto che i link che mutano non creera' problemi, perche' l' estrazione tu la farai solo sui link in elenco.
Come archiviare le informazioni, in modo transitorio o definitivo, dipendera' poi dalla loro natura e organizzazione e dal tipo di elaborazione successiva che devi costruire sopra questo archivio. Il modo piu' semplice e' in forma tabellare con in prima colonna il link o un codice univoco associabile al link e poi la data/ora; questo ti consentira' di ricercare tutte le righe associate allo stesso link e di gestire politiche di archiviazione o cancellazione basate su link o data/ora.
E' certamente fattibile, ma dovrai essere pronto ad adeguare il codice alle modifiche che verranno applicate al codice sorgente delle pagine html.

Ciao

[mpsinf]

Proviamo con mshtml. Ho il seguente codice html da analizzare

<TD STYLE="text-align: right; padding: 0; padding-right: 4px; width: 120px" ID="last_bids_datetime_1">09/11/2012 08:08:53</TD><TD STYLE="font-family: verdana; text-align: left; padding: 0; overflow: hidden" ID="last_bids_username_1">Cathxxx</TD><TD STYLE="text-align: right; padding: 0; padding-right: 4px; width: 50px" ID="last_bids_amount_1">€0.80</TD><TD STYLE="text-align: left; padding: 0" ID="last_bids_type_1">Manuale</TD></TR><TR><TD STYLE="text-align: right; padding: 0; padding-right: 4px; width: 120px" ID="last_bids_datetime_2">09/11/2012 08:08:50</TD><TD STYLE="font-family: verdana; text-align: left; padding: 0; overflow: hidden" ID="last_bids_username_2">SheskinSM</TD><TD STYLE="text-align: right; padding: 0; padding-right: 4px; width: 50px" ID="last_bids_amount_2">€0.79</TD><TD STYLE="text-align: left; padding: 0" ID="last_bids_type_2">Manuale</TD></TR><TR><TD STYLE="text-align: right; padding: 0; padding-right: 4px; width: 120px" ID="last_bids_datetime_3">09/11/2012 08:08:49</TD><TD STYLE="font-family: verdana; text-align: left; padding: 0; overflow: hidden" ID="last_bids_username_3">familiekast</TD><TD STYLE="text-align: right; padding: 0; padding-right: 4px; width: 50px" ID="last_bids_amount_3">€0.78</TD><TD STYLE="text-align: left; padding: 0"

con la seguente parte di macro

Codice: Seleziona tutto

Set TagColl = IE.document.getElementsByTagName("TD")
For Each SpanT In TagColl
    If SpanT.ID = "last_bids_datetime_1" Then ' oppure  "last_bids_datetime" & "_1" con ciclo for next
        MsgBox SpanT '[object]
        MsgBox (SpanT.innerText)
        Cells(i + 1, 1) = SpanT.innerText
     
        i = i + 1
    End If
     
    If SpanT.ID = "last_bids_username_1" Then
        MsgBox SpanT '[object]
        MsgBox (SpanT.innerText)
        Cells(i + 1, 1) = SpanT.innerText
       
        i = i + 1
    End If
     
       
    If SpanT.ID = "last_bids_amount_1" Then
        MsgBox SpanT '[object]
        MsgBox (SpanT.innerText)
        Cells(i + 1, 1) = SpanT.innerText
       
        i = i + 1
    End If
   
Next SpanT

riesco ad estrarre quello che mi serve.
Vorrei però creare una stuttura più lineare e oltre a fare un ciclo for next per estrarre tutte e tre gli username da 1 a 3 volevo sapere se fosse possibile una volta individuato il 1° last_bids_datetime_1 estraporlare anche i relativi last_bids_username_1" e "last_bids_amount_1" e fare in modo che mi vadano tutti su una stessa riga

grazie ancora per l'aiuto

[Anthony47]

Vedo che stai facendo esperimenti e questa e' l' approccio giusto: sperimentare per verificare il comportamento e quindi il possibile utilizzo delle funzioni disponibili.
Nello specifico, devi considerare che hai creato una collezione di TD, che replica la sequenza dei Td nel sorgente.
Ti sara' quindi sufficiente cercare la prima voce ("last_bids_datetime_1") e poi importare tutti i TD che seguono avendo a mente come essi sono strutturati (righe di 4 colonne).
Questa logica corrisponde a un ciclo del tipo

Codice: Seleziona tutto

'cerca ed elenca Le voci
Set myColl = Ie.Document.getElementsByTagName("TD")
For Each myItm In myColl
    If myItm.ID = "last_bids_datetime_1" Or myFound Then
        Range("H5").Offset(I, J) = myItm.innertext     '<<< Tabella messa in H5
        myFound = True
        J = (J + 1) Mod 4
        I = I - 1 * (J = 0)
        If I > 5 Then Exit For  '<<< Ipotesi di raccogliere 5+1 righe
End If
Next myItm

Stop     '


Come sempre vanno personalizzate le istruzioni contenute nelle celle marcate <<<

Ciao

[mpsinf]

Come hai potuto notare cerco di impegnarmi il più possibile per apprendere l'arte del VBAismo...
Ok per la soluzione del RESTO (Mod 4) così incrementa il valore di 1 ogni 4 cicli, ma non ho capito I = I - 1 * (J = 0) me la puoi spiegare ? perchè non capisco come faccia ad aumentare se viene tolto -1
Inoltre come faccio ad inserire i valori senza sovrascriverli a quelli vecchi, ma facendo slittare una lista già analizzata e già presente per esempio in H5 : H10 verso il basso ogni volta che aggiungo nuovi dati. ( alternativa al comando Selection.Insert Shift:=xlDown)
che avevo utilizzato nella mia versione iniziale con queryweb su foglio, ma qui cambia tutto..

Inoltre avevo poi i 2 seguenti problemi, analizzando nuovamente dopo tot tempo lo stesso link mi posso trovare 2 situazioni critiche:

1) i dati da rianalizzare sono cambiati ma non tutti e quindi mi trovo doppioni con lo stesso importo

esempio lista analizzata e già in colonna A
11/11/12 pluto 2,60
11/11/12 paperino 2,59
11/11/12 pluto 2,58
11/11/12 minni 2,57
11/11/12 pluto 2,56
11/11/12 pippo 2,55

lista che troveremo al prossimo controllo

11/11/12 gastone 2,62
11/11/12 paperino 2,61
11/11/12 pluto 2,60
11/11/12 paperino 2,59
11/11/12 pluto 2,58
11/11/12 minni 2,57

quindi se riparto con l'analisi quelli dal 2,60 fino al 2,57 me li ritrovo doppi... e quindi dovrei saltarli ... qui forse penso di potercela fare con un controllo if<= .... attendo comunque un consiglio/dritta...


2) i dati da rianalizzare sono cambiati tutti ed oltre (quindi sfuggiti )... ma possiamo ricostruirli..

esempio lista analizzata e già in colonna A
11/11/12 pluto 2,60
11/11/12 paperino 2,59
11/11/12 pluto 2,58
11/11/12 minni 2,57
11/11/12 pluto 2,56
11/11/12 pippo 2,55

lista che troveremo al prossimo controllo

11/11/12 gastone 3,23
11/11/12 paperino 3,22
11/11/12 pluto 3,21
11/11/12 paperino 3,20
11/11/12 pluto 3,19
11/11/12 minni 3,18

quindi ci mancherebbero tutti i dati da 2,61 a 3,17 e per ricostruirli basterebbe una pezzo di codice che mi prenda gli ultimi 2 nomi
della lista analizzata precedentemente ossia "paperino 2,59 e pluto 2,60 e vengano aggiunti alla lista prima di far partire l'analisi della nuovo situazione alternandoli uno all'altro ossia --> paperino 2,61 - pluto 2,62 - paperino 2,63 - pluto 2,64 - ecc. fino al 3,17.

Ti chiedo un consiglio per poter evitare km. di codice grezzo rispetto a quello che tu riesci a riassumere in poche righe..

[Anthony47]

L' istruzione I = I - 1 * (J = 0) AGGIUNGE 1 (ad ogni ciclo di 4 J) perche' Vero in vba corrisponde a "-1"

Per quanto riguarda la raccolta dati:
1) Per quanto riguarda aggiungere i nuovi dat a un elenco preesistente, farai appunto un Insert Shift:=xlDown.
Poiche' la macro ha cablato al suo interno l' indirizzo dove scrivere (io avevo arbitrariamente usato H5), il numero di righe da inserire (io avevo arbitrariamente usato 5+1=6) e il numero di colonne (io ne importavo 4), puoi usare una istruzione del tipo

Codice: Seleziona tutto

    Range("H5").Resize(6,4).Insert Shift:=xlDown


(da mettere subito prima della riga 'cerca ed elenca Le voci)

2) Una volta raccolti tutti i dati relativi a un item, quindi incluso eventuali duplicati, puoi "normalizzare l' elenco" usando il Filtro avanzato, con la scelta Copia univoca dei record; questo eliminera' eventuali doppioni. Puoi registrare una macro mentre fai questo e usarla per queste situazioni.

3) In quanto a inserire record dummy in presenza di vuoti certamente si puo' fare anche se personalmente ritengo piu' corretto un vuoto che un riempimento a caso che ad esempio ti porteranno a valutare erroneamente la strategia dei singoli utenti. Quindi mi applichero' "se mi avanza del tempo"

Un' ultima considerazione: poche righe o molte righe contorte ma funzionanti sono equivalenti in termini di prestazioni, non lasciarti abbagliare dallo stile di programmazione.

Per favore indica la tua configurazione di lavoro: viewtopic.php?f=26&t=97449
Ciao

[mpsinf]

L' istruzione I = I - 1 * (J = 0) AGGIUNGE 1 (ad ogni ciclo di 4 J) perche' Vero in vba corrisponde a "-1"

Ahh.. OK ! imparato nuova cosa

2) Una volta raccolti tutti i dati relativi a un item, quindi incluso eventuali duplicati, puoi "normalizzare l' elenco" usando il Filtro avanzato, con la scelta Copia univoca dei record; questo eliminera' eventuali doppioni. Puoi registrare una macro mentre fai questo e usarla per queste situazioni.

Ma se uso il filtro avanzato poi i dati rimangono comunque nel foglio e visto che devo successivamente analizzarli risulta scomodo.

In quanto a inserire record dummy in presenza di vuoti certamente si puo' fare anche se personalmente ritengo piu' corretto un vuoto che un riempimento a caso che ad esempio ti porteranno a valutare erroneamente la strategia dei singoli utenti. Quindi mi applichero' "se mi avanza del tempo"

Meglio un dato con un minimo di probabilità che sia quello giusto che una cella vuota.


Risolto così anche se brutto da vedersi

Codice: Seleziona tutto

Set TagColl = IE.document.getElementsByTagName("TD")

For Each myTag In TagColl
   If Left(myTag.ID, 19) = "last_bids_datetime_" Or MyFound Then
     Range("H1").Offset(i, J) = myTag.innerText
        MyFound = True
        J = (J + 1) Mod 4
        i = i - 1 * (J = 0)
         
        If i > 9 Then Exit For ' Per uscire dal ciclo dopo aver acquisito i 10 dati
    End If
Next myTag
   
 For A = 0 To (Range("J10").Value - Range("C1").Value) * 100 - 1
   If Range("C1").Value > 0 Then
   Range("A2:D2").Select
   Selection.Copy
   Range("A1:D1").Select
   Selection.Insert Shift:=xlDown
   Range("C1").Value = Range("C2").Value + 0.01
   Selection.Font.ColorIndex = 3
   End If
 Next A
     
  For A = 10 To 1 Step -1
   If Range("H1").Offset(A - 1, 2).Value <> "" And Range("H1").Offset(A - 1, 2).Value > Range("C1").Value Then
     Range("H" & A & ":K" & A).Select  'brutto codice a vedersi
     Selection.Copy
     Range("A1:D1").Select
     Selection.Insert Shift:=xlDown
   End If
  Next A

tempo = Now + TimeValue("00:00:30")
 ' Application.OnTime tempo, "CICLO"

'Chiusura IE
IE.Quit
Set IE = Nothing


Ora il problema rimane la lista dei link da analizzare che continua a variare.. Hai qualche idea ?

[Anthony47]

Ma se uso il filtro avanzato poi i dati rimangono comunque nel foglio e visto che devo successivamente analizzarli risulta scomodo.

Puoi usare "Filtra in altra posizione" e poi sostituire la lista filtrata (priva di duplicati) alla lista originale.

Risolto così anche se brutto da vedersi

Te l' ho gia' detto: se funziona e' bellissimo

Codice: Seleziona tutto

Ora il problema rimane la lista dei link da analizzare che continua a variare.. Hai qualche idea ?

In qualche altra discussione credo di aver detto che non capivo quale preoccupazione nasce dal fatto che i link cambiano, puoi spiegare l' ambascia?

Ciao

[mpsinf]

Ho risolto il problema facendo un ciclo for next dell'elenco dei link che crea un nuovo foglio per ogni link con il nome dell'etichetta uguale al numero dell'elenco. Per ogni foglio creato inserisce in una cella il link che successivamente la macro che verrà lanciata utilizzerà per l'analisi dei dati che verranno elencati nello stesso foglio. La cancellazione dei fogli, creati momentaneamente per la raccolta dati, verrà in maniera automatica quando nell'analisi del link accade una determinata condizione.
Quando si elimina un foglio significa che il link non esiste più e viene sostituito da uno nuovo, che sarà presente nella lista iniziale al successivo ciclo.
Quindi quando ripartirà il ciclo di analisi mi trovo che se il foglio esiste già (quelli non scaduti per capirci) va in errore e mi servirebbe un controllo per evitare il blocco della macro. Avete un suggerimento ?

Grazie

[Anthony47]

Mi pare quindi che la domanda sia come sapere se un foglio esiste prima di crearlo; per questo puoi usare una sequenza del tipo

Codice: Seleziona tutto

NextName = "Item1234"
On Error Resume Next
    wExist = Sheets(NextName).Name
On Error GoTo 0
If wExist = "" Then Worksheets.Add.Name = NextName


Se il foglio memorizzato in NextName non esiste esso sara' aggiunto.

Ciao

[mpsinf]

Non avevo fatto in tempo ad inserire il codice che avevo sviluppato

Codice: Seleziona tutto

Sheets("Home").Select
For A = 1 To Range("B1").End(xlDown).Row
 Nomefg = Cells(A, "B").Value
 nLink = Cells(A, "A").Value

 On Error Resume Next                                               ' Tuo inizio
 wExist = Sheets(Nomefg).Name
 On Error GoTo 0
 If wExist = "" Then Worksheets.Add.Name = Nomefg    ' Tuo fine

 Worksheets.Add.Name = Nomefg  ' Con tuo tolgo questo
 ActiveSheet.Name = Nomefg
 
  Range("A1") = Nomefg ' inserisce in cella n. dell'asta
  Range("B1") = nLink ' inserisce in cella il link di collegamento
Next A


Ora ho inserito "vedi parte centrale" il tuo pezzo di codice ma mi va in errore comunque . Dove sbaglio ?

[Anthony47]

Tra poco diventi "Utente senior" e ancora non hai capito che per chiedere "Dove sbaglio?" senza indicare che errore ti compare e su quale riga devi andare su www.lostregone.it??

Ciao

[mpsinf]

L'errore è di "run-time 1004" ed appare alla riga di codice

Codice: Seleziona tutto

If wExist = "" Then Worksheets.Add.Name = Nomefg


che inoltre mi sembra strano un If then senza Endif

inoltre ho anche ripristinato della mia vecchia macro dopo la riga "For A=1 .... " questa riga

Codice: Seleziona tutto

Sheets("Home").Select


perchè non sò come creare un foglio nuovo rimanendo sul foglio da dove viene generato

P.S. Lasciatemi junior anzi passatemi a baby

[Flash30005]

Dovresti spostare solo la riga di select foglio "Home" all'interno del ciclo For... next

Codice: Seleziona tutto

For A = 1 To Range("B1").End(xlDown).Row  '<<<esistente
Sheets("Home").Select   '<<<<< riga da inserire qui


If e then possono non avere end if
se viene scritto tutto in una sola riga (un solo risultato, come nel tuo caso)

Codice: Seleziona tutto

If wExist = "" Then Worksheets.Add.Name = Nomefg


oppure

Codice: Seleziona tutto

If wExist = "" Then
Worksheets.Add.Name = Nomefg
'<<<' altre possibili azioni
end if

ciao

[Anthony47]

Quanto suggerito da Flash (vedi sopra) e' la soluzione piu' semplice per correggere l' errore 1004; l' alternativa e' esplicitare all' interno del loop For I /Next I il riferimento al foglio Home; cioe'

Codice: Seleziona tutto

 Nomefg = Sheets("Home").Cells(A, "B").Value
 nLink = Sheets("Home").Cells(A, "A").Value


Nell' ipotesi che le istruzioni Range("A1") = Nomefg / Range("B1") = nLink siano da inserire solo se aggiungi il nuovo foglio allora devi passare alla sintassi If .. Then /Istruzioni /End If,
cioe'

Codice: Seleziona tutto

If wExist = "" Then
  Worksheets.Add.Name = Nomefg   
  Range("A1") = Nomefg ' inserisce in cella n. dell'asta
  Range("B1") = nLink ' inserisce in cella il link di collegamento
End If


Se invece vanno eseguite sempre (anche se il foglio e' gia' presente), allora e' necessario selezionare prima il foglio Nomefg.
Inoltre, riusando la stessa variabile wExist all' interno di un loop dovresti azzerarla prima del prossimo ciclo, o in alternativa modifichi da If wExist = "" Then a If wExist <> Nomefg Then
Infine temo che tu usi dei numeri come nome file; se questo e' vero allora devi inserire in testa alla macro la definizione (che comunque non non fa male):

Codice: Seleziona tutto

Dim Nomefg As String


Ciao

[mpsinf]

Ho una macro che analizza i link presenti in un sito web e li mette in una colonna (101 - 122 -133) poi crea n fogli con nome etichetta corrispondente al valore che trova nella lista. Mi troverò quindi un foglio di nome 101 un altro foglio 122 ecc. sui quali viene fatto in modo ciclico alcuni controlli e se si verifica una determinata condizione viene cancellato il rispettivo foglio.
Al successivo ciclo dell'analisi del sito la lista iniziale potrà essere uguale, quindi nessun problema, oppure diversa perchè alcuni numeri non saranno più presenti, a seguito della cancellazione di alcuni link nel sito. Quindi mi troverò ad esempio 101 - 155 - 133, di conseguenza la macro a ciclo non trovando più in lista il foglio 122 non lo prenderà più in considerazione e quindi rimarrà "pendente" ed ignorato invece di essere analizzato per l'ultima volta e cancellato con il codice di controllo di cui parlavo prima.

A questo punto non trovando soluzione alternativa l'unica cosa che avevo pensato è quella di analizzare con ciclo i fogli creati presenti e passarli alla macro di analisi. Considerando che il numero di fogli che vado a creare non è sempre uguale unico riferimento vengono creati tutti in ordine visivo prima del foglio da me chiamato Home, sapete darmi una soluzione di analisi dei fogli di cui non sono ancora molto pratico?

[mpsinf]

In attesa di qualche suggerimento ho inserito questo pezzo di codice nella macro principale

Codice: Seleziona tutto

For nfg = 1 To Sheets.Count
  If Sheets(nfg).Name = "Home" Then Exit For
  Sheets(Sheets(nfg).Name).Select
  Call A2_Estrai_valori
Next nfg

così ho anche eliminato la macro che poi mi faceva partire la ricerca dopo la creazione dell'elenco

Codice: Seleziona tutto

For A = 1 To Range("B1").End(xlDown).Row
 Sheets("Home").Select
 Nome = Cells(A, "B").Value
 Sheets(Nome).Select
Call A2_Estrai_valori
Next A

[mpsinf]

Mi accorgo ora però che se al ritorno dal "Call A2_Estrai_valori" il foglio analizzato è anche stato cancellato ed è l'ultimo (ossia quello prima della Home) mi si impalla la situazione ossia invece "If Sheets(nfg).Name = "Home" Then Exit For" ed uscire non trova più Home perchè probabilmente a preso il n. di foglio al posto di quello cancellato e va a finire su quello sucessivo e si incasina tutto il sistema.
come posso ovviare ?
oltre ad un escamotage poco elegante

Codice: Seleziona tutto

If Sheets(nfg).Name = "Home" Or Sheets(nfg).Name = "fogliodopo" Then Exit For


dove fogliodopo è quello successivo ad Home

Visto che comunque capita solo quando è l'ultimo foglio prima del foglio Home ad essere cancellato e non se è un foglio precedente,
volevo anche un aiuto su come riordinare i fogli in base ad un valore di una cella presente all'interno di ognuno, ossia se nel foglio che occupa la posizione visiva n. 6 e contine in cella A1 il valore più basso di tutti gli altri fogli diventi il primo foglio e così via.
Questo mi serve anche, visto che la macro prende in esame tutti i fogli, per accellerare l'esecuzione non perdendo tempo con quelli dove il valore è più alto di un tot.