Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

[Excel] Elaborazione file presenze

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

[Excel] Elaborazione file presenze

Postdi cfdff » 10/02/18 12:04

Salve ho un piccolo ( GRANDE ) problema, anzi 2, il primo sono poco pratico di VBA il secondo ve lo espongo di seguito :
In ufficio ho il rilevatore di presenze mi genera un file txt, ad ogni timbratura una riga, con una stringa del tipo :
001000001374964320160929112424E0000000000XXXXXXXXXXXXXXXXX dove da 7 per i successivi 10 caratteri corrispondono alla MATRICOLA, da 17 per i successivi 8 caratteri corrispondono al GIORNO formato yyyy/mm/dd, dal 25 per i successivi 6 ORA, e il 31 la CAUSALE ( E o U ). Giornalmente dovrei fare il controllo del personale che ha timbrato il cartellino e considerando che ogni giorno producono almeno 150 / 200 ( magari ci sono anche più entrate e più uscite dello stesso dipendente) righe vorrei importare i dati in un foglio excel ma gia mettendo la condizione di importare la stringa dati relativi al giorno che mi interessa suddivisa per MATRICOLA - ORA - CAUSALE e contestualmente associare alla matricola il nome e cognome di chi ha timbrato e per questo ho fatto 3 fogli uno DATI ed uno ELENCO ed uno GIORNALIERO.Nel foglio DATI mi importo i campi che mi interessano nel foglio ELENCO ci sono le MATRICOLE ed IL COGNOME E NOME DEL PERSONALE e nel foglio GIORNALIERO dovrei avere le situazione di chi ha timbrato quel giorno. Di seguito vi faccio vedere cosa avevo fatto io, frutto di varie scopiazzate soprattutto del vostro forum, ma non riesco nell'intento mi funziona a metà. HELP ME !!!!!!


MODULO 1 :

Codice: Seleziona tutto
Sub Caricamento()
y = 12
Open "C:\test\timbra.txt" For Input As #1
Do Until EOF(1)
Line Input #1, riga
Matr = Mid(riga, 7, 10)
beggiata = Mid(riga, 31, 1)
giorno = Mid(riga, 17, 8)
DataF = Mid(giorno, 5, 4) & Mid(giorno, 3, 2) & Mid(giorno, 1, 2)
Ora = Mid(riga, 25, 6)
giorno = Format(Mid(giorno, 1, 4) & "-" & Mid(giorno, 5, 2) & "-" & Mid(giorno, 7, 2), "mm-dd-yyyy")
Ora = Mid(Ora, 1, 2) & ":" & Mid(Ora, 3, 2) & ":" & Mid(Ora, 5, 2)
esci = 0
If esci = 1 Then GoTo salta
Sheets("Dati").Cells(y, 2).Value = Matr
Sheets("Dati").Cells(y, 3).Value = giorno
Sheets("Dati").Cells(y, 4).Value = Ora
Sheets("Dati").Cells(y, 5).Value = beggiata
y = y + 1
x = x + 1
z = z + 1
salta:
Loop
Close #1
inpfile = "C:\test\timbra.txt"
outfile = "C:\test\" & DataF & "_timbra.txt"

On Error Resume Next
FileCopy inpfile, outfile
On Error GoTo 0
End Sub


FOGLIO GIORNALIERO :
Codice: Seleziona tutto
Sub cercadata()
Dim xx As String
Dim k As Boolean
Dim cel As Range
Dim rng As Range
Dim I As Long, x As Long, y As Long, z As Long
On Error Resume Next
Me.Range("b12", "b150").ClearContents
Me.Range("j12", "l150").ClearContents
k = False
xx = InputBox("INSERISCI DATA NEL FORMATO GG/MM/AAAA")
Cells(8, 4) = xx

lr = Sheets("giornaliero").Cells(Rows.Count, "b:b").End(xlUp).Row

