if new record then copy and paste cells

[mattew89]

Allora, ho un file excel in cui vengo inseriti continuamente nuovi record. Io voglio che quando viene aperto il file, se c'è un nuovo record rispetto all'apertura precedente vengano copiate determinate celle e copiate in altre determinate celle di un altro excel. Grazie per gli aiuti

[Anthony47]

Non mi e' chiaro...
Tu apri un file, ci fai delle modifiche, e quando lo apri la prossima volta vuoi copiare alcune delle cose che avevi modificato nella sessione precedente?

[mattew89]

Provo a cambiare, mi sono espresso male.. Apro il file, inserisco uno o più nuovi record, quando lo chiudo voglio che determinate celle dei nuovi record vengano copiate in altre determinate celle di un altro file. Si puo' fare?

[Anthony47]

Si puo' fare, e ti serve una macro.
Poiche' una macro e' un set di istruzioni che agiscono sull' applicativo Excel per eseguire automaticamente una sequenza di operazioni, vorrei il dettaglio della sequenza di operazioni da eseguire:
-quali sono le "determinate celle dei nuovi record" che vanno copiate...
-....in quali "altre determinate celle"...
-...di quale altro file

Ciao

[mattew89]

La macro deve essere inserita in questo file http://www.filedropper.com/storicoinserimentiopmonitoraggi, quando questo viene chiuso deve intercettare i nuovi record e incollarli in coda in questo file http://www.filedropper.com/storicoconfigurazione nel seguente modo:
cella A in cella D
cella D in cella A
cella E in cella B
cella K in cella G
cella L in cella E
cella N in cella F
cella AC in cella I

[Anthony47]

Il secondo file e' protetto e quindi non l'ho aperto
Pero' dal contenuto del primo file non so se l'aggiornamento di File2 e' opportuno che lo faccia una macro o lo esegua un addetto responsabile.

Comunque, nel file storicoinserimentiopmonitoraggi.xls inserisci questo codice nel modulo ThisWorkbook del vba:

Codice: Seleziona tutto

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Foglio2.Cells(Rows.Count, 1).End(xlUp).Row > Foglio1.Range("A1") Then
    Call UpDt
End If
End Sub

Private Sub Workbook_Open()
Foglio1.Range("A1") = Foglio2.Cells(Rows.Count, 1).End(xlUp).Row
End Sub


Poi in un modulo standard, es Modulo1, inserisci

Codice: Seleziona tutto

Sub UpDt()
Dim sSRc, dDst, I As Long, DstWb As String
Dim SrcSh As Worksheet, myNext As Long
'Exit Sub                               '!!! Vedi Testo
sSRc = Array("A", "D", "E") 'completare '<<< L'intero elenco di colonne da copiare
dDst = Array("D", "A", "B") 'completare '<<< L'intero elenco di colonne dove incollare
DstWb = "C:\percorso\nomefile.xls"      '<<< L'esatto percorso e nome del file da aggiornare
Workbooks.Open DstWb
Sheets("NomeSheet").Select              '<<< Il nome del foglio del file da aggiornare
myNext = Cells(Rows.Count, 1).End(xlUp).Row + 1
With ThisWorkbook
    For I = .Foglio1!Range("A1") + 1 To .Foglio2.Cells(Rows.Count, 1).End(xlUp).Row
        For j = LBound(sSRc) To UBound(sSRc)
            Cells(myNext, dDst(j)).Value = .Foglio2.Cells(I, sSRc(j)).Value
            myNext = myNext + 1
        Next j
    Next I
End With
Stop                '*** VEDI TESTO
ActiveWorkbook.Close True
ThisWorkbook.Foglio1.Range("A1") = ThisWorkbook.Foglio2.Cells(Rows.Count, 1).End(xlUp).Row
End Sub


Completa o personalizza le istruzioni marcate <<<, come da commento.
Torna su Excel, salva, chiudi il file, riaprilo.

A questo punto prova ad inserire dei record e a salvare il file; al momento del salvataggio dovrebbe aprirsi il secondo file su cui dovrebbero essere aggiunti i nuovi record; per consentirti la verifica del corretto funzionamento, la macro si ferma su uno Stop (riga marcata ***): controlla l'esito dell'aggiornamento e se NON E' Ok allora CHIUDERE il secondo file senza salvare e interrompi la macro (Menu /Esegui /Ripristina); inoltre elimina l'apostrofo in testa alla riga marcata "!!!" (serve a evitare che la macro venga eseguita ulteriormente). Spiega nel prossimo messaggio che cosa non andava.
Se invece e' Ok allora, assicurati di attivare il secondo file, val sull'editor delle macro e premi F5 per completare la macro.
Dopo un paio di prove elimina la riga di Stop.

