[EXCEL] Richiamo dati da più file

[MassiMOE2007]

Ciao a tutti,
guardando da due giorni i vari topic simili, non ho trovato una soluzione semplice al mio problema, con i nomi/formule in excel me la cavo, ma quando subentra VBA mi perdo un pò!
Problema:
nella cartella 2012 durante l'anno archivio n file .xlsx (preventivi per clienti) tutti costruiti nella medesima forma;
nel file produzione.xlsx riporto l'elenco dei prodotti dalla conferma del preventivo divenuto contratto;
questa operazione al momento è manuale quindi lunga e soggetta ad errori di copiatura, mi piacerebbe funzionasse in automatico.
L'operazione "da uno a molti" mi riesce (es: richiamo i dettagli cliente dal file unico CodiceCliente.xlsx ai singoli Preventivi) impiegando la formula:
'\\Server\Cartella1\[CodiceCliente.xlsx]Foglio1'!A1

l'operazione inversa "da molti a uno" (il mio problema) no ; non riesco a modificare la parte di formula che punta al file richiesto.
Mi piacerebbe, se possibile, nel file produzione, inserire nella cella A1 il nome file del preventivo da richiamare (preventivo 123.xlsx),automaticamente in B1 venga richiamato dal file preventivo relativo un determinato dato di una cella specifica.
Qualcuno sa dirmi dove mi stò perdendo? Grazie.

[Anthony47]

Ciao MassiMOE2007, benvenuto nel forum.
Hai gia' chiaro che se vuoi prendere i dati da un file chiuso devi necessariamente scrivere nelle celle formule che puntano esattamente il percorso completo, il nome file, il nome foglio, la cella (come nel caso che hai definito "da uno a molti"). Tu invece (mi pare) vorresti usare una specie di "Indiretto"...
Per un caso abbastanza simile al tuo prova a guardare qui: viewtopic.php?f=26&t=94799.
Comunque per capire che cosa proporti dovresti spiegare meglio quali e quante informazioni dovresti prelevare dal file Preventivo.xlsx e come dovresti disporle sul file Produzione.xlsx

Ciao

[Flash30005]

Benvenuto MassiMOE2007, anche da parte mia

Ti posto una macro realizzata per uno scopo simile al tuo, la puoi testare ed eventualmente, successivamente, modificare il percorso.
Crea una cartella in C:\ con nome "DocumentiN"
Crei all'interno di questa due o più cartelle che determinano l'anno quindi
2010
2011
2012
All'interno di ognuna cartelle con un nome testo (Cliente) quindi
Pippo
Paperino
Pluto