matricola = Sheets("Dati").Cells(lr + 11, 2)
orario = Sheets("Dati").Cells(lr + 11, 4)
causale = Sheets("Dati").Cells(lr + 11, 5)
giorno = Sheets("Dati").Cells(lr + 11, 3)
If giorno = xx Then
Cells(lr + 11, 2) = matricola
Cells(lr + 11, 10) = orario
Cells(lr + 11, 12) = causale
matricola = matricola + 1
orario = orario + 1
causale = causale + 1



Non sono in grado di trovare una formula che legge già le righe del TXT e mi restituisce in EXCEL solo i dati che mi interessano, ovvero tutta la stringa divisa per MATRICOLA - ORA - CAUSALE che contiene la data che mi interessa........... GRAZIE A TUTTI.-
cfdff
cfdff
Newbie
 
Post: 9
Iscritto il: 30/01/18 16:41

Sponsor
 

Re: [Excel] Elaborazione file presenze

Postdi Anthony47 » 10/02/18 13:46

Dalle Linee Guida per pubblicare le vostre Domande (vedi viewtopic.php?f=26&t=103911)
1) Date alla vostra discussione un titolo pertinente, che riassuma al meglio la domanda che state per porre.

13) Raccomandazioni finali:
Evitate frasi vaghe o a effetto quali "Quesito per esperti", "Aiuto...", "Sono disperato", perche' in genere sono controproducenti.


Ho modificato io il titolo del tuo quesito.

Per cominciare io non avrei aperto il file timbra.txt per farne poi da vba il parsing delle righe, ma avrei usato "Importazione guidata di dati esterni":
-Menu /Dati; gruppo Carica dati esterni, opzione Da Testo
-scegli il file; premi Importa
-nella finestra Importazione guidata testo - Passaggio 1 di 3 scegli "Larghezza fissa"; Avanti; imposti le separazioni colonne come da informazioni in tuo possesso; Avanti; imposti per ogni colonna il formato che meglio si adatta (probabilmente Testo su tutti i campi, eccetto Data /AMG per il campo Data)
-Fine

In questo modo ti troverai con il file importato con i vari campi posizionati in colonne diverse.

Se registri una macro mentre effettui il processo di Importazione guidata ti troverai il codice pronto per le volte successive.

Supponiamo che hai importato i dati in Foglio5, con le matricole (2° campo) in colonna B, le date in colonna C, l'Orario in colonna D, il Tipo in colonna E.

A questo punto vai su un altro foglio e creati la tabella con le timbrature del giorno che ti interessa.
Ti crei uno specchietto come questo:
Immagine

In B1 inserisci la data cercata
In A3 inserisci la formula
Codice: Seleziona tutto
=SE.ERRORE(PICCOLO(SE(Foglio5!$C$1:$C$500=$B$1;RIF.RIGA(Foglio5!$A$1:$A$500);"");RIF.RIGA(A1));"")
Va confermata con la combinazione Contr-Maiusc-Enter, non il solo Enter, poi copiata verso il basso per il numero max di timbrature che potresti avere

In B3 ottiene la matricola corrispondente alla prima timbratura, con la formula
Codice: Seleziona tutto
=SE(A3<>"";INDICE(Foglio5!$B$1:$B$500;$A3);"")


In C3 importi il Nominativo tramite Cerca.Vert alla tua tabella Matricole /Nominativi

In D3 ottieni l'orario con la formula
Codice: Seleziona tutto
=SE(A3<>"";INDICE(Foglio5!$D$1:$D$500;$A3);"")


In E3 il Tipo con la formula
Codice: Seleziona tutto
=SE(A3<>"";INDICE(Foglio5!$E$1:$E$500;$A3);"")


Anche queste formule vanno copiate verso il basso per quante formule hai messo in colonna A

Questo dovrebbe darti il risultato che vorresti ottenere.

Nota che puoi impostare le "proprietà" dell'importazione guidata ad esempio per avere:
-l'aggiornamento dei dati importati all'apertura del file
-l'aggiornamento dei dati ogni N minuti
Per questo, dopo aver premuto Fine in "Importazione guidata testo - Passaggio 3 di 3", usa il pulsante Propieta' e spunta le voci che ti interessano.

