[excel]: creare un riferimento ad un file con nome variabile

[luca62]

Ciao a tutti
Ho un ...bel problemino,almeno per me..
in pratica necessito che in una cella dove ho un riferimento ai valori di un cella
di un file esterno, tale riferimento ,in particolare il nome del file sia variabile
faccio l'esempio:
sono nel file PIPPO e foglio di lavoro PLUTO,in una cella di questo foglio di lavoro
scrivo
='H:\produzione\scheda preventivo\[55900.xls]CARTIGLIO'!C4

per richiamare il valore del file 55900, foglio di lavoro CARTIGLIO e cella C4


e tutto va bene, il mio problema è rendere il nome del file [55900.xls] (solo del file)
variabile in funzione di un valore di una cella C2 posta nello stesso foglio PLUTO
ho provato a inserire una funzione all'interno della parentesi quadra che
identifica il file (che è la sola variabile che cambia) ma niente!!
Chi mi da un help?

Grazie e BUON NATALE

[Anthony47]

Ciao luca62, benvenuto nel forum.
In prima battuta, potresti usare la funzione "Indiretto" per puntare a un file che costruisci concatenando il path (fisso), il nome file contenuto in C2, foglio e cella (fissi); questo presuppone pero' che quel file sia "Aperto" altrimenti avresti errore.
Suggerirei quindi di usare una macro di WorksheetChange per ricompilare la formula quando modifichi il contenuto di C2.
Ad esempio:

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
'
FileIn = "$C$2" '<<< La cella che punta al file
If Target.Address <> FileIn Then Exit Sub
FormIn = "C20"  '<<< La cella con la formula da variare
If Range(FileIn) <> "" Then
    Range(FormIn).Formula = _
        "= 'H:\produzione\scheda preventivo\[" & _
        Range(FileIn).Value & ".xls]CARTIGLIO'!C4"   <**
End If
End Sub