Con l'avvertenza che essendo il secondo file protetto tutta questa architettura io non l'ho collaudata.

Ciao.

[mattew89]

Purtroppo non succede nulla.. ti allego il secondo file senza password http://www.filedropper.com/storico

[mattew89]

errore debug 348 al seguente punto:

Codice: Seleziona tutto

  For I = .Foglio1!Range("IV1") + 1 To .Foglio1.Cells(Rows.Count, 1).End(xlUp).Row

[mattew89]

Ulteriori considerazioni:
Non capisco perchè richiami il foglio 2?
Nella Sub all'apertura del file viene inserito in Cella A1 il numero di righe conteggiate e così facendo perdo l'etichetta di colonna A

[Anthony47]

C'erano un paio di errori in quanto ti avevo proposto, tra cui l'errato uso di Foglio1 e Foglio2.
Nel file storicoinserimentiopmonitoraggi.xls inserisci questo codice nel modulo ThisWorkbook del vba:

Codice: Seleziona tutto

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Foglio1.Cells(Rows.Count, 1).End(xlUp).Row > Foglio2.Range("A1") Then
    Call UpDt
End If
End Sub

Private Sub Workbook_Open()
Foglio2.Range("A1") = Foglio1.Cells(Rows.Count, 1).End(xlUp).Row
End Sub


Poi in Modulo1

Codice: Seleziona tutto

Sub UpDt()
Dim sSRc, dDst, I As Long, DstWb As String
Dim SrcSh As Worksheet, myNext As Long, iMin As Long, iMax As Long
'Exit Sub                               '!!! Vedi Testo
sSRc = Array("A", "D", "E") 'completare '<<< L'intero elenco di colonne da copiare
dDst = Array("D", "A", "B") 'completare '<<< L'intero elenco di colonne dove incollare
DstWb = "C:\PERCORSO\byMATTEW88-Beta-Dest_STORICO_B61115.xls"      '<<< L'esatto percorso e nome del file da aggiornare
iMin = Foglio2.Range("A1").Value + 1
iMax = Foglio1.Cells(Rows.Count, 1).End(xlUp).Row
Workbooks.Open DstWb
Sheets("Prodotti").Select              '<<< Il nome del foglio del file da aggiornare
myNext = Cells(Rows.Count, 1).End(xlUp).Row + 1
    For I = iMin To iMax
        For j = LBound(sSRc) To UBound(sSRc)
            Cells(myNext, dDst(j)).Value = Foglio1.Cells(I, sSRc(j)).Value
        Next j
        myNext = myNext + 1
    Next I
Stop                '*** VEDI TESTO
ActiveWorkbook.Close True
Foglio2.Range("A1") = Foglio1.Cells(Rows.Count, 1).End(xlUp).Row
End Sub


OPERATIVAMENTE:
1) metti il codice nei moduli del file su cui lavori. Aggiorna le righe marcate <<<, in particolare in sSRc e dDst devi inserire nella corretta sequenza le celle da copiare e la loro destinazione sul secondo file (io mi sono limitato ai primi 3).
2) scrivi in A1 del foglio Monitor il numero di righe presenti sul foglio Storico (cioe' il numero dell'ultima riga occupata); A1 e' una cella di servizio che sara' usata per tenere traccia di quanti record "c'erano prima" rispetto a quanti ce ne sono "adesso". Il confronto viene fatto tutte le volte che il file viene salvato, e funziona solo per elenco che cresce (nuovi record vengono aggiunti), mentre non funziona se ne vengono tolti.
3) salva il file

4) a questo punto puoi provare ad aggiungere dei record in Storico e salvare il file: dovrebbe ora partire l'apertura del file di destinazione (che quindi deve ESSERE CHIUSO mentre lavori sul tuo file) e l'eventuale l'aggiornamento del secondo foglio; lo Stop ti consente di verificare se l'esito e' corretto. Per i casi Se Corretto e Se Non Corretto vale quanto avevo scritto ieri sera.

Ciao

[mattew89]

ok, spettacolo!! funziona alla grande.. Un'ultima cosa, il file storico è protetto da password di lettura. Come faccio a settare la macro in modo che la psw venga compilata automaticamente?

[mattew89]

