Export maschera filtrata

Question

Ciao,

sto ancora lavorando al mio database, diciamo che sto cercando di migliorarlo.

Ho un problema che non riesco a risolvere:

Ho creato una maschera per filtrare i record di una tabella ( i filtri sono applicati tramite caselle combinate, caselle di testo e date che ho posizionato nell'intestazione di maschera), nel corpo della maschera visualizzo i risultati. 

Ora quello che vorrei fare è esportare in formato Excel proprio i risultati del filtro, ho provato in diversi modi quello che più si avvicina a quello che vorrei ottenere è questo:

Private Sub cmd_export_excel_Click()

DoCmd.OutputTo acOutputForm, "Search Report", acFormatXLSX, "", False, "", , acExportQualityPrint

End Sub

Il problema è che oltre a esportare i campi che mi servono, nel file Excel mi ritrovo anche le etichette e i comandi che si trovano nell'intestazione di maschera, io vorrei esportare solo il contenuto filtrato che si trova nel corpo della maschera e con lo stesso layout.

Ho bisogno anche di poter scegliere di volta in volta il percorso di salvataggio del file.

Mi potete gentilmente aiutare?

Grazie anticipatamente.

Buon pomeriggio 

Claudio

Answer 1

ciao Claudio,

nell'impostazione del filtro, supponendo fosse presente una tabella prodotti con idprodotto, hai sicuramente indicato :

me.filter="idProdotto=" & me.idprodotto

nella clausola where che vai a concatenare quel idProdotto è associato per esempio alla tabella ordini o dettaglio ordini come chiave esterna.

Devi quindi sostituire idProdotto con l'alias + nome della tabella nella concatenazione del filtro.

esempio:

Dim strSql    As String

Dim strFilter As String

Dim qdf    As DAO.QueryDef

Const queryFilter As String = "QExport"

strFilter = Replace(Me.Filter, "[id prodotto]", "p.[id prodotto]")

strSql ="select p.idprodotto…. from prodotti as P inner join ….  where " & strFilter

supponendo che la tabella prodotti abbia un alias P nel predicato sql

il replace va fatto per tutti i campi oggetto di filtro.

ciao, Sandro.

Answer 2

ciao Claudio,

se devi esportare in xls perche' non appoggiarsi al metodo TransferSpreadsheet ?

L'unico inconveniente e' che non si puo' di default con questo metodo, che sia una tabella o un query, eliminare i campi :

Dim strSql As String

Dim qdf    As DAO.QueryDef

Const queryFilter As String = "QExport"

strSql = "select * from ordini where " & Me.Filter

Set qdf = CurrentDb.CreateQueryDef(Name:=queryFilter, sqlText:=strSql)

DoCmd.TransferSpreadsheet TransferType:=acExport, _

                          SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _

                          TableName:=queryFilter, _

                          FileName:="C:\prova" & Format$(VBA.Now(), "yyyymmdd") & CStr(CLng(VBA.Rnd() * 10000000)) & ".xlsx"

CurrentDb.QueryDefs.Delete Name:=queryFilter

Set qdf = Nothing

personalizza il nome della tabella ordini con quella propria del tuo scenario.

Se proprio proprio le intestazioni ti infastidiscono a me viene in mente l'utilizzo del metdo queryTable membro di Excel, ma serve l'automazione.

Onestamente, preferirrei questa seconda opzione anche perche' puoi maniupolare il worksheet in modo migliore aprendo l'istanza di Excel da Access.

Ovviamente la strada e' piu' complessa.

Se ti serve scegliere il path di destino ti devi appoggiare ad un fileDialog, ma per questo secondo aspetto in caso ti aiuto nel post successivo, prima vediamo l'esportazione.

Facci sapere.

Ciao, Sandro.

Answer 3

Ciao Sandro,

ho eseguito i vari JOIN tra le tabelle interessate, ma l'export ha ancora qualche problemino: se dalla mia maschera imposto i filtri dalle caselle di testo o filtro per data nessun problema riesco ad esportare il mio file excel, ma se provo a filtrare la mia tabella da una qualsiasi casella combinata mi compare un messaggio di errore "Errore di run-time '3079' Il riferimento al campo specificato 'ID_campo' potrebbe essere relativo a più tabelle elencate nella clausola FROM dell'istruzione SQL".

Di seguito ti riporto il codice:

Private Sub ExportExcel_Click()

Dim strSql As String

Dim qdf    As DAO.QueryDef

Const queryFilter As String = "QExport"

strSql = "SELECT div.division as Division, disc.discipline as Discipline, plat.platform as Platform, veh.vehicle_programcode as ProgramCode, " _

& "WT.work_task as WorkTask, [CTS id], comp.component as Component, program_description as [Program Description], " _

& "an.Analyst AS Analyst, [Report name], [Release date], Repository, Note " _

& "FROM (((((((Report AS R INNER JOIN Division AS div ON R.[ID_division] = div.[ID_division]) " _

& "INNER JOIN Discipline AS disc ON R.[ID_discipline] = disc.[ID_discipline]) " _

& "INNER JOIN Platform AS plat ON plat.[ID_platform] = R.[ID_platform]) " _

& "LEFT JOIN Vehicle_ProgramCode AS veh ON veh.ID_vehicle_programcode = R.ID_vehicle_programcode) " _

& "INNER JOIN [Work task] AS WT ON WT.ID_work_task = R.ID_work_task) " _

& "LEFT JOIN Components AS comp ON comp.ID_component = R.ID_component) " _

& "INNER JOIN Analysts AS an ON an.ID_Analyst = R.ID_Analyst) " _

& "WHERE " & Me.Filter & " ORDER BY [Release date]"

Set qdf = CurrentDb.CreateQueryDef(Name:=queryFilter, sqlText:=strSql)

DoCmd.TransferSpreadsheet TransferType:=acExport, _

                          SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _

                          TableName:=queryFilter, _

                          FileName:="C:\prova" & Format$(VBA.Now(), "yyyymmdd") & CStr(CLng(VBA.Rnd() * 10000000)) & ".xlsx"

CurrentDb.QueryDefs.Delete Name:=queryFilter

Set qdf = Nothing

End Sub

Grazie per l'aiuto.

Ciao

Claudio

Answer 4

ciao Claudio,

nel mio esempio ho semplicemente inserito :

strSql = "select * from ordiniwhere " & Me.Filter

come hai giustamente notato se utilizzi delle chiavi esterne, ed esse sono numeriche, l'esportazione in xsl mostra il numero e non la descrizione o quanto maggiormente a te significativo per dare senso a quanto estrattto.

Sviscera maggiormente il predicato sql inserendo in join le varie tabelle a cui fanno riferimento le FK ( foreign key, chiavi esterne ) in modo da consentirti l'estrazione della descrizione ( passami in termine ) e non il valore numerico della chiave stessa.

del tipo: 

select

      O.id_ordine, O.data, do,Articolo

from 

     ordini as O

inner join

     dettagliOrdini as DO

on

      DO.idordine_o.idOrdine

ect....

HTH e facci sapere.

Ciao, Sandro

Answer 5

ciao Claudio,

se devi esportare in xls perche' non appoggiarsi al metodo TransferSpreadsheet ?

L'unico inconveniente e' che non si puo' di default con questo metodo, che sia una tabella o un query, eliminare i campi :

Dim strSql As String

Dim qdf    As DAO.QueryDef

Const queryFilter As String = "QExport"

strSql = "select * from ordini where " & Me.Filter

Set qdf = CurrentDb.CreateQueryDef(Name:=queryFilter, sqlText:=strSql)

DoCmd.TransferSpreadsheet TransferType:=acExport, _

                          SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _

                          TableName:=queryFilter, _

                          FileName:="C:\prova" & Format$(VBA.Now(), "yyyymmdd") & CStr(CLng(VBA.Rnd() * 10000000)) & ".xlsx"

                       

                        

CurrentDb.QueryDefs.Delete Name:=queryFilter

Set qdf = Nothing

personalizza il nome della tabella ordini con quella propria del tuo scenario.

Se proprio proprio le intestazioni ti infastidiscono a me viene in mente l'utilizzo del metdo queryTable membro di Excel, ma serve l'automazione.

Onestamente, preferirrei questa seconda opzione anche perche' puoi maniupolare il worksheet in modo migliore aprendo l'istanza di Excel da Access.

Ovviamente la strada e' piu' complessa.

Se ti serve scegliere il path di destino ti devi appoggiare ad un fileDialog, ma per questo secondo aspetto in caso ti aiuto nel post successivo, prima vediamo l'esportazione.

Facci sapere.

Ciao, Sandro.

Ciao Sandro,

grazie per l'aiuto... In questi giorni avevo letto qualcosa sull'automazione e avevo notato che questa strada era un pò più complessa, magari potrebbe essere il passo successivo per migliorare il database (ho acquistato un testo per approfondire l'argomento).

Ho provato il codice che mi hai inviato e in realtà la formattazione finale del file Excel generato va bene, solo che nel file Excel non trovo tutti i dati che invece ho nella maschera filtrata.

Provo a spiegare meglio quello che ho fatto: prima ho passato allo script la tabella originale su cui è basata la maschera che filtra i miei dati però, dato che ho diverse caselle combinate associate a degli id (che si trovano nella colonna nascosta della casella combinata), nel file Excel trovavo delle colonne con valori numerici, ho poi creato una query con tutti i campi della tabella e l'ho passata allo script, il risultato nei due casi ovviamente è uguale ed è come se i dati venissero filtrati una seconda volta in funzione del primo record della prima colonna, faccio un esempio: supponiamo che la prima colonna (della tabella o della query) sia composta solo da due valori numerici come 1 e 2 (1, 1, 1, 1, 1, 2 , 2, 1, 1) nella maschera filtrata riesco a vedere tutti i dati correttamente mentre nell' Excel esportato è come se i dati venissero filtrati di nuovo in funzione del primo valore che incontra nella colonna cioè la sequenza che mi ritrovo è "1, 1, 1, 1, 1, 1, 1" (con tutti gli altri campi corrispondenti).

Spero di essere stato abbastanza chiaro, scriverlo forse è più difficile.

Dato che lo script funziona bene l'errore dovrebbe essere in quello che gli passo come input (tabella o query), in cosa sbaglio?

Grazie.

Buona giornata, 

Claudio