[Risolto] Filtrare con criterio intestazione colonna

[Nicco]

Buongiorno, sto provando a creare una macro per filtrare un database ma sono alle prime armi e non ho trovato in rete niente che mi faccia arrivare al mio obbiettivo.
Avrei bisogno di filtrare un database in base all'intestazione della colonna. Vi allego un file che ho preparato come esempio:
Il foglio "DB" contiene il database da filtrare, il "foglio1" contiene un elenco selezionabile in "A1" che deve essere il criterio di filtro (l'elenco a,b,c, corrispondono alle intestazioni delle colonne del database).
Nel "foglio2" ho incollato quello che dovrebbe essere l'obbiettivo, ovvero, premendo il pulsante giallo "importa tabella" la macro dovrebbe filtrare il database, copiare il risultato ed incollarlo nella cella "A4", poi ripristinare il filtro.
Ho già provato a scrivere la macro ma mi vergogno quasi a postarla, mi piacerebbe imparare a scriverla piuttosto che ricevere la soluzione e basta, anche perchè la dovrò applicare ad un documento molto più complesso.

Grazie

[Gaetanopr]

Hai detto che vuoi imparare a scriverla, quindi ti spiego come farei io, se i dati fossero incolonnati uno dietro l'altro potresti usare il filtro standard copiando i dati su un altro foglio, ma nel file sono un pò sparsi ovunque, innanzitutto creerei tante aree, tanti nomi definiti quanti sono gli elementi, da esempio sono a - b - c poi userei i metodi GetDataArray e setDataArray che si applicano per copiare interi range di celle da un'area di origine a un'area di destinazione, l'origine sarebbe la scelta effettuata nel menù a tendina, se scegli l'elemento a andrai a copiare il range di celle con il nome definito AREAA e la destinazione sarà a partire da C4 estendendo l'area tante quante righe e colonne compongono l'area di origine.
Se fai una ricerca sul forum troverai tanti esempi di come usare questo metodi che ti ho menzionato, oppure puoi usare il metodo copyrange.

[Nicco]

Grazie Gaetano per la risposta, il problema è che nel documento originale il database è composto da più di 1000 tabelle, quindi mi risulterebbe un po complicato denominare ogni singolo range a mano. Per questo motivo avevo pensato ad una macro, il problema è che non riesco a scrivere la macro che mi crei i range denominati come il primo rigo della tabella e che comprendano tutte le colonne con quella intestazione.
Per riferirmi al file di esempio:
Nel foglio DB io ho 3 tabelle, nella riga 4 ho tutte le intestazioni, quando vado a definire i range con la seguente macro

Codice: Seleziona tutto

Sub AddManyNamedRanges()

Dim oSheet 'Sheet that contains the named range.
Dim oAddress 'Range address.
Dim oRanges 'The NamedRanges property.
Dim oRange 'Single cell range.

oRanges = ThisComponent.NamedRanges
oSheet = ThisComponent.getSheets().getByName("DB")
oRange = oSheet.getCellRangeByName("B4:M40")
oAddress = oRange.getRangeAddress()
oRanges.addNewFromTitles(oAddress, _
com.sun.star.sheet.Border.TOP)

End Sub

ottengo i range con il giusto nome, ma contengono solo l'ultima colonna di ogni tabella (colonne D, I, M), ho provato anche a unire le celle che contengono l'intestazione in modo da avere una sola cella di riferimento ma non funziona ugualmente.
Una volta filtrata la giusta tabella, potrei applicare il tuo suggerimento, ma se non riesco a definire i range in modo corretto tramite una macro dovrei definirli tutti a mano

[Gaetanopr]

Per scrivere un codice funzionante bisognerebbe avere altre informazioni, ad esempio l'esempio rispecchia l'originale? l'ultima colonna è sempre colorata? un problema potrebbe essere la presenza di righe vuote all'interno delle tabelle come la riga 21 del range c.
Le altre tabelle come sono disposte?

[Nicco]

Si, l'esempio rispecchia l'originale, le celle che ho colorato in giallo contengono formule, quindi in realtà righe completamente vuote non ce ne sono. Nell'originale tutte le tabelle sono allineate con l'intestazione in riga 4 come nell'esempio, l'unica differenza è che le tabelle sono tutte colorate con colori differenti a seconda se la cella contiene formule, deve essere compilata, o contiene del testo. Ogni tabella ha quindi celle colorate ma in differenti posizioni.
Faccio prima ad allegare un estratto del database originale...

Nel Foglio1 trovi un estratto del database che continuerebbe per un totale di circa 1000 tabelle diverse tra loro.
Nel foglio2 le celle verdi contengono un menu a tendina, in base alle scelte fatte ottengo un codice in C2 che deve essere il criterio con cui filtrare la tabella giusta. La tabella filtrata vorrei fosse copiata ed incollata in A7 del Foglio2.

EDIT (Non so se può essere utile come informazione: In C2 ho una formula =CONCATENA())

Nell' file allegato il sistema di assegnazione codici e gli elenchi nei menu a tendina non funzionano perche ho copiato e incollato in un nuovo documento per evitare di allegare il file originale che contiene dati che non vorrei pubblicare, ma penso che non serva per creare la macro di cui parliamo.

[Gaetanopr]

Nell'originale tutte le tabelle sono allineate con l'intestazione in riga 4 come nell'esempio, l'unica differenza è che le tabelle sono tutte colorate con colori differenti a seconda se la cella contiene formule, deve essere compilata, o contiene del testo. Ogni tabella ha quindi celle colorate ma in differenti posizioni.

Se sono tutte allineate sulla riga 4 diventa semplice, basta fare un ciclo dalla prima colonna all'ultima colonna compilata, verifichi che la cella sia compilata(inizio AREA) e che la prossima sia uguale alla precedente (1000A 1000A) la prima cella vuota che incontri segnerà la fine dell' AREA quindi colonna B inizio AREA, colonna C fine AREA, ti ricavi ultima cella compilata della prima colonna (B) e il range sarà B4:C34 e prosegui, la successiva colonna compilata è la F(inizio AREA) e così via.

[Nicco]

Perdonami ma non riesco a venirne a capo, ho cercato in rete come fare quello che mi hai spiegato ma non arrivo a nulla. Cosa intendi per fare un ciclo? Dovrei fare una macro che individui l'inizio e la fine del range e gli assegna il nome giusto? questa poi potrebbe "looppare" fino all'ultimo range, o ne andrebbe scritta una per ogni range? in questo caso il mio problema è sempre il solito... ho piu di 1000 tabelle...
Io non voglio approfittare della vostra disponibilità ma non ho abbastanza competenze per scrivere la macro da solo... è vero che vorrei imparare ma magari se partissi da qualcosa di già scritto potrei provare a capirne il funzionamento, chiedo scusa ma così non riesco.

[Gaetanopr]

Dovrei fare una macro che individui l'inizio e la fine del range e gli assegna il nome giusto?

Esatto
La macro l'avevo già scritta, ti allego il file, lanciala e vedi il risultato, forse dovrai adattarla, nell'esempio le aree iniziano da riga 8 nel tuo originale forse da riga 4 dicevi.
La macro crea i nomi definiti, se va bene devi creare un'altra macro per filtrare i dati.

[Nicco]

Grazie Gaetano, purtroppo ricevo l' errore allegato

[Gaetanopr]

Purtroppo non avevo provato con Libreoffice, con OpenOffice funziona, in pratica Libreoffice non fa creare nomi definiti iniziando con i numeri, cambia questa parte

Codice: Seleziona tutto

Codicearea = oSheet.getCellByPosition(i, Riga).String

così

Codice: Seleziona tutto

Codicearea = "AREA" & oSheet.getCellByPosition(i, Riga).String

[Nicco]

Per il primo e secondo intervallo funziona bene, dal terzo in poi non seleziona il range corretto. allego immagine

[Gaetanopr]

Strano a me li seleziona correttamente, vedi file allegato
In ogni caso cambiala con questa, l'ho modificata in modo tale da considerare eventuali modifiche dei range.

Codice: Seleziona tutto

Sub AddManyNamedRanges()

Dim oSheet 'Sheet that contains the named range.
Dim oAddress 
Dim oRanges As Object
Dim oRange 
Dim c as Object
Dim oDir As New com.sun.star.table.CellRangeAddress
Dim Codicearea as string

oSheet = ThisComponent.getSheets().getByName("Foglio1")
c = oSheet.createCursor
c.gotoEndOfUsedArea(false)
LastRow = c.RangeAddress.EndRow
LastCol = c.RangeAddress.EndColumn
Riga = 7 ' Riga 8
oRanges = ThisComponent.NamedRanges
oCellAddress = ThisComponent.Sheets.getByindex(0).getCellRangeByName("A1").getCellAddress()

 For i = 1 To LastCol
   If oSheet.getCellByPosition(i, Riga).String <> "" Then
      ColIn = i 
      For y = LastRow To Riga Step - 1
        If oSheet.getCellbyposition(ColIn, y).String <> "" Then 
           UltRiga = y
           exit for
        End if   
      Next y
      Codicearea = "AREA" & oSheet.getCellByPosition(i, Riga).String
      For x = ColIn To LastCol
        If oSheet.getCellByPosition(x, Riga).String = "" Then
           ColFine = x - 1
           	With oDir
	         	.Sheet = 0		
	        	.StartColumn = ColIn
	        	.EndColumn = ColFine
	        	.StartRow = Riga
		        .EndRow = UltRiga		
            End With
            If Not oRanges.hasByName(Codicearea) then
               Range = oSheet.getCellRangeByPosition(oDir.StartColumn, oDir.StartRow, oDir.EndColumn, oDir.EndRow) 
               oRanges.addNewByName(Codicearea, Range.AbsoluteName, oCellAddress, 0)
               i = x' + 1
               Exit for
            Else
              oRanges.removeByName(Codicearea)
            End if    
        End if    
      Next x
   end if
       
 Next i

End Sub

[Nicco]

Spettacolo! ora funziona ed abbiamo i range denominati. L'unica cosa che non capisco è perché non mi riconosce l'ultima tabella, ne ho 8 nell'esempio e mi nomina solo 7 aree. Comunque non è un problema, è bastato incollare una tabella "finta" a caso in fondo e mi nomina anche l'ottava tabella..
Ora che abbiamo tutti i range denominati Area123456 devo dirgli di copiare ed incollare in una certa posizione l'area richiamata dalla cella foglio2, C2.
Dopo ore di tentativi e un forte mal di testa ho partorito questo... sembra funzionare, mi daresti un occhio se ho scritto fesserie?

Codice: Seleziona tutto

Sub CopiaRange()


Dim oSheetincolla
Dim oRangeAddress
Dim oCellAddress
Dim oSheetCriterio
Dim oCellcriterio as object
Dim cellacriteriocopia as String

oCellcriterio = ThisComponent.Sheets.getByindex(1).getCellByPosition(2,1)
cellacriteriocopia = "AREA" & oCellcriterio.string

oSheetCriterio = ThisComponent.getSheets().getByName("Foglio1")
oRangeAddress = oSheetCriterio.getCellRangeByName(cellacriteriocopia).getRangeAddress()


oSheetincolla = ThisComponent.getSheets().getByName("Foglio2")
oCellAddress = oSheetincolla.getCellByposition(0,6).getCellAddress()
oSheetcriterio.copyRange(oCellAddress, oRangeAddress)

End Sub

Sto continuando ad usare il file "estratto originale" per fare le prove, quello che ho allegato qualche post fa, su quello dovrebbe funzionare.

[Gaetanopr]

Si la macro è corretta, però con questo sistema si copiano le formule e non il risultato di esso, non so se è quello che ti serve dato che avresti riferimenti errati.
Inoltre devi aggiungere all'inizio il codice per cancellare la precedente ripresa.

[Nicco]

Le tabelle che devo incollare dal database sono tabelle che mi serviranno per inserire i dati di produzione giornaliera. Nei menu a tendina scelgo il tipo di produzione, importo la tabella relativa a quel tipo di produzione e poi inserisco i dati della produzione giornaliera. Per questo motivo le formule contenute nelle tabelle mi servono solo una volta inseriti i dati di produzione, quindi in definitiva, si, è coretto che mi incolli le formule. In teoria dovrebbero funzionare perché ho lasciato tutti i riferimenti relativi.
Non finirò mai di ringraziarti Gaetano, senza il tuo aiuto non sarei arrivato da nessuna parte.
Ho pure imparato qualcosa, ma il tuo codice per individuare i range non riesco comunque a capirlo.
Metto risolto...
EDIT

Inoltre devi aggiungere all'inizio il codice per cancellare la precedente ripresa.

Mi era sfuggito....cosa intendi?

[Gaetanopr]

Intendo dire che devi ripulire l'area copiata del foglio2 prima di procedere ad un'altra ripresa.

[Gaetanopr]

Ho pure imparato qualcosa, ma il tuo codice per individuare i range non riesco comunque a capirlo.
Metto risolto.

Appena ho due minuti riallego codice commentato.

[Nicco]

Intendo dire che devi ripulire l'area copiata del foglio2 prima di procedere ad un'altra ripresa.

E' per quello che se clicco una seconda volta il pulsante non succede nulla?
Nella versione originale del file che sto testando ora... e pare funzionare... io avrei 6 menu a tendina ogni foglio giornaliero con 6 pulsanti differenti a cui è associata una macro diversa (cambiano i riferimenti per il copia incolla). Se ho capito quello che mi stai dicendo, i 6 pulsanti una volta ciascuno dovrebbero funzionare, il problema è che poi avrei la stessa macro del primo pulsante nel foglio del giorno successivo e non mi funzionerebbe? Giusto?
P.s. ho adattato la macro con i riferimenti al foglio attivo.

Comunque ora provo

[Gaetanopr]

Il migliore sistema è creare una sola macro è associarla all'evento contenuto modificato della cella o celle che contengono i menù a tendina e da lì ricavare il foglio dove avviene la modifica e copiare su questo, non so se sono stato chiaro.

[Nicco]

Non ho ben capito quello che hai detto, comunque ho provato e funziona perfettamente. La macro che hai fatto tu, per assegnare i nomi ai range, la devo lanciare una sola volta visto che il database non dovrebbe cambiare mai.
una volta assegnato i nomi alle varie aree quella macro li non mi serve più. almeno che non debba per qualche ragione modificare il database, a quel punto dovrei cancellare tutte le aree nominate e rilanciare la macro una volta. Anche se, pensandoci bene, se dovessi tipo aggiungere una tabella al database, a quel punto faccio prima a premere ctrl+F3 e assegnarle il nome, per una sola si può fare.

Poi ho fatto 6 macro differenti per ogni pulsante che ho (ho la possibilità di fare fino a 6 produzioni diverse al giorno). Le macro in realtà sarebbero identiche, cambia solo il riferimento di cella per la copia e quello per incolla). Ho creato così il foglio "giornaliero" che ho duplicato per 365 volte e rinominato con 1,2,3,4, etc... Le formule delle macro sono impostate su