ce l'ho fatta!!

[mattew89]

ancora un ultimissima cosa, se il file che apre la macro è aperto da un altro utente voglio che esca un msbox.. come si fa?

[mattew89]

problema: se nella cella c'è un numero che inizia con 0 (es. '0256) questo non viene riportato nell'altro excel

[Anthony47]

Devi spiegare bene come simulare l'errore del contenuto che comincia con "0"

Per la richiesta del msgbox, puoi usare la funzione FileStatus, descritta qui: viewtopic.php?f=26&t=106171#p621391

Inserisci il codice della Function FileStatus in coda su Modulo1 e aggiungi queste istruzioni in questa posizione nel codice della Sub UpDt

Codice: Seleziona tutto

iMax = Foglio1.Cells(Rows.Count, 1).End(xlUp).Row
'Controlli AGGIUNTI:
fstat = FileStatus(DstWb)
If fstat <> 0 Then
    MsgBox ("Errore " & Err.Description & vbCrLf & "Operazion abortita")
    Exit Sub
End If
'Fine aggiunte
Workbooks.Open DstWb


Ciao

[mattew89]

Provo a spiegarmi meglio, se nella cella è presente il seguente testo:

'0253

quando lo riporta nell'altro file, lo zero non viene riportato. Nella cella è presente l'apice prima del numero es('0253) in modo che il contenuto della cella venga visualizzato come testo

[Anthony47]

Allora invece di copiare i valori tra i fogli facciamo un Copy tradizionale; modifica questa parte della macro

Codice: Seleziona tutto

        For j = LBound(sSRc) To UBound(sSRc)
'            Cells(myNext, dDst(j)).Value = Foglio1.Cells(I, sSRc(j)).Value    '---TOGLI
            Foglio1.Cells(I, sSRc(j)).Copy Cells(myNext, dDst(j))              '+++METTI
        Next j


Ciao

[mattew89]

La macro funziona benissimo.. L'ho modificata un po' per evitare che vada in run time se il file di destinazione è già aperto, copiando i valori prima in un nuovo workbook e poi da lì una macro che lancio solo io da locale che mi copia i nuovi record dal nuovo workbook nel file di destinazione e poi li elimina in modo da non ricopiare record già presenti. Ora però vorrei aggiungere un pezzo:

Vorrei che se vengono modificati dei record nel file di origine questi venissero copiati nel nuovo workbook e poi sostituiti nel file di destinazione quando effettuo la copia con la macro. La chiave univoca per riconoscere i record è la combinazione delle colonne A+D+L nel file di origine con le colonne D+A+E del file di destinazione.
Spero di essermi spiegato

ciao e grazie

[Anthony47]

Vorrei che se vengono modificati dei record nel file di origine questi venissero copiati nel nuovo workbook e poi sostituiti nel file di destinazione quando effettuo la copia con la macro. La chiave univoca per riconoscere i record è la combinazione delle colonne A+D+L nel file di origine con le colonne D+A+E del file di destinazione.
Spero di essermi spiegato

In confidenza, i giochi a tre non mi sono mai piaciuti...

Comunque questa prestazione la devi inserire nel "file di origine" che evidentemente viene condiviso da piu' utenti, e (sia chiaro) funzionera' solo se in quel momento il "nuovo workbook" non e' in aperto; se invece fosse aperto allora potresti sempre memorizzare le modifiche al file di origine in un "quarto file", che periodicamente il secondo file (quello che abbiamo prima chiamato "nuovo workbook") andra' a sondare per vedere se ci sono dei cambiamenti (per questo "basterà" scandire tutti i record del nuovo workbook e controllarli con ognuno dei record presenti nel quarto workbook)

Se mi hai seguito allora penso che non sara' difficile realizzare la macro da inserire sul file di origine e sul nuovo workbook.

Se invece non mi hai seguito allora significa che forse e' meglio ripensare come i vari utenti accedono ai file in comune; in molti casi la mia impostazione e' stata di avere un file centrale a cui gli utenti accedono in realtime tramite una loro cartella di lavoro, per prelevare o inserisce quel che serve, quindi immediatamente salva e rilascia il file centrale per eventuali altri utenti. Ma questo richiede una analisi e una progettazione, che su un forum non si trovano...

Ciao

[mattew89]

Ok, ti seguo.. Quello che posso assicurarti è che "nuovo workbook" non sarà mai aperto ma resterà sempre dispoinibile... è l'unica certezza che ho! Oraq, come si fa la macro?