Infine metti dei file (qualsiasi tipo all'interno di ogni Cliente: pippo paperino, pluto)

Apri un nuovo file di excel e rinomina il Foglio1 nominandolo "Elenco"
all'interno di un modulo inserisci questo codice

Codice: Seleziona tutto

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Public Const SW_NORMAL = 1
Public perc As String

Sub ElencoFileXls()
If Worksheets("Elenco").Range("E2").Value = "" Then Worksheets("Elenco").Columns("IV:IV").Delete Shift:=xlToLeft
If Worksheets("Elenco").Range("D2").Value = "" Then Worksheets("Elenco").Columns("IU:IU").Delete Shift:=xlToLeft
perc = "C:\DocumentiN\" & Worksheets("Elenco").Range("D2").Value & "\" & Worksheets("Elenco").Range("E2").Value & "\"
Worksheets("Elenco").Select
Range("A1").Select
  With ActiveCell
     Worksheets("Elenco").Range("A2", .End(xlDown)).ClearContents
  End With
  Trova Direct:=perc, Estens:="*.*", Inicell:=ActiveCell
End Sub

Private Sub Trova(Direct As String, Estens As String, Inicell As Range)
MStrT = ""
MStrD = ""
    Set fs = Application.FileSearch
    With fs
        .LookIn = perc
        .SearchSubFolders = True
        .Filename = Estens
        If .Execute > 0 Then

            For i = 1 To .FoundFiles.Count
                FileT = Replace(.FoundFiles(i), perc, "")
                Inicell(i + 1) = FileT
                If Worksheets("Elenco").Range("D2").Value = "" Then
                    DirD = Mid(FileT, 15, Len(FileT) - 13)
                    If InStr(DirD, "\") = 0 Then GoTo SaltaDirD
                    If MStrD = Mid(DirD, 1, InStr(DirD, "\") - 1) Then GoTo SaltaDirD
                    Worksheets("Elenco").Cells(Rows.Count, 255).End(xlUp).Offset(1, 0).Value = Mid(DirD, 1, InStr(DirD, "\") - 1)
                    MStrD = Mid(DirD, 1, InStr(DirD, "\") - 1)
                End If
SaltaDirD:
                If Worksheets("Elenco").Range("E2").Value = "" Then
                    DirT = Mid(FileT, 20, Len(FileT) - 18)
                    If InStr(DirT, "\") = 0 Then GoTo SaltaDir
                    If MStrT = Mid(DirT, 1, InStr(DirT, "\") - 1) Then GoTo SaltaDir
                    Worksheets("Elenco").Cells(Rows.Count, 256).End(xlUp).Offset(1, 0).Value = Mid(DirT, 1, InStr(DirT, "\") - 1)
                    MStrT = Mid(DirT, 1, InStr(DirT, "\") - 1)
                End If
SaltaDir:
On Error GoTo 0
            Next i
        End If
    End With
Application.ScreenUpdating = False
Call OrdinaData
If Worksheets("Elenco").Range("E2").Value = "" Then
Call OrdinaIV
End If
If Worksheets("Elenco").Range("D2").Value = "" Then
Call OrdinaIU
End If
Worksheets("Elenco").Range("A1").Select
Application.ScreenUpdating = True
End Sub
Private Sub OrdinaData()
URG = Worksheets("Elenco").Cells(Rows.Count, 3).End(xlUp).Row
    Worksheets("Elenco").Range(Cells(1, 1), Cells(URG, 1)).Select
    Selection.Sort Key1:=Cells(2, 1), Order1:=xlAscending _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
        Range("A1").Select
End Sub
Private Sub OrdinaIU()
    Columns("IU:IU").Select
    Selection.Sort Key1:=Range("IU1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Call EliminaDoppieIU
    Call ConvElencoIU
End Sub
Private Sub EliminaDoppieIU()
URY = Worksheets("Elenco").Cells(Rows.Count, 255).End(xlUp).Row
URY2 = Worksheets("Elenco").Cells(Rows.Count, 255).End(xlUp).Row
For RR1 = 1 To URY - 1
    Str1 = Worksheets("Elenco").Range("IU" & RR1).Value
    If Str1 = "" Then GoTo Esci
    For RR2 = URY2 To RR1 + 1 Step -1
        Str2 = Worksheets("Elenco").Range("IU" & RR2).Value
        If Str2 = "" Then GoTo SaltaRR2
        If Str1 = Str2 Then Range("IU" & RR2).Delete Shift:=xlUp
    Next RR2
SaltaRR2:
    URY2 = Worksheets("Elenco").Cells(Rows.Count, 255).End(xlUp).Row
Next RR1
Esci:
URY = Worksheets("Elenco").Cells(Rows.Count, 255).End(xlUp).Row
Worksheets("Elenco").Range("IU1:IU" & URY).Cut Destination:=Range("IU2")
End Sub
Private Sub ConvElencoIU()
URIU = Worksheets("Elenco").Cells(Rows.Count, 255).End(xlUp).Row
    Range("D2").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$IU$1:$IU$" & URIU
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
Private Sub OrdinaIV()
    Columns("IV:IV").Select
    Selection.Sort Key1:=Range("IV1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Call EliminaDoppie
    Call ConvElenco
End Sub
Private Sub EliminaDoppie()
URY = Worksheets("Elenco").Cells(Rows.Count, 256).End(xlUp).Row
URY2 = URY
For RR1 = 1 To URY - 1
    Str1 = Worksheets("Elenco").Range("IV" & RR1).Value
    If Str1 = "" Then GoTo Esci
    For RR2 = URY2 To RR1 + 1 Step -1
        Str2 = Worksheets("Elenco").Range("IV" & RR2).Value
        If Str2 = "" Then GoTo SaltaRR2
        If Str1 = Str2 Then Range("IV" & RR2).Delete Shift:=xlUp
    Next RR2
SaltaRR2:
    URY2 = Worksheets("Elenco").Cells(Rows.Count, 256).End(xlUp).Row
Next RR1
Esci:
URY = Worksheets("Elenco").Cells(Rows.Count, 256).End(xlUp).Row
Worksheets("Elenco").Range("IV1:IV" & URY).Cut Destination:=Range("IV2")
End Sub
Private Sub ConvElenco()
URIV = Worksheets("Elenco").Cells(Rows.Count, 256).End(xlUp).Row
    Range("E2").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$IV$1:$IV$" & URIV
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub


E nel Vba del foglio "Elenco" inserisci questo codice

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
CheckAreaB = "D2:E2"
If Not Application.Intersect(Target, Range(CheckAreaB)) Is Nothing Then
    If Target.Address = "$D$2" Or Target.Address = "$E$2" Then
        If Target.Address = "$D$2" Then
            Application.EnableEvents = False
            Worksheets("Elenco").Range("E2").Value = ""
            Application.EnableEvents = True
        End If
        Call ElencoFileXls
    End If
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
URE = Cells(Rows.Count, 1).End(xlUp).Row
If URE < 2 Then Exit Sub
CheckAreaA = "A2:A" & URE
If Not Application.Intersect(Target, Range(CheckAreaA)) Is Nothing Then
    If (Selection.Rows.Count + Selection.Columns.Count) > 2 Then Exit Sub
    Nfile = Target
    If Mid(Nfile, Len(Nfile) - 3, 4) = ".xls" Then
        Workbooks.Open(perc & Nfile).Activate
        Exit Sub
    Else
        Dim X As Long
        X = ShellExecute(hWnd, "Open", perc & Nfile, vbNullString, vbNullString, SW_NORMAL)
    End If
End If
End Sub


In ThisWorkbook questo codice

Codice: Seleziona tutto

Private Sub Workbook_Open()
Application.EnableEvents = False
Worksheets("Elenco").Range("D2").Value = Year(Now)
Worksheets("Elenco").Range("E2").Value = ""
Application.EnableEvents = True
Call ElencoFileXls
End Sub


Salva il file dove vuoi
Apri il file e attraverso le celle D2 (scegli l'anno), E2 il Cliente (opzionale)
avrai l'elenco dei file nella colonna A relativa a quell'anno se non inserisci nulla avrai l'elenco di tutti i file in ogni directory anno e directory cliente

Ora puoi cliccare su un nomefile in colonna A e...

Fai sapere
Ciao

[MassiMOE2007]

Grazie per la celerità e l'interesse al mio problema
Credo di dover meglio spiegare le mie necessità per non approfittare della vs gentile disponibilità

Lo spiego in questi termini:
mediante dei file predisposti excel presentiamo centinaia di preventivi ai clienti, nel quale oltre agli estremi del cliente (società, indirizzo, tel. ect.. recuperati dal file CODICECLIENTE.xlsx) compare un elenco di materiali , quantità ed ovviamente il prezzo.

durante l'anno arrivano le conferme d'ordine per questi preventivi (es: il preventivo ROSSI 123.xslx diviene conferma d'ordine)

a questo punto si guardano i preventivi confermati ordine come ROSSI 123.xlsx e si riportano i prodotti e le quantità nella tabella del file PRODUZIONE.xlsx così da avere un elenco ordini;

sulla stessa righa, in corrispondenza ad un numero incrementale id di contratto (C0001) sulla colonna A, il riferimento di preventivo colonna B (Rossi 123), richiamo sulla colonna C il nome società (sempre da CODICECLIENTE), sulle colonna D, E, F, ect. in corrispondenza all'articolo di nostra produzione (es:Art.001 colonna D) le quantità richieste come da preventivo Rossi (3, 5 ,8 ect.)

all'interno di tutti i file preventivo esiste una tabella materiali quantità per la quale la ricerca delle quantità in corrispondeza all'articolo basta un CERCA.VERT

il mio problema è ad ogni riga puntare ad un file preventivo diverso
pensavo di poterlo risolvere così:
='\\Server\Preventivi2012\[B2]Foglio1'!$A$2
dove nella cella B2 indico il nome file del preventivo per il quale voglio recuperare i dati.


Ho guardato il topic suggerito: credo di capire che indiretto funzioni con diversi fogli nello stesso file e problema più grande la necessità di dover aprire tutti i file (nel mio caso 400/500 preventivi)

Ho provato anche la sol. con il VBA ( ho copiato la seconda parte di codice in un modulo2) risponde errore run-time 09

[Flash30005]

Provo ad inviarti il file
che sarà sicuramente da adattare alle tue esigenze ma essendo alquanto complesso è opportuno che venga innanzitutto testato da te.
Per fare questo devi creare le directory in C:\
come riportato nel post precedente ed inserire dei file (qualsiasi file) all'interno delle directory
Download File

Per utilizzarlo segui le istruzioni indicate nel post ore 8:26

Ciao

[MassiMOE2007]

Grazie Flash per il file,


all' avvio restituisce errore run-time 445

dal debug, si riscontra nella sub trova del modulo1 un errore in questa stringa:

Set fs = Application.FileSearch

...

Funzionando dovrebbe restituire l'elenco dei file trovati sulla colonna A in base ai criteri di ricerca in D2 o E2

Mi manca però il nesso con il mio problema: come posso prendere un informazione da un file preventivo richiesto, ad esempio la data di consegna merce nella casella E5 del foglio "Prev12"

[Flash30005]

Se vuoi usare quel file
devi creare una struttura come descritta nel post precedente e cioè
1) Creare una directory in C:\ di nome C:\DocumentiN (quindi avrai un percorso così C:\DocumentiN
2) all'interno di questa directory altre directory una per anno (2010, 2011, 2012..)
e avrai quindi C:\DocumentiN\2012\
3) all'interno di queste (per anno) altre directory nome cliente (Pippo, Pluto, Topolino)
4) dentro queste ultime inserisci file immagini o altro (jpg, mdb, doc, pdf, xls etc)
ora apri il file e in E2 scegli l'anno per avere un elenco di tutti i file di quell'anno
oppure (e) in F2 scegli un cliente (pippo, topolino, pluto; che dovrebbe apparire se hai fatto regolarmente tutto) e avrai i file di quel cliente.

L'errore del Fileserch è dovuto a excel2007 che non è compatibile con quella funzione
Magari arriverà qualche consiglio da parte di chi usa la tua stessa versione per sostituire quel codice

ciao

[MassiMOE2007]

La struttura è stata creata , sicuramente come dici tu è un problema di versione.
Non è però un elenco che mi serve.
Grazie per averci provato.

[Flash30005]

Il mio era solo un esempio che si può aggiustare per excel2007 perché penso che da un elenco (che si può adattare anche a data invece che a cliente) puoi prelevare dei dati (invece di visualizzare il file), insomma è tutto da imbastire ma se la "stoffa" già non va bene è inutile metter mano all'ago

ciao

[Anthony47]

Io non ho ancora capito quanti dati devi estrarre dal file Preventivo e che uso ne dovrai fare, quindi mi permetto di indicare un approccio alternativo:
-ti crei nel file Produzione un foglio di servizio che chiamerai Scratch
-quando scrivi il nome di un file (un preventivo) in B2 del foglio (di Produzione) su cui lavori una macro ti apre quel file, legge un foglio precisato nella macro e ne mette tutto il contenuto nel foglio Scratch
-chiudo il file "preventivo"
A questo punto dovresti avere il Scratch i dati da cui attingere con normali formule

La macro che fa questo e' la seguente:

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
CheckA = "B2"    '<<< La cella col nome del Preventivo
Suff = ".xls"    '<<< Oppure .xlsx, oppure ...
FoglioN = "Foglio1" '<<< Il nome del Foglio di preventivo da copiare
Percorso = "\\Server\Cartella1\" '<<< Il path completo dove reperire il Preventivo
'
If Application.Intersect(Target, Range(CheckA)) Is Nothing Then Exit Sub
If Range(CheckA) = "" Then Exit Sub
Application.EnableEvents = False
Sheets("Scratch").Cells.Clear
If Len(Range(CheckA).Value) = Len(Replace(Range(CheckA).Value, ".xls", "")) Then
    Range(CheckA).Value = Range(CheckA).Value & Suff
End If
'
NFile = Range(CheckA).Value
Workbooks.Open Percorso & NFile
Sheets(FoglioN).Select    '<<< CANCELLARE se il file contiene 1 solo foglio
ActiveSheet.Cells.Copy
ThisWorkbook.Activate
Sheets("Scratch").Select
ActiveSheet.Range("A1").Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
ActiveCell.PasteSpecial Paste:=xlPasteFormats
'
Application.CutCopyMode = False
Workbooks(NFile).Close Savechanges:=False
Application.EnableEvents = True
End Sub


Uso: tasto dx sul tab col nome del foglio di partenza (quello dove scriverai il nome del file di preventivo), scegli Visualizza codice, copia il codice e incollalo nel frame di dx.

Personalizza le istruzioni marcate <<<; accertati di avere nel tuo file corrente (immagino Produzione.xlsx) un foglio di servizio chiamato Scratch; poi scrivi in B2 (o nelle celle che hai impostato come "CheckA) il nome file; la macro fara' il resto lasciandoti nel foglio Scratch il cotenuto del foglio prescelto del preventivo prescelto.

Spero ti sia di qualche utilita'.

Ciao

[MassiMOE2007]

Ciao Anthony,
provo a spiegarmi con un esempio:

Pippo, Pluto e Paperino richiedono un preventivo, archivierò 3 file su pc quali:

Prev 1.xls
cella A1 Sig. Pippo
cella A2 macchina rossa (articolo)
cella B2 3 (quantità)
cella C2 2 (prezzo unitario)
cella D2 6 (prezzo totale)

Prev 2.xls
cella A1 Sig. Pluto
cella A2 macchina blu (articolo)
cella B2 1 (quantità)
cella C2 3 (prezzo unitario)
cella D2 3 (prezzo totale)

Prev 3.xls
cella A1 Sig. Paperino
cella A2 macchina verde (articolo)
cella B2 2 (quantità)
cella C2 2 (prezzo unitario)
cella D2 4 (prezzo totale)


Pippo e Paperino confermano l'ordine
Mi serve un file Produzione.xls così strutturato:

colonna A Prog.Contratti;
colonna B Rif.Preventivo;
colonna C NominativoCliente;
colonna D macchina rossa;
colonna E macchina blu;
colonna F macchina verde
ect.

cella A2 C001 (numero contratto) inserimento manuale
cella B2 Prev1 (riferimento preventivo) inserimento manuale
cella C2 Pippo (richiamo cliente da prev 1)
cella D2 3 (richiamo q.tà da prev 1)
celle E2-F2 ect. 0 (richiamo q.tà da prev 1)

cella A3 C002 (numero contratto) inserimento manuale
cella B3 Prev3 (riferimento preventivo) inserimento manuale
cella C3 Paperino(richiamo cliente da prev 3)
cella D3 0 (richiamo q.tà da prev 3)
cella E3 2 (richiamo q.tà da prev 3)
celle F3 ect. 0 (richiamo q.tà da prev 3)


nel file produzione posso verificare il totale delle macchine vendute visualizzando contemporaneamente le singole quantità per conferma d'ordine. il prev 2 non confermato NON COMPARE. i preventivi confermati richiamano i dati dai relativi file.
Il mio problema, forse tanto semplice che è stato sopravvalutato con soluzioni complesse per la mie conoscenze, è quello di poter creare un'unica formula che modifichi il puntamento file in base alla cella B2,B3 ect. evitandomi di modificare manualmente il percorso:

se la quantità di macchine rosse per Pippo la richiamo così:
=CERCA.VERT(D1;'\\Server\Preventivi2012\[Prev 1.xls]Foglio1'!A2:B10;2;FALSO)

quelle per Paperino:
=CERCA.VERT(D1;'\\Server\Preventivi2012\[Prev 3.xls]Foglio1'!A2:B10;2;FALSO)

avendo circa 80/100 articoli (macchine rossa, blu verdi ect.) da gestire ed altre info da richiamare, mi chiedevo se esiste una funzione o macro ( che ancora non conosco, e sono molte ) che possa modificare la parte di formula che punta al file , senza farlo in manuale o con lo strumento sostituisci, ma utilizzando una cella di appoggio (colonna B).

Grazie per la pazienza.

[Flash30005]

Quanto richiedi è abbastanza complesso non tanto per la realizzazione delle macro ma per la struttura e lo splittamento dei file.
Secondo me è più opportuno che invii tutti i file interessati (Pippo, Pluto e Paperino etc etc)
e in uno di essi descrivi ciò che vorresti ottenere
altrimenti colui che vorrebbe darti un consiglio (o realizzare per te il programma) è costretto a creare i file come da tua descrizione, con molta possibilità di sbagliare i riferimenti e quindi con notevole perdita di tempo per successivi aggiustamenti.

ciao

[Anthony47]

Leggi il messaggio di Flash sopra.

Domanda: Ma quindi sono preventivi con una sola riga??