Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

[Excel] creare database riepilogativo

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

[Excel] creare database riepilogativo

Postdi Rabunin » 09/08/07 11:03

ciao a tutti, ho un quesito da porvi:

ho una cartella (C:\Documents and Settings\XXX\Desktop\Pratiche) che contiene qualche centinaio di file excel (rinominati 001, 002, 003 ecc.ecc.) dove per ognuno mi interessa estrapolare alcuni dati essenziali (identificati per ogni file da alcune celle rinominate "valore1", "valore2", "valore3") per raccogliere questi dati all'interno di un file riepilogativo ("Elenco Pratiche").

Quello che dovrei fare è:

- nel file riepilogativo su ogni riga dovrebbero apparire i dati essenziali di ogni file
- nella prima cella della riga dovrei inserire il nome del file che mi interessa e la riga dovrebbe completarsi automaticamente con i dati "salienti" presenti nel file (es: in A1 scrivo il nome del file "001", e in A2 mi appare "valore2", in A3 "valore3" ecc.ecc.); inoltre questo file riepilogativo dovrebbe anche aggiornarsi ogni volta che lo si apre, andando ad aggiornare i valori che hanno subito modifiche...

ho letto alcuni post del forum e ho visto che qualcosa è possibile tramite macro, ma la mia conoscenza della materia è infima (quindi mi scuso se pongo magari una domanda a cui avete già risposto :oops: ): potete darmi una manina?


grassie in anticipo!
Rabunin
Newbie
 
Post: 8
Iscritto il: 09/08/07 10:51

Sponsor
 

Postdi Anthony47 » 10/08/07 01:34

Un paio di domande: le celle che ti interessa importare nel foglio di riepilogo sono in un unico Foglio di cui conosci il nome? Tutti i File hanno gli stessi fogli (con gli stessi nomi)?

Ciao.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13895
Iscritto il: 21/03/06 16:03
Località: Ivrea

Postdi Rabunin » 10/08/07 07:52

le celle che mi interessa importare sono 4 celle per ogni file, sparse su fogli diversi (i nomi dei fogli li conosco, saranno sempre gli stessi: i file sono identici, cambiano i valori che vi vengono inseriti)

:)
Rabunin
Newbie
 
Post: 8
Iscritto il: 09/08/07 10:51

Postdi Anthony47 » 10/08/07 16:11

Ecco la mia proposta.
1) Nel file RIEPILOGO.xls, in una colonna, inserisci i nomi di tutti i files presenti nella tua cartella; questo sara’ fatto inizialmente tramite una macro di inventario, poi puo’ essere aggiornato a mano (se i file nuovi sono pochi e controllati) o puo’ essere aggiornato tramite un nuovo ciclo di inventario.
2) Nelle celle A1-B1-C1-D1 inserisci i nomi assegnati alle celle che vuoi importare; invece di A1:A4 puoi usare altre colonne contigue (es D1:G1), oppure le colonne possono essere in numero diverso da 4.
3) Una seconda macro popola le celle con le formule di collegamento ai file e ai range elencati.

Per questo userai il seguente codice:
Codice: Seleziona tutto
Sub Inventa()
'
StartFile = "N3"                       '<<<<  Cella da cui si popola l' inventario
'
Range(StartFile).Range("A1:A65000").ClearContents   'Azzera l’ elenco dei file
'
SourceDir = "C:\Documents and Settings\XXX\Desktop\Pratiche\"       '<<<< Aggiustare
Set fs = Application.FileSearch
With fs
    .LookIn = SourceDir
    .SearchSubFolders = False
    .Filename = "*.xls"
If .Execute() = 0 Then
    MsgBox "Zero files in " & SourceDir
    Exit Sub
End If
End With
'Trovati file:
With fs
MsgBox "Ci sono " & .FoundFiles.Count & " file(s) found."    '1
For i = 1 To .FoundFiles.Count
NomeFile = .FoundFiles(i)
Range(StartFile).Offset(i - 1, 0).Value = NomeFile
'MsgBox NomeFile
Next i
End With
End Sub


Codice: Seleziona tutto
Sub Collega()
'
' DEFINIZIONI   <<<<  Variare come da situazione
StartFile = "N3"        'Prima Cella con Nome file da collegare; vedi macro INVENTARIO
NFoglio = "Foglio1"     'Foglio da cui estrarre le info
Compil = "A1:C1"        'Celle con il range da importare
'
'
FileR = Range(StartFile).Row
FileC = Range(StartFile).Column

