cercare un valore in più fogli excel ed avere un output

[redvin]

Buonasera a tutti,
avrei bisogno del vostro prezioso aiuto.

Ho una cartella con una serie di files in excel.
Ogni files excel ha diversi fogli con i mesi dell'anno.
Avrei bisogno di un programmino o quant'altro possibile (forse si potrebbe fare con una macro in excel) che scorra tutti i files presenti nella cartella saltando quelli rinominati con "finito" e che veda se all'interno dei fogli da gennaio ad aprile nelle celle j13 e j14 ci sono valori diversi da 0.
Dopo aver fatto tutto questo mi dovrebbe dare l'output con tutti i files che presentano quella caratteristica e magari fammi vedere, per ogni file, anche il mese di riferimento, in cui è presente un valore diverso da 0 in quella cella.

Vi ringrazio in anticipo per il vostro aiuto.
Sto imparando ad usare excel.
Grazie mille.

[Tribuno]

Benvenuto redvin nel forum,

prova a vedere queste due discussioni
http://www.pc-facile.com/forum/viewtopic.php?f=26&t=75455?
http://www.pc-facile.com/forum/viewtopic.php?f=26&t=75645

poi siamo qui per aiutarti.

Ciao
Tribuno

[redvin]

Tribuno grazie per l'aiuto tempestivo.
Provo a dare un'occhiata e vedere se riesco ad adattarlo al mio caso.
Ciao.

[redvin]

Ciao, ho provato a vedere le discussioni che mi ha consigliato Tribuno, ma non riesco in nessun modo ad adattarle al mio problema.
A me servirebbe qualcosa che cerchi nelle sottocartelle i files che non hanno la dicitura "finito". Dopo dovrebbe controllare all'interno di questi files e cercare in alcuni fogli (che dovrei definire prima) se c'è un valore in una cella specificata.
Se così fosse mi dovrebbe dare un output con tutti i file che hanno queste caratteristiche con accanto il mese corrispondente.
Che ne dite si potrebbe fare una cosa del genere?
Non sono per niente esperta in queste cose, ma mi servirebbe tanto altrimenti dovrei controllare circa 1500 files.
Vi ringrazio tanto per il vostro aiuto.
ciao

[Tribuno]

Ciao redvin,
sì, quello che chiedi si deve fare con una macro.

1) Hai un file xls di riepilogo nel quale inserire i dati che corrispondono ai criteri da te definiti?
2) Tutti gli altri file sono nella medesima cartella (es. i file Pippo.xls, Pluto x.ls, finito.xls) oppoure parti da una cartella e mano a mano crei delle sottocartelle dove inserisci i file finito.xls?
3) I file denominati finito.xls, se sono tutti nella stessa cartella, poiché ci devono essere file con nomi univoci, hanno altri caratteri che li differenziano, come ad esempio finito001.xls, finito002.xls e così via?
4) I nomi dei fogli dei vari file da verificare hanno tutti il medesimo nome (gennaio, febbraio, marzo, ecc.)? Hanno tutti i file il medesimo numero di fogli?
5) Nel tuo foglio riepilogativo dove e come devono essere disposti i dati di output?

Come vedi per poter dare una risposta al tuo post iniziale c'è la necessità di avere qualche informazione in più.

Ciao
Tribuno

[redvin]

Ciao Tribuno,
ti ringrazio tanto per la tua disponibilità.
Cerco di essere più chiara possibile, effettivamente, è un pò difficile da spiegare.

1) Non ho un file dove mettere i miei riepiloghi ma volendo ne potrei creare uno. Non saprei come fare a definirlo, ma ci potrei provare.

2) Il percorso dei miei documenti è:


Cartella principale
|
|_Sottocartella
| |_SubA
| | |
ProdottoA 001.xls
ProdottoA 002.xls
ProdottoA 003-finito.xls
ProdottoA 004.xls
|_SubB
| | |
ProdottoB 001.xls
ProdottoB 002-finito.xls
ProdottoB 003.xls
ProdottoB 004.xls
ProdottoB 005-finito.xls
|_SubC
| | |
ProdottoC 001.xls
ProdottoC 002-finito.xls
ProdottoC 003-finito.xls
ProdottoC 004.xls
ProdottoC 005.xls
ProdottoC 006-finito.xls
Ecc…

Praticamente all’interno di ogni sottocartella ci sono dei prodotti riordinati per nome e ci sono quelli in processo di lavorazione e quelli che hanno finito il processo di lavorazione.



3) come si vede dall’esempio precedente i file con la dicitura “finito” sono all’interno di quasi tutte le cartelle, ma sono preceduti dal tipo di prodotto che è inserito in base alla prima lettera del prodotto nella cartella corrispondente.


4) I fogli sono organizzati tutti nello stesso modo:
hanno come primo foglio “Dati generali” e poi iniziano da gennaio a dicembre ed ogni foglio è rinominato con le prime 3 lettere del mese, tipo “gen”, “feb”, “mar”…
Dopo i mesi ci sono altri fogli con diversi dati che sono collegati ai fogli precedenti.

5)Nel mio foglio riepilogativo i miei file vorrei cercare di renderli più chiari possibili da trovare visto che sono più di 1500 files (e magari se fosse possibile, ma capisco che la cosa è molto complicata, rendere un collegamento ipertestuale, in modo che al click su di esso si apra automaticamente la cartella di lavoro e il foglio) ma l’importante è che mi vengano visualizzati i file che hanno quelle caratteristiche e magari anche in quale foglio si trovano, sempre per ogni file.
Ad esempio:
Cartella principale -> Sottocartella -> Sub A-> Prodotto 001.xls -> Foglio “gen”
Cartella principale -> Sottocartella -> Sub A-> Prodotto 004.xls -> Foglio “mar”
Cartella principale -> Sottocartella -> Sub B-> Prodotto 003.xls -> Foglio “apr”
Ecc.


Ho cercato di essere più chiara possibile, spero di esserci riuscita.
Nel caso fatemi altre domande.
Vi ringrazio tantissimo per l’aiuto.
ciao redvin

[Anthony47]

Mi intrometto per dire la mia su come si potrebbe fare, ispirandosi molto al topic viewtopic.php?f=26&t=75455? di Antom
1. si scrive un PIPPO.BAT, archiviato sotto C:\, che contenga

Codice: Seleziona tutto

CD Directory principale
ATTRIB *.xls /s >C:\pippo123.txt

2. La macro lancera' questo bat; il comando e'

Codice: Seleziona tutto

Shell ("c:\pippo.bat")

Come e' intuibile questo creera' un file C:\pippo123.txt con l' elenco di tutti i file xls disponibili nella directory e subdirectories (come nel post di Antom, solo che lì era un lavoro una tantum e l' elenco si faceva a mano; qui e' un continuo aggiornamento e se non si fa con Shell si diventa vecchi)

3. Importare questo elenco in un foglio del file di ricerca; il codice e' quello prodotto da un Registra nuova macro mentre si eseguono le seguenti operazioni (sempre dal topic di Antom)
-vai sul foglio "attrib"
-seleziona tute le colonne A-B-C-D-E-F-G e cancella
-importa da A1 il contenuto del file pippo123.txt. Procedura:
>seleziona A1
>Menu /Dati /Importa dati esterni /Importa dati
>scegli Tipo file=txt e vai a scegliere in C: il file pippo123.txt; premi Apri
>scegliere Larghezza fissa, Avanti
>togliere eventuali separatori di colonna gia' presenti nell' anteprima dati (sono delle linee verticali sovrapposte al testo, fare doppioclick per cancellarle), e inserirne 1 solo subito prima di c:\ (fare click per inserire)
>premere Fine
-in questo modo avrai in col B, da B1, tutti i file che sono da manipolare
-eliminare la colonna A per avere l' elenco in col A