Tasto dx sul tab col nome Pippo, scegli Visualizza codice; copia il codice e incollalo nel frame di dx; personalizza le righe marcate <<<.
Controlla anche la riga marcata <**: l' uso o meno dei 4 caratteri ".xls" dipende da come scrivi il nome file in C2 (con o senza estensione).
Torna sul foglio Excel e prova a modificare il valore della cella che punta al file. Qualora scriva un nomefile inesistente ti si aprira' una finestra di dialogo che ti invitera' a scegliere un file valido; in questi casi scegli "Annulla" e reimposta un file corretto nella cella di partenza (che, per evitare errori, e' meglio sia sottoposta a Convalida da elenco).

Fai sapere, ciao.

[luca62]

Anthony,
intanto grazie ma..mi sono annodato anche perchè non mi ero spiegato
completamente e per evitare misunderstanding riscrivo seza PIPPO E Pluto,
sono nel file H:\commesse\Com standard.xls e foglio di lavoro 55900 ALBERO CON ECCENTRICI ,in una cella di questo foglio di lavoro
scrivo
='H:\produzione\scheda preventivo\[55900.xls]CARTIGLIO'!C4
per richiamare il valore del file 55900, foglio di lavoro CARTIGLIO e cella C4
e tutto va bene, il mio problema era rendere il nome del file [55900.xls] (solo del file)
variabile in funzione di un valore di una cella C2 posta nello stesso foglio 55900 ALBERO CON ECCENTRICI (era PLUTO).
la cella C2 è una cella creata con la funzione stringaestrai etc che mi dia renda il
numero 55900 corrispondente ai primi 5 caratteri del nome del foglio.
Volendo creare questo irferimento variabile in funzione del nome del foglio, ho
fatto penso un passo intermedio che forse non occorre (ossia andare a scrivere nella cella c2 il valore
ricavato dal nome del foglio)
La domanda: è possibile ricavare il riferimento variabile direttamente dal nome del foglio?
ossia
='H:\produzione\scheda preventivo\[55900.xls]CARTIGLIO'!C4
con il 55900 ricavato (in nome variabile) come i primi 5 caratteri del nome del foglio dove mi trovo?ossia
in questo caso
H:\commesse\[Com standard.xls]55900 ALBERO CON ECCENTRICI! ?
Avendo 30 fogli di lavoro dovrò utilizzare la stessa macro in funzione
del nome del foglio che ovviamente cambierà

ho provato ma..a parte il mal di testa forse per l'ora mattutina....non levo un

ragno dal buco

grazie e Auguri

[Anthony47]

Allora lascia stare la macro precedente e inserisci invece questa nel modulo ThisWorkbook dell' editor delle macro:

Codice: Seleziona tutto

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
FileIn = Trim(Left(Sh.Name, 5))
If IsNumeric(FileIn) Then

FormIn = "C20"  '<<< La cella con la formula da variare
    Range(FormIn).Formula = _
        "= 'H:\produzione\scheda preventivo\[" & _
        FileIn & ".xls]CARTIGLIO'!C4"   '<**
End If
End Sub


In questo modo tutte le volte che attivi un foglio il cui nome cominci con 5 caratteri numerici, o che sia piu' corto di 5 ma sia numerico, imposterai la formula nella cella puntata dalla variabile FormIn.

C2 viene ignorata.

Ciao, fai sapere.

[luca62]

aNTHONY GRAZIE MA...SEI TROPPO VELOCE,,,,
ALMENO PER ME IGNORANTE....
HO PERSO LA TESTA A MODIFICAT
RE IL TUO SUGGERIMENTO
COMUNQUE GRAZIE

[Anthony47]

Pero' io non ho capito se hai risolto o non ancora...

[luca62]

no non ancora... probabilmente sono troppo acerbo!

[Anthony47]

Ma del suggerimento che avevo dato (viewtopic.php?f=26&t=97981&p=563478#p563112) che uso ne hai potuto fare?

Ciao

[luca62]

Anthony torno con un argomento che avevo lasciato a metà
perchè lavorando su questi file devo dare una botta alla teoria e una alla pratica.
allego il file
http://www.filedropper.com/esempioluca62
è un file che piano piano con i vs preziosi suggerimenti sto cercando
di automatizzare con macro e funzioni.
ti spiego la mia esigenza che mi piacerebbe risolvere con una funzione più che con unamacro.
Nel mio file ho un primo foglio che serve da legenda e altri fogli operativi.
nei fogli dal 2^ in poi dalla riga 3 fino alla 100 inclusa ho la necessità di automatizzare un lavoro che faccio
ora manualmente.
Adesso faccio così:
posizione b3 :='H:\produzione\scheda preventivo\[PIPPO.xls]CARTIGLIO'!A3 devo cambiare il
riferimento al file esterno in [70193.xls] che poi è un valore numerico che ho sempre nella cella $D$2 e quindi vado a sostituire
manualmente tale cella, e le celle C3,D3,E3 fatto questo copio la riga 3 fino alla riga 100 e proteggo il
foglio (.le prime 6 colonne,ora lasciato sprotetto nell'esempio)
esiste un modo con le funzione di rendere sempre il riferimento al file esterno uguale al numero che ho in $C$2
o in A3 (copia del valore $C$2) ?

NB col suggerimento che mi avevi dato mi sono annodato, mi sembra complicato

[Anthony47]

Si potrebbe fare con formule se il file a cui si fa di volta in volta riferimento risulta "Aperto"; il che mi sembra difficile da ottenere, vero?
Inoltre il file e' zeppo di formule con "collegamento" al file remoto, quindi l' approccio che ti avevo proposto (che cambiava una sola formula) va sicuramente rivisto.
Comunque visto che il file e' zeppo anche di macro, quale e' esattamente il motivo (se veramente c' e') per cui preferiresti farlo con formule e non con macro?

Ciao

[luca62]

avversione naturale a ciò che si sa fare meno....ma se è indispensabile
ben venuta macro...effettivamente il file a cui si fa riferimento non è aperto

[Anthony47]

Come immaginavo...
Allora prova questa procedura:
-tasto dx sul tab con nome del primo dei fogli su cui vuoi fare la variazione (ad es. 70193 1^OPERAZIONE DIA 65)
-scegli Visualizza codice, ti si apre l' editor delle macro puntando al modulo che ci interessa
-copia il seguente codice e incollalo nel frame di dx

Codice: Seleziona tutto

Private Sub Worksheet_Activate()
Dim myBase(1 To 4)
myBase(1) = "'H:\produzione\scheda preventivo\[ZCZCX.xls]CARTIGLIO'!A3"
myBase(2) = "'H:\produzione\scheda preventivo\[ZCZCX.xls]CARTIGLIO'!C3"
myBase(3) = "'H:\produzione\scheda preventivo\[ZCZCX.xls]CARTIGLIO'!B3"
myBase(4) = "'H:\produzione\scheda preventivo\[ZCZCX.xls]CARTIGLIO'!D3"
'Check esistenza file:
mySplit = Split(myBase(1), "[")
myFile = Replace(mySplit(0), "'", "") & Range("$D$2").Value & ".xls"
If Len(Dir(myFile)) = 0 Then
    MsgBox ("il file " & myFile & " non esiste" & vbCrLf & _
        "Le formule non sono state alterate")
    Exit Sub
End If
'
Application.EnableEvents = False
LastA = Cells(Rows.Count, 1).End(xlUp).Row
For I = 1 To 4
    Cells(3, 1 + I).Resize(LastA - 3, 1).FormulaLocal = "=" & Replace(myBase(I), "ZCZCX", Range("$D$2").Value)
Next I
Application.EnableEvents = True
End Sub


A questo punto torna su excel, vai sul foglio STAMPI DIA 65 e modifica il "Numero" richiamato in D2 del foglio su cui hai applicato la macro; seleziona il foglio 70193 1^OPERAZIONE DIA 65 e controlla le formule, dovrebbero essere state aggiornate. Prova anche con un "Numero" inesistente.

Se il comportamento e' coerente con le attese allora ripeti l' inserimento della stessa macro sugli altri fogli interessati.

Fai sapere, ciao.

[luca62]

SPETTACOLARE!!!!
PERFETTO!
step by step..sto arrivando a pallino!
ti faccio alcune domande:
1) è possibile con la stessa macro far cambiare il nome al foglio dove faccio il cambiamento?
e che mi dia prima il numero,spazio e la descrizione '(quella che entra?
es 70210 stampo 2^operazione dia 65
2) la macro che mi hai dato, non figura tra le macro del file ,ma rimane comunque una
proprietà di quel file e di quel foglio di lavoro? facendo un salva come al file la posso
"trasportare sul nuovo file?
3) un altra cosa la posto in un altro post perchè penso sia più utile a tutti gli utenti del forum