NCols = Range(Compil).Columns.Count
Set AreaFile = Intersect(Range(StartFile, Cells(FileR + 65000, FileC)), ActiveSheet.UsedRange)
stacol = Range(Compil).Range("A1").Column

AreaFile.Select
For Each NFile In AreaFile
If NFile = "" Then GoTo Esci
CuRiga = NFile.Row
CuFILE = Mid(NFile, InStrRev(NFile, "\", -1, vbTextCompare) + 1, 99)
CuDIR = Replace(NFile, CuFILE, "")
For J = 0 To NCols - 1
Colleg = Chr(39) & CuDIR & "[" & CuFILE & "]" & NFoglio & Chr(39) & "!" & Range(Compil).Range("A1").Offset(0, J).Value
Cells(CuRiga, stacol + J).Select
'MsgBox (Colleg)
ActiveCell.Formula = "=" & Colleg
Next J
Next NFile

Esci:
Range(Cells(Selection.Row + 1, stacol), Cells(65000 - Selection.Row, stacol + NCols - 1)).ClearContents

End Sub


Procedura:
-Da Excel apri il vba editor con Alt-F11
-Menu /Inserisci /Modulo
-Copia il codice e incollalo nel frame bianco di dx del vba editor
-eventualmente assegna le macro a due pulsanti o a due tasti di scelta rapida

“Inventa” esegue l’ inventario e “Collega” inserisce le formule di collegamento.
Dai uno sguardo alle istruzioni marcate <<<<, perche’ vanno adattate:
StartFile = "N3" Ho ipotizzato che l’ inventario si faccia in colonna N da riga 3 (sia su Inventa che su Collega)
NFoglio = "Foglio1" Qui inserisci uno qualsiasi dei fogli presenti sui file da cui attingi
Compil = "A1:C1" Qui definisci su quali celle contigue di riga 1 sono compilate con i nome delle celle da cui prelevare i dati; le formule che importeranno i relativi valori saranno messe in queste colonne da riga 3 (se StartFile = “XX3”) in giu’, fintanto che non si trova un vuoto sull’ elenco dei file.


Uso:
Personalizzare le macro con i parametri detti prima
Inserisci su riga 1 i nomi dei range da cui leggere i dati

Eseguire la macro Inventa, che mettera’ l’ elenco dei file nella colonna prescelta
Eseguire la macro Collega, che riempira’ le celle con i collegamenti necessari.

La riga 2 l’ ho lasciata libera pensando che potesse essere usata per inserire eventuali intestazioni.

Se vuoi avere in qualche colonna il solo NOME del file, puoi fare così:
-in una cella, es N1, scrivi la directory (nome della cartella; nel tuo caso C:\Documents and Settings\XXX\Desktop\Pratiche\)
-nella cella prescelta scrivi la formula
Codice: Seleziona tutto
=SOSTITUISCI(N3;$N$1;"")
(se l’ inventario e’ formato da N3 in giu’)
-poi copi questa formula nelle celle sottostanti, quanto basta.

Infine, prima di salvare il file:
-Menu /Modifica /Collegamenti
-premi Prompt di avvio e setta l’ opzione che preferisci.

Prova quanto suggerito, e fai sapere!
Ciao.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13895
Iscritto il: 21/03/06 16:03
Località: Ivrea

Postdi Rabunin » 17/08/07 11:29

scusate se rispondo solo adesso, ma le ferie chiamavano (ahhh... sono già finite!!!)


che dire Anthony... se fossi donna mi sarei già innamorata!!!
NON SOLO le macro che mi hai suggerito sono perfette,
MA me le hai spiegate talmente bene (a me profano della materia) che sto riuscendo anche a fare quelle minime modifiche per adattarle al mio scopo...


GRAZIE GRAZIE GRAZIE

(di solito su questi forum quando qualche niubbo pone una domanda tecnica non viene nemmeno preso in considerazione... qui invece ho ricevuto subito una risposta precisa, ben spiegata e perfettamente funzionale... d'ora in poi quando dovrò rompere "le coconas" saprò dove postare le mie domande!)
Rabunin
Newbie
 
Post: 8
Iscritto il: 09/08/07 10:51

Postdi Rabunin » 17/08/07 13:37

uhm... tanto che ci sono continuo con le domandine....

1- è possibile fare in modo che le celle già "popolate" non vengano + prese in considerazione al passaggio successivo?

2- se la ricerca trova un file a cui manca il "foglio 1" (quello contente i dati che servono) mi da un errore... non potrebbe saltarlo e proseguire a quello successivo, ignorandolo?
Rabunin
Newbie
 
Post: 8
Iscritto il: 09/08/07 10:51

Postdi Anthony47 » 20/08/07 23:18

Per quanto riguarda la 1, quale e’ l’ obiettivo? Parli della sola macro Collega, nell’ ipotesi che l’ elenco file venga aggiornato manualmente, giusto?

Per la 2, metti “Skippa:” (senza virgolette) tra Next J e Next NFile e “On Error Go to Skippa” (senza virgolette) dopo For Each NFile In AreaFile.

Ciao.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13895
Iscritto il: 21/03/06 16:03
Località: Ivrea

Postdi Rabunin » 21/08/07 08:21

1- l'obiettivo sarebbe quello di non appesantire il processo, facendogli saltare quei file di cui ha già recuperato precedentemente i valori (i file da analizzare sono tanti, circa 500, e dover ogni volta aggiornare anche i file già catalogati porta via tanto tempo).

2- ho provato ad inserire i comandi che mi hai consigliato ma mi segnala un errore di sintassi...
Rabunin
Newbie
 
Post: 8
Iscritto il: 09/08/07 10:51

Postdi Anthony47 » 22/08/07 00:42

Per l' "errore di sintassi", hai inserito le istruzioni in una nuova riga nelle posizioni indicate.

Per l' altro problema, si puo' fare, ma:
-sono in viaggio e quindi mi serve qualche giorno
-chi garantisce che i file e le formule siano rimasti allineati?

Ciao.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13895
Iscritto il: 21/03/06 16:03
Località: Ivrea

Postdi Anthony47 » 22/08/07 00:47

Correzione:
Per l' "errore di sintassi", hai inserito le istruzioni in una nuova riga nelle posizioni indicate???

Ciao.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13895
Iscritto il: 21/03/06 16:03
Località: Ivrea

Postdi Anthony47 » 26/08/07 22:25

Ciao Rabunin,
avevi chiesto di mantenere buone le celle gia’ popolate; per questo dovrebbe essere sufficiente inserire l’ istruzione “If ActiveCell.Formula …” in questa sequenza della macro Collega:

Codice: Seleziona tutto
. . . .
Cells(CuRiga, stacol + J).Select
If ActiveCell.Formula <> "" Then Exit For
'MsgBox (Colleg)
ActiveCell.Formula = "=" & Colleg
. . . .


Non so se il problema di errore di sintassi e’ ancora presente…
Ciao, fai sapere!
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13895
Iscritto il: 21/03/06 16:03
Località: Ivrea

Postdi Rabunin » 30/08/07 10:23

Anthony47 ha scritto:Ciao Rabunin,
avevi chiesto di mantenere buone le celle gia’ popolate; per questo dovrebbe essere sufficiente inserire l’ istruzione “If ActiveCell.Formula …” in questa sequenza della macro Collega:

Codice: Seleziona tutto
. . . .
Cells(CuRiga, stacol + J).Select
If ActiveCell.Formula <> "" Then Exit For
'MsgBox (Colleg)
ActiveCell.Formula = "=" & Colleg
. . . .


Non so se il problema di errore di sintassi e’ ancora presente…
Ciao, fai sapere!


- PROBLEMA ERRORE DI SINTASSI:
ho inserito i due comandi da te suggeriti (On Error Go to Skippa ; Skippa:) nelle posizioni da te indicate, in una nuova riga.
L'errore che mi da è: "Errore di compilazione: errore di sintassi", e mi apre visual basic evidenziandomi la riga relativa a "On Error Go to Skippa".

- COMANDO If ActiveCell.Formula …
ho inserito il codice tra le due righe da te indicate (in una nuova riga), e quando avvio la macro mi da il seguente errore: Errore di Runtime '1004': errore definito dall'applicazione o dall'oggetto.
Se clicco su Debug mi evidenzia "If ActiveCell.Formula <> "" Then"

Nell'eventualità stia sbagliando io a inserire qualcosa, ti mando l'intero testo della macro così come risulta con entrambe le modifiche da te consigliate:

Sub Collega()
'
' DEFINIZIONI <<<< Variare come da situazione
StartFile = "K7" 'Prima Cella con Nome file da collegare; vedi macro INVENTARIO
NFoglio = "dati (NON STAMPARE)" 'Foglio da cui estrarre le info
Compil = "A5:J5" 'Celle con il range da importare
'
'
FileR = Range(StartFile).Row
FileC = Range(StartFile).Column

NCols = Range(Compil).Columns.Count
Set AreaFile = Intersect(Range(StartFile, Cells(FileR + 65000, FileC)), ActiveSheet.UsedRange)
stacol = Range(Compil).Range("A1").Column

AreaFile.Select
For Each NFile In AreaFile
On Error Go to Skippa
If NFile = "" Then GoTo Esci
CuRiga = NFile.Row
CuFILE = Mid(NFile, InStrRev(NFile, "", -1, vbTextCompare) + 1, 99)
CuDIR = Replace(NFile, CuFILE, "")
For J = 0 To NCols - 1
Colleg = Chr(39) & CuDIR & "[" & CuFILE & "]" & NFoglio & Chr(39) & "!" & Range(Compil).Range("A1").Offset(0, J).Value
Cells(CuRiga, stacol + J).Select
If ActiveCell.Formula <> "" Then Exit For
'MsgBox (Colleg)
ActiveCell.Formula = "=" & Colleg
Next J
Skippa:
Next NFile

Esci:
Range(Cells(Selection.Row + 1, stacol), Cells(65000 - Selection.Row, stacol + NCols - 1)).ClearContents

End Sub


grazie ancora per il tempo che mi stai dedicando!
Rabunin
Newbie
 
Post: 8
Iscritto il: 09/08/07 10:51

Postdi Anthony47 » 30/08/07 11:38

Allora, per l' errore di sintassi, invece di "On Error Go to Skippa" devi scrivere "On Error Goto Skippa". Tra l' altro, leggendo bene il quesito (saltare i file che non contengono un certo Foglio), e' possibile che questo sistema non risolva, ma comunque non fara' male.

Per l' Errore di Runtime '1004' su "If ActiveCell.Formula <> "" Then Exit For", ho copiato la macro da te postata e la eseguo correttamente, compreso saltare le righe che hanno gia' un collegamento.

Che versione di excel usi?
Puoi descrivere in che momento ti va in debug?

Ciao.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13895
Iscritto il: 21/03/06 16:03
Località: Ivrea

Postdi Rabunin » 10/10/07 18:44

Rieccomi dopo tanto tempo... sono di nuovo a richiedere aiuto: sto utilizzando le macro che mi avevi fornito (sono utilissime!); fino ad ora ho cercato di "aggirare" il problema della continua rielaborazione di file già scritti (praticamente faccio un continuo copia e incolla).


per cercare di ovviare al problema, torno alla carica per chiederti ancora consiglio:
1- mi chiedevi quale fosse la mia versione di excel: uso Excel 2003 SP2
2- in errore mi va quando clicco sul pulsante relativo alla macro collega (con inventario mi conta i file presenti nella directory in maniera corretta), ma se clicco su collegga riparte a generarmi tutto dall'inizio, riscrivendo anche i dati già precedentemente importati dai vecchi file (il numero di questi file sta diventando elevato, e ogni volta devo lasciare il pc a macinare, magari rigenerando 400-500 file per doverne aggiungere solamente 1 nuovo... è un delirio!)
Rabunin
Newbie
 
Post: 8
Iscritto il: 09/08/07 10:51

Postdi Anthony47 » 12/10/07 16:33

Dopo tanto tempo devo fare nuovamente mente locale… inoltre non mi sono tenuto l’ ambiente di test su cui avevo lavorato.
Insomma devi pazientare qualche giorno. Ciao.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13895
Iscritto il: 21/03/06 16:03
Località: Ivrea

Postdi Rabunin » 12/10/07 16:48

Anthony47 ha scritto:Dopo tanto tempo devo fare nuovamente mente locale… inoltre non mi sono tenuto l’ ambiente di test su cui avevo lavorato.
Insomma devi pazientare qualche giorno. Ciao.


figurati, nessunissimo problema.... anzi, ti ringrazio per l'interessamento
Rabunin
Newbie
 
Post: 8
Iscritto il: 09/08/07 10:51


Torna a Applicazioni Office Windows


Topic correlati a "[Excel] creare database riepilogativo":


Chi c’è in linea

Visitano il forum: Nessuno e 11 ospiti