In questo modo, se il file con le timbrature viene aggiornato in real time dal sistema di rilevazione presenze, ti trovarai con lo specchietto delle timbrature che si puo' aggiornare automaticamente man mano che il file viene reimportato automaticamente.

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 17656
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] Elaborazione file presenze

Postdi cfdff » 10/02/18 14:41

Anthony grazie di cuore e scusa per il mio essermi espresso male, ma essendo nuovo del forum per fare presto ho sbagliato a relazionarmi.........

Cercherò di mettere in atto le tue preziose informazioni e ti farò sapere, a dopo e grazie di tutto ancora.-
cfdff
Newbie
 
Post: 9
Iscritto il: 30/01/18 16:41

Re: [Excel] Elaborazione file presenze

Postdi cfdff » 10/02/18 15:28

Ciao Anthony, Eccomi di nuovo qua, la prima parte va bene ho registrato la macro ed è perfetta per importare solo che mi sarà sfuggito di specificare che il file del rilevatore presenze è sempre uno, ovvero ad ogni timbratura si aggiunge la riga quindi da un giorno all'altro le righe aumentano ecco perchè volevo importare già il file già con una macro ed una condizione cosi' anche se il file.txt è enorme in excel ho solo quello che mi interessa e quindi più fluido o cosi' si impallerebbe subito, mi puoi aiutare ? GRAZIE !!!!!
cfdff
Newbie
 
Post: 9
Iscritto il: 30/01/18 16:41

Re: [Excel] Elaborazione file presenze

Postdi Anthony47 » 10/02/18 18:13

Ma a te interessa fare delle query sulla giornata corrente, o vuoi poter scegliere una data a piacere e verificare le timbrature, o scegliere le date dell'ultima settimana - ultimo mese - ultimo decennio - o che cosa?
Il file e' sempre lo stesso per tutto l'anno - per tutto il mese - o che cosa?

Personalmente ritengo che elaborare un elenco di 10mila righe per excel sia normale attivita', tu che esperienza invece hai avuto?

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 17656
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] Elaborazione file presenze

Postdi cfdff » 10/02/18 18:31

vorrei fare una query relativa al giorno e sapere chi ha timbrato e se riesco a risolvere questo primo passo magari cercare con la matricola le timbrature di un mese
cfdff
Newbie
 
Post: 9
Iscritto il: 30/01/18 16:41

Re: [Excel] Elaborazione file presenze

Postdi cfdff » 10/02/18 19:00

dimenticavo il file è sempre lo stesso ogni giorno si arricchisce delle nuove timbrature
cfdff
Newbie
 
Post: 9
Iscritto il: 30/01/18 16:41

Re: [Excel] Elaborazione file presenze

Postdi Anthony47 » 11/02/18 01:09

vorrei fare una query relativa al giorno e sapere chi ha timbrato
Quanto proposto nel mio primo messaggio produce questo risultato. Devi solo adattare quei "500" al reale numero di righe atteso dal file timbrature.

e se riesco a risolvere questo primo passo magari cercare con la matricola le timbrature di un mese
Ti fai uno schemino come quello per le timbrature, ma
-in B1 scrivi la matricola da cercare, in C1 scrivi il mese da cercare (nel formato 01/mese/anno)
- in A3 metti la formula
Codice: Seleziona tutto
=SE.ERRORE(PICCOLO(SE((Foglio5!$B$1:$B$500=$B$1)*(TESTO(Foglio5!$C$1:$C$500;"aaaamm")=TESTO($C$1;"aaaamm"));RIF.RIGA(Foglio5!$A$1:$A$500);"");RIF.RIGA(A1));"")

Anche questa da confermare con Contr-Maiusc-Enter e poi copiare verso il basso per circa 35 righe.

Le formule delle altre colonne rimangono invariate, salvo che le colonne Matricola e Nominativo non hanno senso: la matricola e' quella di cui stai facendo la query, il nominativo lo calcoli una sola volta con la formula Cerca.Vert ad esempio in D1

