pivot table view

Hai un problema con Excel?

Nuova discussione

pivot table view
di Random88 (utente non iscritto) data: 25/02/2014 17:11:24
AIUTOOO..Ragazzi sto impazzendo.. Questo pezzo di codice trasferisce una query da access in excel in pivot table view. Il problema è che non riesco a puntare un foglio predefinito. Infatti TransferSpreadsheet punta sul foglio Filename e funziona ma riporta la query in formato tabella non in pivot table... RunCommand invece funziona ma apre una nuova istanza di excel per ogni query.. Avete qualche idea'?? Vi ringrazio..

Function ESPORTA_IN_EXCEL()
On Error GoTo ESPORTA_IN_EXCEL_Err

    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    
    Dim objXL As Object
    Dim xlWB As Object
    
    report_dir = "C:"
    FileName = report_dir & "Report" & Format(Date, "yyyymmdd") & ".xls"
    outputfilename = report_dir & FileName
    
    For Each obj In dbs.AllQueries
       DoCmd.OpenQuery obj.Name, acViewPivotTable, acEdit
       'DoCmd.RunCommand acCmdPivotTableExportToExcel
       DoCmd.TransferSpreadsheet acExport, 9, obj.Name, FileName, False
       DoCmd.Close acQuery, obj.Name, acSaveNo
    Next obj

ESPORTA_IN_EXCEL_Exit:
    Exit Function

ESPORTA_IN_EXCEL_Err:
    MsgBox Error$
    Resume ESPORTA_IN_EXCEL_Exit

End Function

di Vecchio Frac data: 25/02/2014 22:27:37
Questo quesito è interessante.

di Random88 (utente non iscritto) data: 25/02/2014 22:32:28
:) Bene.. dopo tanto sono arrivato ad una prima soluzione.. Non è perfetta.. Adesso veramente ho bisogno del vostro aiuto.. Ogni idea è ben accetta :).. Quello che vorrei fare è di scrivere ogni query in formato pivot view sullo stesso file excel cambiando solo lo sheet. QUesta macro attualmente salva un file per ogni query.

Function ESPORTA_IN_EXCEL()
On Error GoTo ESPORTA_IN_EXCEL_Err

    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    Dim strPath As String
    
    Dim XlsApp As Object
    Dim xlSheet As Object
    Dim xlWorkbook As Object
    
    report_dir = "C:"
    strFileName = report_dir & "Report" & Format(Date, "yyyymmdd") & ".xls"
    
    Set XlsApp = CreateObject("Excel.Application")
    For Each obj In dbs.AllQueries
       DoCmd.OpenQuery obj.Name, acViewPivotTable, acEdit
       DoCmd.RunCommand acCmdPivotTableExportToExcel
       XlsApp.Visible = True
       Set xlSheet = XlsApp.Workbooks(1).Sheets(1)
       'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, obj.Name, FileName, True
       'DoCmd.TransferSpreadsheet acExport, 9, obj.Name, FileName, False
       DoCmd.Close acQuery, obj.Name, acSaveNo
       XlsApp.Workbooks(1).SaveAs FileName:=strFileName
    Next obj
     
     
ESPORTA_IN_EXCEL_Exit:
    Exit Function

ESPORTA_IN_EXCEL_Err:
    MsgBox Error$
    Resume ESPORTA_IN_EXCEL_Exit

End Function

di Vecchio Frac data: 25/02/2014 22:34:16
Stavo facendo prove.
Mi ero accorto intanto che il secondo parametro di TransferSpreadSheet doveva essere diverso.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, obj.Name, filename, False

Ma hai già corretto vedo.
di Random88 (utente non iscritto) data: 25/02/2014 22:40:15
Ciao vecchio Frac :) Grazie! devo ammettere che oggi ho passato un bel po' di tempo su questo problema.. non sono molto vicino al vba di access.. In ogni modo brevemente, come saprai, DoCmd.TransferSpreadsheet mi permette di trasferire in exel una query non mantenendo però la struttura in pivot view ma semplicemente come tabella,,in questo caso però sarà possibile indirizzare il file in un path definito e con nome a piacere... invece DoCmd.RunCommand acCmdPivotTableExportToExcel permette di mantenere il formato ma non è possibile settare il path e il nome del file da produrre. Dopo un bel po' sono riuscito a produrre questa soluzione che in sostanza mi salva il file exel che attivo. Tuttavia ad ogni giro di query che eseguo ovviamente mi chiede di sovrascrivere il file.. Io vorrei semplicemente aggiungere in uno sheet e scrivere ogni risultato a seguire.. se hai voglia dammi qualche ideA :) GRAZIEE!
di Random88 (utente non iscritto) data: 25/02/2014 22:41:28
Si esatto infatti in questa versione l'ho commentato.. :) cosa ne pensi?
di Random88 (utente non iscritto) data: 26/02/2014 11:07:10
Niente ragazzi?? Non riesco a risolvere
di Vecchio Frac data: 26/02/2014 11:47:10
Sì... sono a buon punto, mi serve ancora un po' di tempo.
Adesso il capo mi ha chiamato e devo sospendere... ma ci arrivo :(
di Random88 (utente non iscritto) data: 26/02/2014 12:54:51
Grazie mille :)!! Ovviamente ci sto lavorando anche io.. Chi ha una soluzione prima la posta.. Grazie ancora!!