A questo punto si ripassano uno per uno i files in elenco, e se il nome non contiene "finito":
-si apre il file e per semplicita' se ne memorizzza il nome; dalla macro Antom:

Codice: Seleziona tutto

Workbooks.Open Filename:=NextName, UpdateLinks:=0
OWb = ActiveWorkbook.Name

-si scorrono tutti i fogli del file con un ciclo del tipo

Codice: Seleziona tutto

For I= 1 to ActiveWorkbook.Worksheets.Count

-si legge il nome foglio, e con un If multiplo si verifica se il nome corrisponde a uno di quelli in elenco:

Codice: Seleziona tutto

ShN=Sheets(I).name
If ShN="Gennaio" or ShN="Febbraio" or ...  Then
'istruzioni
End if

-se If e' vero, si apre il foglio e si va a verificare le celle
-se il valore corrisponde, si memorizza il nome foglio sulla prima colonna libera accanto al nome file su foglio Attrib
-si ripete il check foglio (Next I)

-si chiude il file aperto; io farei (va bene non salvare?)

Codice: Seleziona tutto

Workbooks(OWb).Close SaveChanges:=False

-si passa al file successivo del foglio attrib, fino a trovare un vuoto.

Alla fine, sull' elenco ci sono tutti i files con in evidenza i fogli i cui dati corrispondono ai criteri di check.

E vissero felici e contenti.

Se non e' sufficiente, sono qua.

[Tribuno]

Ciao redvin,

che versione hai di Excel?

Il percorso quindi sarà:
C:\Percorso\Sottocartella\SubA\file.xls
C:\Percorso\Sottocartella\SubB\file.xls
.
.
.
C:\Percorso\Sottocartella\SubZ\file.xls

ed in Sottocartella ci sono solo ed esclusivamente i 1500 file da controllare. Corretto? O ci sono altri file excel che non rientrano nella ricerca?

Comunque si può benissimo creare un foglio excel riepilogativo nel quale avrai questi dati dei file che corrispondono ai tuoi criteri: percorso del file (con collegamento ipertestuale), mese in cui J13 e J14 sono = 0 (tutte e due le celle o solo una delle due?), eventualmente anche i valori presenti nelle due celle controllate.

Ciao
Tribuno

[Tribuno]

Ciao Anthony, ho visto che mi hai anticipato, infatti all'inizio avevo indicato quella discussione ed infatti ho chiesto la versione di excel, in quanto, come nella discussione di antom, con excel 2003 si può utilizzare FileSearch, che ne dici?

Ciao
Tribuno

[redvin]

Ciao vi ringrazio proprio tanto del vostro aiuto.
questi tipi di applicazioni le uso sia su excel 2003 che su excel 97, visto che alcuni pc hanno ancora installato il 97 e non vogliono saperne di aggiornarlo.
nelle sottocartelle ci sono solo i files excel che dovrei controllare.
Mi serve vedere i files ed i mesi corrispondenti in cui j13 e j14 (a volte può risultare compilata una sola cella a volte tutte e due) in cui il valore è DIVERSO da zero.
su quelle celle dovrei fare anche delle altre operazioni, però al momento vorrei riuscire a risolvere questo grosso problema che è più urgente e più complicato a mio avviso, visto che non saprei proprio come risolverlo.
grazie tantissimo.
ciao ciao

[Anthony47]

Io lavorerei col Bat, cosi' guardiamo avanti per quando il 2007 sara' piu' comune; e poi assolutamente non so se FileSearch esisteva gia' col 97!

Ciao.

[Tribuno]

Ecco, io ho preparato questa macro:

Codice: Seleziona tutto

Dim Riep As Object, FLNew As Object
Dim FlNewName As String
Dim I As Integer, Rgh As Integer, CL As Integer