Ripropongo una domanda senza risposta: "Il file e' sempre lo stesso per tutto l'anno - per tutto il mese - [per tutta la vita] o che cosa? "

Ciao


Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 17656
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] Elaborazione file presenze

Postdi cfdff » 11/02/18 01:19

in pratica il rilevatore alla mia richiesta mi rilascia un file txt che parte dalla prima timbratura , ovvero dalla sua installazione, e finisce al momento che gli dico di generarlo.
cfdff
Newbie
 
Post: 9
Iscritto il: 30/01/18 16:41

Re: [Excel] Elaborazione file presenze

Postdi cfdff » 11/02/18 01:20

e dovrebbe rimanere cosi' per tutta la vita
cfdff
Newbie
 
Post: 9
Iscritto il: 30/01/18 16:41

Re: [Excel] Elaborazione file presenze

Postdi Anthony47 » 11/02/18 22:55

e dovrebbe rimanere cosi' per tutta la vita
ma tu sei sicuro di quanta memoria ti hanno messo nel terminalino?
Comunque non voglio sembrare piu' interessato di te a una buona soluzione. Quindi con questa macro puoi riportare sul foglio Excel solo le ultime N timbrature che figurano nel file:
Codice: Seleziona tutto
Sub Timbrer()
'vedi http://www.pc-facile.com/forum/viewtopic.php?f=26&t=109411&p=642077#p642077
'
Dim fFil, recNum As Long, TxtFile As String, cSh As String
Dim VLine, I As Long, stAt As Long, dataSh As String
'
TxtFile = "C:\PercorsoCompleto\pippo_prova.txt"     '<<< Percorso e nome del file con le timbrature
dataSh = "Foglio5"                                  '<<< Nome del foglio in cui si importeranno le timbrature
'
cSh = ActiveSheet.Name
Sheets(dataSh).Select
fFile = FreeFile
Open TxtFile For Input As #fFile
Do While Not EOF(1)
    Line Input #fFile, VLine
    I = I + 1
Loop
Close #fFile
stAt = I - 10000         '<<< 10000 = numero di timbrature da prendere in considerazione; modificare a piacere
If stAt < 0 Then stAt = 1
Application.DisplayAlerts = False
Range("A:H").ClearContents
Application.DisplayAlerts = True
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & TxtFile, Destination:=Range("$A$1" _
        ))
        .Name = "pippo_prova"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = stAt               'La riga da cui cominciare
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 2, 5, 2, 1, 1)
        .TextFileFixedColumnWidths = Array(7, 9, 8, 6, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
Sheets(cSh).Select
End Sub

Il codice va messo in un modulo standard del vba. Le righe marcate <<< sono da modificare come da commento per indicare:
-il file da cui attingere
-il foglio in cui importare le timbrature
-il numero di record che si vuole importare

Eseguendo la macro solo gli ultimi N record saranno importati nel foglio di destinazione, usando sempre la tecnica di Importazione dati esterni.

ATTENZIONE: le colonne A:H del foglio di importazione vengono azzerate senza preavviso prima di importare i nuovi record

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 17656
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel] Elaborazione file presenze

Postdi cfdff » 11/02/18 23:17

Grazie di cuore..... la provo e ti faccio sapere.......
cfdff
Newbie
 
Post: 9
Iscritto il: 30/01/18 16:41

Re: [Excel] Elaborazione file presenze

Postdi cfdff » 15/02/18 23:17

Perdonami se mi faccio vivo solo ora, ma ho avuto qualche problemino...... La macro va benissimo con l'aiuto di alcune formule excel ho risolto, diciamo, il tutto.... appena posso vi mando la soluzione dettagliata usata cosi' magari mi dite cosa ne pensate e magari mi date qualche consiglio per fare meglio....... Grazie ancora di cuore Anthony......
cfdff
Newbie
 
Post: 9
Iscritto il: 30/01/18 16:41


Torna a Applicazioni Office Windows


Topic correlati a "[Excel] Elaborazione file presenze":


Chi c’è in linea

Visitano il forum: Nessuno e 27 ospiti