Codice: Seleziona tutto

= ThisComponent.currentcontroller.getActiveSheet().getCellRangeByName("P36")

quindi mi funzionano sul foglio attivo. A mio parere funziona tutto, non capisco cosa potrebbe funzionare meglio se collegato all'evento.

[Gaetanopr]

... a quel punto dovrei cancellare tutte le aree nominate e rilanciare la macro una volta

Non occorre già la macro cancella le precedenti aree, tranne che non devi eliminarla completamente.

quindi mi funzionano sul foglio attivo. A mio parere funziona tutto, non capisco cosa potrebbe funzionare meglio se collegato all'evento.

In sostanza si crea una sola macro che agisce sulla cella del menù a tendina(la macro non verrebbe azionata da un pulsante ma si azionerebbe a seguito della scelta effettuata tramite menù a tendina, la macro analizzerebbe quale cella è stata modificata e agirebbe di conseguenza.
Comunque se non conosci questo evento ti verrà difficile capire, puoi approfondire l'argomento cercando sul forum.

[Nicco]

Non occorre già la macro cancella le precedenti aree, tranne che non devi eliminarla completamente.

Ok perfetto tanto meglio.
Ti ringrazio ancora, metto risolto e, per il momento non ti disturbo più, sono più che soddisfatto!

[Gaetanopr]

alla prossima