Sub Controlla_redvin()
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With
Shell ("C:\pippo.bat") 'Rileva file da controllare
Columns("A:E").Select
Selection.ClearContents 'Cancella dati in foglio riepilogo
Selection.QueryTable.Delete
Range("A2").Select
'Importa dati da file pippo123.txt
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\pippo123.txt", _
        Destination:=Range("A2"))
        .Name = "pippo123"
        .FieldNames = True
         .PreserveFormatting = True
        .RefreshStyle = xlInsertDeleteCells
        .SaveData = True
        .AdjustColumnWidth = True
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileTabDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileFixedColumnWidths = Array(5)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft 'Elimina 1ª colonna creata
Range("A1").Select
'Elimina da foglio riepilogo file finiti
Rgh = 2
While Cells(Rgh, 1) <> ""
    If Right(Cells(Rgh, 1), 10) = "finito.xls" Then
        Rows(Rgh).Delete
    Else
        Rgh = Rgh + 1
    End If
Wend
Columns("A:A").EntireColumn.AutoFit
Range("A1") = "File": Range("B1") = "Mese"
'Controlla file
Set Riep = Workbooks("Riepilogo (redvin).xls").Worksheets("Riepilogo")
Rgh = 2
While Riep.Cells(Rgh, 1) <> ""
    Workbooks.Open Filename:=Riep.Cells(Rgh, 1).Text
    FlNewName = ActiveWorkbook.Name
    CL = 2
    For I = 1 To Worksheets.Count
        If Workbooks(FlNewName).Worksheets(I).Name = "Gennaio" Or Workbooks(FlNewName).Worksheets(I).Name = "Febbraio" _
        Or Workbooks(FlNewName).Worksheets(I).Name = "Marzo" Or Workbooks(FlNewName).Worksheets(I).Name = "Aprile" Then
            If Range("J13") Or Range("J14") <> 0 Then
                Riep.Cells(Rgh, CL) = Workbooks(FlNewName).Worksheets(I).Name
                CL = CL + 1
            End If
        End If
    Next I
    Workbooks(FlNewName).Close SaveChanges:=False
    Rgh = Rgh + 1
Wend
'Crea collegamenti ipertestuali
Rgh = 2
While Riep.Cells(Rgh, 1) <> ""
    Cells(Rgh, 1).Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Cells(Rgh, 1).Text _
    , TextToDisplay:=Cells(Rgh, 1).Text
    Rgh = Rgh + 1
Wend
With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
End Sub


1) prepara il file pippo.bat, come spiegato da Anthony, con il Blcco note e lo salvi con estensione .bat

2) apri un nuovo file di excel, rinominalo "Riepilogo (redvin)" (senza virgolette), il primo foglio lo rinomini "Riepilogo"

3) nell'editor di VBA incolla la macro (Alt+F11---> Inserisci modulo---> nel frame di destra incolli la macro--->Alt+F11)

4) fai partire la macro (Alt+F8 e scegli la macro "Controlla_redvin")

Nel foglio di riepilogo troverai: colonna A il percorso completo del file con il collegamento ipertestuale, nelle colonne da B a E l'indicazione del nome del foglio dove non c'è valore 0 in J13 e/o J14.

Attenzione: nei miei file prova i fogli si chiamano Gennaio, Febbraio, Marzo, Aprile. Se nei tuoi il nome è diverso (es. gennaio oppure gen., ecc.), allora bisogna modificare questa parte del codice:

Codice: Seleziona tutto

If Workbooks(FlNewName).Worksheets(I).Name = "Gennaio" Or Workbooks(FlNewName).Worksheets(I).Name = "Febbraio" _
        Or Workbooks(FlNewName).Worksheets(I).Name = "Marzo" Or Workbooks(FlNewName).Worksheets(I).Name = "Aprile" Then

Ciao
Tribuno

[redvin]