[Anthony47]

Cambiare nome del foglio potrebbe essere pericoloso se all' interno di altre macro fosse usato proprio in nome foglio come elemento di selezione (per questo nelle macro complesse si usa il "codename" del foglio, non il "name").
Inoltre il nome foglio e' limitato a max 31 caratteri, e la tua stringa non rispetta questa regola oggi, ne' penso che possa garantire di rispettarla domani.
Comunque puoi inserire subito prima di End Sub una riga del tipo

Codice: Seleziona tutto

ActiveSheet.Name = Left([D2] & " " & [C2], 31)


Questa ti modifica il nome con i primi 31 caratteri che risultano dal concatenamento tra Numero (in D2) e Denominazione (in C2); non so se ti puo' andare bene.

La macro che ti ho dato ieri sera e' a tutti gli effetti una macro del file, e si replica nei file generati tramite Salva con nome.

Ciao

[luca62]

Ciao Anthony,

tempo fa mi confezionasti una macro per andare a copiare dei dati da altri file di nome variabile, su n righe.
Macro che ho poi trasformato cos':

Codice: Seleziona tutto

Private Sub Worksheet_Activate()
ActiveSheet.Unprotect
Dim myBase(1 To 12)
myBase(1) = "'H:\disegni\commesse\[ZCZCX.xlsm]ORDINI'!A2"
myBase(2) = "'H:\disegni\commesse\[ZCZCX.xlsm]ORDINI'!B2"
myBase(3) = "'H:\disegni\commesse\[ZCZCX.xlsm]ORDINI'!C2"
myBase(4) = "'H:\disegni\commesse\[ZCZCX.xlsm]ORDINI'!D2"
myBase(5) = "'H:\disegni\commesse\[ZCZCX.xlsm]ORDINI'!E2"
myBase(6) = "'H:\disegni\commesse\[ZCZCX.xlsm]ORDINI'!F2"
myBase(7) = "'H:\disegni\commesse\[ZCZCX.xlsm]ORDINI'!G2"
myBase(8) = "'H:\disegni\commesse\[ZCZCX.xlsm]ORDINI'!H2"
myBase(9) = "'H:\disegni\commesse\[ZCZCX.xlsm]ORDINI'!I2"
myBase(10) = "'H:\disegni\commesse\[ZCZCX.xlsm]ORDINI'!J2"
myBase(11) = "'H:\disegni\commesse\[ZCZCX.xlsm]ORDINI'!K2"
myBase(12) = "'H:\disegni\commesse\[ZCZCX.xlsm]ORDINI'!L2"
'Check esistenza file:
mySplit = Split(myBase(1), "[")
myFile = Replace(mySplit(0), "'", "") & Range("$b$1").Value & ".xlsm"
If Len(Dir(myFile)) = 0 Then
    MsgBox ("il file " & myFile & " non esiste" & vbCrLf & _
        "Le formule non sono state alterate")
    Exit Sub
End If
'
Application.EnableEvents = False
LastA = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To 12
    Cells(2, 0 + i).Resize(LastA - 1, 1).FormulaLocal = "=" & Replace(myBase(i), "ZCZCX", Range("$b$1").Value)
   
Next i
Application.EnableEvents = True
ActiveSheet.Name = Left([B1] & " " & [C2], 7)
ActiveSheet.Protect
End Sub

[/code]