di Vecchio Frac data: 26/02/2014 15:35:45
Eureka :)
Il codice va un po' ottimizzato per il tuo scenario.
Nota bene che la destinazione della tabella pivot è la cella F1 del foglio in cui risiede la tabella dei dati.
La destinazione andava messa come riferimento R1C1 invece che A1.
Inoltre questa versione funziona per Excel 2003, per versioni superiori cambiano un po' i metodi di PivotCaches (che non ammette più Add ma Create e aggiunge un altro parametro che ora non ricordo) e allora bisogna adattare il codice di conseguenza.
Ma dalle prove fatte tutto sembra funzionare.
Ora bisogna vedere di adattarlo alle tue necessità.

Option Compare Database
Option Explicit

Function ESPORTA_IN_EXCEL()
Dim report_dir As String, strfilename As String, obj As AccessObject, dbs As Object
Dim strPath As String, xlApp As Object, xlSheet As Object, xlWorkbook As Object, s As String

Const xlPivotTableVersion10 = 1
Const xlColumnField = 2
Const xlRowField = 1
Const xlSum = -4157
Const xlDatabase = 1
    
    On Error GoTo ESPORTA_IN_EXCEL_Err
   
    Set dbs = Application.CurrentData
    
    report_dir = "G:"
    strfilename = report_dir & "Report" & Format(Date, "yyyymmdd") & ".xls"
    
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    
    For Each obj In dbs.AllQueries
        DoCmd.OpenQuery obj.Name, acViewPivotTable
        
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, obj.Name, strfilename, True
        
        Set xlWorkbook = xlApp.Workbooks.Open(strfilename)
        With xlWorkbook
            Set xlSheet = .sheets(1)
            
            s = xlSheet.range("A1").currentregion.Address
           .PivotCaches.Add(SourceType:=xlDatabase, SourceData:=s).CreatePivotTable _
                        TableDestination:=xlSheet.Name & "!R1C6", TableName:="Tabella_pivot1", DefaultVersion:=xlPivotTableVersion10

       
            .sheets(xlSheet.Name).Select
            ' Add the column field (the name of the pivot field is one of your data table) '
            With .ActiveSheet.PivotTables("Tabella_Pivot1").PivotFields("Campo1")
                .Orientation = xlColumnField
                .Position = 1
            End With
            
            ' Add the row field (the name of the pivot field is one of your data table) '
            With .ActiveSheet.PivotTables("Tabella_Pivot1").PivotFields("Campo2")
                .Orientation = xlRowField
                .Position = 1
            End With
            
            ' Add the data field
            With .ActiveSheet.PivotTables("Tabella_Pivot1")
                .AddDataField .PivotFields("Campo1"), "Sum of a value", xlSum
                .AddDataField .PivotFields("Campo2"), "Sum of another value", xlSum
            End With
        End With
        
        DoCmd.Close acQuery, obj.Name, acSaveNo
        xlApp.ActiveWorkbook.SaveAs FileName:=strfilename
    Next obj
     
     
ESPORTA_IN_EXCEL_Exit:

    xlApp.Quit
    Set xlApp = Nothing
    Exit Function

ESPORTA_IN_EXCEL_Err:
    MsgBox Error$
    DoCmd.Close acQuery, obj.Name, acSaveNo
    Resume ESPORTA_IN_EXCEL_Exit

End Function

di Random88 (utente non iscritto) data: 26/02/2014 16:45:27
Eeee qua ci sta la mano di un maestro..appena sono un attimo libero lo guardo bene e ti faccio sapere..credo che devo aggiustare alcune cose per adattarlo al mio contesto.. In ogni modo grazie mille.. Sono appena arrivato anche io ad una soluzione ma molto sporca :) .. Una soluzione indolore diciamo.. In sostanza apro tutte le query in formato pivot table in excel.. Ed infine lancio un autorun che mi prende tutte le istanze aperte di excel e me le mette nello stesso foglio ;) .. Eheheheh .. Che ne pensi? :)
di Random88 (utente non iscritto) data: 26/02/2014 22:42:18
Ciao vecchio Frac.. ho iniziato a guardare il tuo codice proprio ora.. Purtroppo però non mi gira.. Il problema è che le mie query non hanno un formato standard ma diversi filtri e campi in formato pivot table.. in ogni modo è veramente un bel codice.. la mia soluzione ovviamente è molto sporca e dopo averci lavorato molto ho raggirato il processo in quel modo..

Vuoi Approfondire?