Ciao,
ho provato il metodo di Tribuno, ma quando faccio partire la macro a volte mi esce un errore:
"Errore di run-time '1004':
Errore definito dall'applicazione o dall'oggetto"
e poi ci sono due pulsanti "fine" o "debug".
Se clicco su "debug" mi evidenzia la riga "Selection.QueryTable.Delete".

A volte, invece, parte ma nel foglio "Riepilogo" in A1 inserisce "File" e in B1 "Mese" e niente altro, anche, se effettivamente ci sono foglio che hanno dei valori nelle celle j13 o j14.

da che cosa può dipendere?
Grazie ancora

[Tribuno]

Ciao,
prova a mettere un apice (') davanti all'istruzione Selection.QueryTable.Delete, a questo punto non dovrebbe darti più errore.

Hai verificato il file pippo123.txt per vedere cosa c'è scritto? Si trova in C? I nomi dei fogli da controllare corrispondono a quelli della macro?

Attenzione: nei miei file prova i fogli si chiamano Gennaio, Febbraio, Marzo, Aprile. Se nei tuoi il nome è diverso (es. gennaio oppure gen., ecc.), allora bisogna modificare questa parte del codice:

Codice: Seleziona tutto
If Workbooks(FlNewName).Worksheets(I).Name = "Gennaio" Or Workbooks(FlNewName).Worksheets(I).Name = "Febbraio" _
Or Workbooks(FlNewName).Worksheets(I).Name = "Marzo" Or Workbooks(FlNewName).Worksheets(I).Name = "Aprile" Then

Per adesso non mi viene in mente altro. Fammi sapere su quello che ti ho chiesto.

Ciao
Tribuno

[Tribuno]

Ho fatto altre prove e mi dava un errore in quanto l'esecuzione della macro è più veloce dell'aggiornamento del file pippo123.txt, quindi ho dovuto mettere un piccolo ritardo di 3 secondi.

Questa è la parte iniziale della macro da sostituire

Codice: Seleziona tutto

Sub Controlla_redvin()
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With
Shell ("C:\pippo.bat") 'Rileva file da controllare
OraVer = Now + TimeValue("00:00:03")
Do
Loop While Now < OraVer
Columns("A:E").Select
Selection.ClearContents 'Cancella dati in foglio riepilogo
'Selection.QueryTable.Delete
Range("A2").Select
'Importa dati da file pippo123.txt

Il resto è invariato.

Ciao
Tribuno

[redvin]

Ciao Tribuno,
grazie ancora per avermi aiutata.
Avevo anche io provato a commentare quella riga di codice che dava problemi, ma niente.
I mesi li avevo già sistemati in base alla mia impostazione.
Ora la macro va, solo che il risultato non è proprio quello che dovrei avere.
Mi dovrebbe dare in output solo quei file in cui è presente il valore nella cella J13 o j14 e solo il mese o i mesi in cui si presenta questa caratteristica. L'output della macro, invece, mi da tutti i files che non hanno il nome "finito", e se in uno di questi c'è la caratteristica di avere un valore in J13 o j14 mi da tutti i mesi che ci sono nell'intervallo del codice della macro.
Si può risolvere anche questo problema?
Lo so che ti chiedo tanto.
sei grande e ti ammiro per tutto quello che sai, vorrei avere una parte della tua conoscenza in materia.
Grazie ancora.
ciao Redvin

[Anthony47]

Bene, mi pare che il piu' sia stato fatto da Tribuno, complimenti...

Per il problema dell' elenco, vedi se aggiungendo questa dopo la lunga IF migliora:
If Workbooks(FlNewName).Worksheets(I).Name = "Gennaio" Or Workbooks(FlNewName).Worksheets(I).Name = "Febbraio" _
Or Workbooks(FlNewName).Worksheets(I).Name = "Marzo" Or Workbooks(FlNewName).Worksheets(I).Name = "Aprile" Then '<<< ESISTENTE

Codice: Seleziona tutto

          Sheets(I).select                                           '<<<<<<<<<<<<<<< AGGIUNGERE

If Range("J13") Or Range("J14") <> 0 Then '<<< ESISTENTE


INOLTRE, mi sono sono reso conto che Shell e' un comando asincrono dopo aver postato il suggerimento di usarlo invece del FileSearch; l' attesa di alcuni secondi e' mediamente sufficiente, ma per precauzione si potrebbero variare queste righe:
Shell ("C:\pippo.bat") 'Rileva file da controllare
OraVer = Now + TimeValue("00:00:03")
Do
Loop While Now < OraVer
con queste:

Codice: Seleziona tutto

Dim FDStamp As Date
On Error Resume Next
Kill "C:\pippo123.txt"
Shell ("c:\pippo.bat")
Application.Wait (Now + TimeValue("0:00:05"))
Attesa:
FDStamp = FileDateTime("C:\pippo123.txt")
If IsEmpty(FDStamp) Then GoTo Attesa
If DateDiff("s", FDStamp, Now) < 20 Then GoTo Attesa
On Error GoTo 0

Questo blocco, intanto cancella un eventuale precedente pippo123.txt, poi legge la data/ora di modifica del file e attende fintanto che questa data e' almeno vechia di 20 secondi rispetto a "now" (cioe' per 20 sec il file non e' stato toccato).

Hai gia' provato la macro sui 1500 files?
Ciao.

[redvin]

Ciao,
grazie anche ad Anthony per l'aiuto.
Siete davvero fantastici, mi state riducendo notevolmente il lavoro che dovrei fare manualmente.
Non l'ho ancora provato con i 1500 files, lo sto provando con pochi files.
Ora funziona, mi da solo il mese di riferimento dove è inserita la variazione.
Al momento, però, visualizzo sia i files che hanno il valore nella cella j13 o j14 con il mese di riferimento(e va benissimo), sia i files che non hanno nessun valore in queste celle.
Se non vi porta via tanto tempo si potrebbe sistemare per visualizzare solo i files con il valore?
Ed un'ultimissima cosa, prometto di non chiedervi altre aggiunte, perchè capisco il notevole lavoro e la noia.
Accanto al mese di riferimento si potrebbe avere il valore di un'altra cella (G42) che è collegata con j13 o j14? naturalmente per tutti i mesi che mi escono accanto al file si riferimento(perchè il valore potrebbe cambiare mese per mese)
Ho cercato di scrivere qualcosa io come codice, ma non riesco a capire ancora molte cose, però voglio imparare, è troppo bello!
Grazie ancora per il vostro prezioso aiuto.
Ciao
Redvin

[Anthony47]

Il valore di G42 lo vuoi nella colonna accanto a quella del nome foglio, vero? Se SI, fai questa modifica alla macro (1 aggiunta, 1 modifica):

Codice: Seleziona tutto

            If Range("J13") Or Range("J14") <> 0 Then
                Riep.Cells(Rgh, CL) = Workbooks(FlNewName).Worksheets(I).Name
                Riep.Cells(Rgh, CL+1) = Range("G42").value    '<<<< AGGIUNTA
                CL = CL + 2                                '<<<<<<<<<<<<< MODIFICATA
            End If

Per l' eliminazione delle righe che non hanno nessun foglio evidenziato, prova a farlo col filtro automatico:
-selezioni tutta la colonna B (la prima che dovrebbe contenere un nome foglio), Menu /Dati /Filtro /Filtro automatico, poi dalla freccina in testa a col B scegli "Non vuote". In questo modo le righe senza evidenza saranno nascoste, non cancellate: e' sufficiente?

Ciao.

[redvin]

Ciao Anthony,
grazie! va benissimo!
siete davvero grandi.
Vi ringrazio tantissimo per il vostro aiuto.
Appena organizzo bene il lavoro lo testerò sui miei 1500 files e vi farò sapere.
Ora sui miei files prova va a meraviglia.
Spero un giorno di poter imparare anche io queste cose.
Continuate così.
alla prossima
Ciao Redvin