Automazione da access: modificare excel

di il
11 risposte

Automazione da access: modificare excel

Buongiorno a tutti,

genero automaticamente dei file excel da access soggetti  a modifiche da alcuni colleghi che viene riposto quotidianamente in una cartella onedrive, che poi viene successivamente da me utilizzati per aggiornare alcune tabelle.

Vorrei, da access in vba bloccare la prima riga in alto dal menu Visualizza e abilitare dal menu Dati ill filtro.

grazie

11 Risposte

  • Re: Automazione da access: modificare excel

    Ciao,

    devi aprire il documento excel in access con una connesione e con visualizzazione = false

    imposti le proprietà del foglio di lavoro

    salva e chiudi documento excel

    chiudi oggetti connessione a excel

  • Re: Automazione da access: modificare excel

    06/08/2023 - ziobill ha scritto:


    Vorrei, da access in vba bloccare la prima riga in alto dal menu Visualizza e abilitare dal menu Dati ill filtro.

    Hai detto bene nell'oggetto del thread: si tratta di automazione, nel senso tecnico del termine in VBA: controllare un'applicazione da un'altra.

    Per fare quello che chiedi, io che sono un emerito ignorante del modello ad oggetti di Excel, suggerisco di usare Excel in modo “classico” e con il registratore di macro ottenere il codice VBA delle operazioni che ti interessano. Poi lo trasporti in Access. Potrebbe essere necessario qualche adattamento.

  • Re: Automazione da access: modificare excel

    Carissimi buongiorno,

    a seguire il codice per aprire il file di excel che ho utilizzato con le macro

       Dim appExcel As Excel.Application
       Dim myWorkbook As Excel.Workbook
       Set appExcel = CreateObject("Excel.Application")
       Set myWorkbook = appExcel.Workbooks.Open(strFilePath)
       appExcel.Visible = True
       
       'Blocca prima riga in alto - MAcro excel
         With ActiveWindow
           .SplitColumn = 0
          '.SplitRow = 1
       End With
       ActiveWindow.FreezePanes = True
       
      'Aggiungi filtro al foglio - MAcro Excel
        Selection.AutoFilter
      
       
       Set appExcel = Nothing
       Set myWorkbook = Nothing
    

    Non riesco a capire come modificare il codice delle macro.

    premetto che non sono un grosso conoscitore di VBA.

  • Re: Automazione da access: modificare excel

    07/08/2023 - ziobill ha scritto:


    Non riesco a capire come modificare il codice delle macro.

    premetto che non sono un grosso conoscitore di VBA.

    Devi solo valorizzare strFilePath con il percorso completo del file da gestire, poi secondo me va il grosso è fatto. Basta aggiungere anche il salvataggio del file e la sua chiusura, che se non sbaglio si può fare con un comando solo.

    https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.close

  • Re: Automazione da access: modificare excel

    Non è il problema del percorso,

     With ActiveWindow
           .SplitColumn = 0
          '.SplitRow = 1
       End With
       ActiveWindow.FreezePanes = True
       
      'Aggiungi filtro al foglio - MAcro Excel
        Selection.AutoFilter
        
        ma queste righe che non vengono riconosciute valide. Sono di fatti il coia incolla delle 2 macro create su excel per: riga 1 bloccata in alto e il filtro attivato. 
        Il problema è come modificare + o - quelle 4 righe di codice.
        
        
    
  • Re: Automazione da access: modificare excel

    07/08/2023 - ziobill ha scritto:


    Non è il problema del percorso,

    Ciao

    ma non hai fatto i passaggi che ti dicevo all'inizio del thread ?  a quest'ora avevi bel che fatto. 

    Se  posso una domanda per precisare alcune cose:
    ogni volta crei un nuovo file excel e nuovo foglio di lavoro ?

  • Re: Automazione da access: modificare excel

    In attesa di un tuo riscontro, vado a spiegarti due semplici cose che potresti fare

    Esempio:

    • si crea un nuovo file excel
    • si popola il foglio di lavoro con i dati desiderati
    • si applica il blocco riquadri da riga 2
    • si applica il filtro

    Quindi per aprire in Access da Vba l'applicazione Excel si useranno due oggetti del tipo :

    Set ExcelApp = CreateObject("Excel.Application")
    Set ExcelWorkbook = ExcelApp.Workbooks.Add

    e quindi abbiamo aperto l'applicazione Excel e aggiunto al modello base un nuovo workbook. 
    Se non ci interessa vederlo aggiungeremo l'istruzione ExcelApp.Visible = False e in questo modo non impegneremo il video e agirà in modo silenzioso.

    A questo punto possiamo popolare il foglio excel ExcelWorkbook.Sheets(1) …. qui vedi tu le operazioni che devi eseguire a tal fine.

    A questo punto possiamo impostare il filtro… per esempio nella prima riga del foglio excel: ExcelWorkbook.Sheets(1).Range("A1").AutoFilter

    A questo punto possiamo impostare il Blocca Riquadri… per esempio a partire dalla seconda riga del foglio di lavoro:
    ExcelWorkbook.Sheets(1).Range("A2").Select
    ExcelApp.ActiveWindow.FreezePanes = True

    A questo punto possiamo salvare il file esxcel per esempio con il metodo SaveAs : ExcelWorkbook.SaveAs "TuaPath\TuoFile.xlsx"

    A questo punto possiamo chiudere il nuovo file excel e l'applicazione Excel… per esempio: ExcelWorkbook.Close  e  ExcelApp.Quit

    A questo punto se metti insieme quanto sopra riportato, esce fuori una cosa di questo tipo:

    Dim ExcelApp As Objec
    Dim ExcelWorkbook As Object
    ' set excel application obj
    Set ExcelApp = CreateObject("Excel.Application")
    ExcelApp.Visible = False
    ' set new workbook
    Set ExcelWorkbook = ExcelApp.Workbooks.Add
    
    ' populate sheets
    ExcelWorkbook.Sheets(1)... non so come importi i dati nel foglio di lavoro, vedi tu quello che devi fare...
    
    ' set autofilter
    ExcelWorkbook.Sheets(1).Range("A1").AutoFilter
    ' set freeze panes
    ExcelWorkbook.Sheets(1).Range("A2").Select
    ExcelApp.ActiveWindow.FreezePanes = True
    
    ' save as excel file
    ExcelWorkbook.SaveAs "TuaPath\TuoFile.xlsx"
    
    ' close excel application
    ExcelWorkbook.Close
    ExcelApp.Quit
    Set ExcelWorkbook = Nothing
    Set ExcelApp = Nothing

    A questo punto se segui tale traccia trovi la logica di come poter interagire su un file di Excel da Vba Access

    A questo punto … penso che più di così non saprei cosa aggiungere … fai sapere ;-)

    … e a questo punto … ciao e buon lavoro ;-)) ti lascio un po' di riferimenti:
    CreateObject function (Visual Basic for Applications) | Microsoft Learn

    Edit:
    A questo punto . . . se mi bannano fanno pure bene, perchè in pratica ti ho dato la “pappa pronta” e questo non se pò fà … speriamo bene.  ;-)

  • Re: Automazione da access: modificare excel

    Grazie Franco ho risolto.

    però partivo da un punto iniziale diverso, il file excel era di fatti un' estrazione di una query di access , automatizzata in vba con il seguente comando:

     DoCmd.OutputTo acOutputQuery, QueryConvExcel, acFormatXLSX, Percorso

    Vorrei eliminare la richiesta di sovrascrizione; ho provato con  .save anzichè .SAveAs  senza successo, ma la cosa più tragica è quella della richiesta: “Aggiungere etichetta di riservatezza”.

    Questo Problema lo avevo risolto con invio di Mail automatiche, creando un modello di mail, mi auguro che ci sia un modo in vba per risolvere, anzichè modificare tutto e ricominciare da capo.

    Queste conferme bloccano, degli automatismi a cadenza settimanale che non devono prevedere necessariamente la mia presenza.

    Queste EStrazioni prevedono successivamente delle importazioni, sempre in automatico, che mi aggiornano alcune tabelle sul db.

    Spero ancora nella tua gentilissima disponibilità.

    se quel 65 è l'anno di nascita, sono più vecchio di te di anni 3 :-)

    Sub ESTRAI_PER_Aggiornamento_Figure_di_Legge()
        Dim gg As Integer
        gg = DLookup("[Figure_di_Legge_GG]", "servizio1")
        data_estr = DLookup("[Data_Figure_Legge]", "servizio1")
      
        If Date >= data_estr + gg Then
    
    'Estraggo Ingegneria
           QueryConvExcel = "Q_Estrazione_Figure di Legge_ST_ING"
           Percorso = DLookup("[Folder_Figure_legge_EStrazione]", "Servizio1") & DLookup("[ING]", "Servizio1")
           Percorso = Percorso & "Estrazione Figure di Legge_ING.xlsx"
           DoCmd.OutputTo acOutputQuery, QueryConvExcel, acFormatXLSX, Percorso
           OpenExcelFile (Percorso) 'Grazie a Franco65 - I Programmatori-  questa è la funzione Sistemata Grazie alla tua gentilissima cortesia e disponibilità.
    
    'Estraggo UT_Nodo
            QueryConvExcel = "Q_Estrazione_Figure di Legge_ST_UT_NODO"
            Percorso = DLookup("[Folder_Figure_legge_EStrazione]", "Servizio1") & DLookup("[UT_NODO]", "Servizio1")
            Percorso = Percorso & "Estrazione Figure di Legge_ST_UT_NODO.xlsx"
            DoCmd.OutputTo acOutputQuery, QueryConvExcel, acFormatXLSX, Percorso
            OpenExcelFile (Percorso)
    
    'Estraggo UT_NordEst
            QueryConvExcel = "Q_Estrazione_Figure di Legge_ST_UTNE"
            Percorso = DLookup("[Folder_Figure_legge_EStrazione]", "Servizio1") & DLookup("[UT_NE]", "Servizio1")
            Percorso = Percorso & "Estrazione Figure di Legge_ST_UTNE.xlsx"
            DoCmd.OutputTo acOutputQuery, QueryConvExcel, acFormatXLSX, Percorso
            OpenExcelFile (Percorso)
    
    'Estraggo UT_SudEst
            QueryConvExcel = "Q_Estrazione_Figure di Legge_ST_UTSE"
            Percorso = DLookup("[Folder_Figure_legge_EStrazione]", "Servizio1") & DLookup("[UT_SE]", "Servizio1")
            Percorso = Percorso & "Estrazione Figure di Legge_ST_UTSE.xlsx"
            DoCmd.OutputTo acOutputQuery, QueryConvExcel, acFormatXLSX, Percorso
            OpenExcelFile (Percorso)
            
            '\more
  • Re: Automazione da access: modificare excel

    08/08/2023 - ziobill ha scritto:


    se quel 65 è l'anno di nascita, sono più vecchio di te di anni 3 :-)

    Yes !!!  ;-))


    Riprendendo la traccia che ti ho lascito in precedenza ….  sarà sufficiente eseguire il Save nella forma “ExcelWorkbook.Save”

    Quindi ;

    • open file excel che hai generato
    • applica Filtro e Blocca riquadri
    • save per salvare le modifiche 
    • close file excel e application
    Dim ExcelApp As Object
    Dim ExcelWorkbook As Object
    ' set excel application obj
    Set ExcelApp = CreateObject("Excel.Application")
    ExcelApp.Visible = False
    ' open existing workbook
    Set ExcelWorkbook = ExcelApp.Workbooks.Open("FullPath\TuoFile.xlsx")
    
    ' set autofilter
    ExcelWorkbook.Sheets(1).Range("A1").AutoFilter
    
    ' set freeze panes
    ExcelWorkbook.Sheets(1).Range("A2").Select
    ExcelApp.ActiveWindow.FreezePanes = True
    
    ' save file
    ExcelWorkbook.Save
    
    ' close excel application
    ExcelWorkbook.Close
    ExcelApp.Quit
    Set ExcelWorkbook = Nothing
    Set ExcelApp = Nothing

    Nel caso vuoi utilizzare il SaveAs ( salva con nome ) allora dovrai cancellare il file esistente e poi salvare il nuovo con nome :

    ' delete file... if exists
    If Dir("FullPath\TuoFile.xlsx", vbArchive) <> "" Then Kill "FullPath\TuoFile.xlsx"
    ' save excel file
    ExcelWorkbook.SaveAs "FullPath\TuoFile.xlsx"

    Doc per info argomenti trattati:
    Dir function (Visual Basic for Applications) | Microsoft Learn
    Kill statement (VBA) | Microsoft Learn


    più o meno dovresti avere tutte le tracce da poter applicare al tuo caso.

    ;-)


    Edit :

    Dimenticavo una cosa … naturalmente il Dir e Kill lo puoi usare anche prima di creare il file Excel… insomma… in ogni dove, dove meglio lo ritieni opportuno:

    ' delete file... if exists
    If Dir("FullPath\TuoFile.xlsx", vbArchive) <> "" Then Kill "FullPath\TuoFile.xlsx"
    
    ' create new excel file
    DoCmd.OutputTo acOutputQuery, QueryConvExcel, acFormatXLSX, Percorso
            OpenExcelFile (Percorso)
  • Re: Automazione da access: modificare excel

    Grazie, proverò tutto domattina.

    Ti aggiorno, buona serata

  • Re: Automazione da access: modificare excel

    Buonasera Franco, 

    scusami per il lungo silenzio, ma ho dovuto lasciare quel lavoro per occuparmi di altro + ferie sopraggiunte.

    orbene,

    tutto ok, ma mi rimane il problema di AZURE e quindi l'obbligo di selezionare la riservatezza.

    E' possibile aggiungerla al file utilizzando VBA?

    se si come?

     aspetto una buona notizia da te o dal forum :-)

    Raffaele

Devi accedere o registrarti per scrivere nel forum
11 risposte