Tutto ok tranne che mi esegue la ricerca sui file esterni solo fino ad un certo numero di righe.
Non riesco a capire quale funzione nella macro determina per quante righe (dalla seconda in poi)
itero il procedimento (io vorrei impostare da 2 a 2000 ad esempio come è possibile?

[Anthony47]

Tutto ok tranne che mi esegue la ricerca sui file esterni solo fino ad un certo numero di righe.
Non riesco a capire quale funzione nella macro determina per quante righe (dalla seconda in poi)
itero il procedimento (io vorrei impostare da 2 a 2000 ad esempio come è possibile?

La macro parte dal presupposto che il foglio contenga gia' delle formule "statiche", e le modifica per inserire un nome file "dinamico".
Quindi se il problema e' che oggi vengono messe poche formule dovrebbe essere sufficiente copiare (anche solo in colonna A) le formule presenti fino al rigo che ti interessa, poi esci e rientri sul Foglio.

Ciao

[luca62]

tempo fa mi avete "conefzionato una macro "magica" che mi permette di creare il riferimento ad un file con un nome variabile.
Il mio quesito è questo è possibile fare in modo che oltre al nome del file sia variabile anche il foglio?.
allego il codice che mi avevate confezionato


Private Sub Worksheet_Activate()
Dim myBase(1 To 4)
myBase(1) = "'H:\produzione\scheda preventivo\[ZCZCX.xls]CARTIGLIO'!A3"
myBase(2) = "'H:\produzione\scheda preventivo\[ZCZCX.xls]CARTIGLIO'!C3"
myBase(3) = "'H:\produzione\scheda preventivo\[ZCZCX.xls]CARTIGLIO'!B3"
myBase(4) = "'H:\produzione\scheda preventivo\[ZCZCX.xls]CARTIGLIO'!D3"
'Check esistenza file:
mySplit = Split(myBase(1), "[")
myFile = Replace(mySplit(0), "'", "") & Range("$D$2").Value & ".xls"
If Len(Dir(myFile)) = 0 Then
MsgBox ("il file " & myFile & " non esiste" & vbCrLf & _
"Le formule non sono state alterate")
Exit Sub
End If
'
Application.EnableEvents = False
LastA = Cells(Rows.Count, 1).End(xlUp).Row
For I = 1 To 4
Cells(3, 1 + I).Resize(LastA - 3, 1).FormulaLocal = "=" & Replace(myBase(I), "ZCZCX", Range("$D$2").Value)
Next I
Application.EnableEvents = True
End Sub
[/code]
Vorrei che il foglio del file invece che fisso (CARTIGLIO) fosse anch'esso variabile in funzione del valore che leggo nella cella A1.
è possibile?

[Anthony47]

Nella cella, invece del solo NomeFile, inserisci

Codice: Seleziona tutto

 [NomeFile.xls]NomeFoglio

Poi modifica il contenuto di myBase in

Codice: Seleziona tutto

myBase(1) = "'H:\produzione\scheda preventivo\ZCZCX'!A3"
myBase(2) = "'H:\produzione\scheda preventivo\ZCZCX'!C3"
'etc
'etc


Il resto della macro dovrebbe essere a posto

Ciao

[luca62]

ciao Anthony e grazie tante , il mio problema è che attualmente il nome file , lo vado a prendere nella cella d2, dove una semplice formula (mi prende un dato da un altro foglio) mi da il valore del file.
ossia nella cella d2 dal quale prendo il nome del file (esempio: 60750) ho scritto =Lista!C4, cioè vado a prendere il valore 60750 che trovo nella cella c4 del foglio lista.
adesso se ho capito devo invece mettere nella cella d2 il nome del file e del foglio.
.Il nome del file lo prendo da Lista!C4, il nome del foglio da Lista!G4, come devo scrivere la formula in d2?
=Lista!C4 e poi?
non vorrei poi inserire nella cella d2 un richiamo all'estensione del file.
grazie ancora

[luca62]

Anthony,

sono riuscito come da tuo consiglio a inserire nella cella dove vado a prendere il riferimento
[NomeFile.xls]NomeFoglio
ma andando a sostituire:
myBase(1) = "'H:\produzione\scheda preventivo\ZCZCX'!A3"
myBase(2) = "'H:\produzione\scheda preventivo\ZCZCX'!C3"
'etc
'etc

la macro mi da un errore del tipo: non trova il file
H:\produzione\scheda preventivo\ZCZCX'!A3"[NomeFile.xls]NomeFoglio!A3

mi dai un occhiata sulla macro? deve esserci un errore qui:

'Check esistenza file:
mySplit = Split(myBase(1), "[")
myFile = Replace(mySplit(0), "'", "") & Range("$D$2").Value & ".xls"
If Len(Dir(myFile)) = 0 Then
MsgBox ("il file " & myFile & " non esiste" & vbCrLf & _
"Le formule non sono state alterate")