[es] - [Excel] Povezivanje ćelija iz različitih fajlova

paja_odi

Član broj: 54344
Poruke: 29
*.broadband.blic.net.

Profil

[Excel] Povezivanje ćelija iz različitih fajlova

^{13.02.2007. u 13:03 - pre 209 meseci}

U jednom foderu imam npr. ove fajlove knjiga.xls, k 1-2.xls, k 2-2.xls, k 3-2.xls.
Fajl knjiga.xls je otprilike sledećeg sadržaja:

____A_____B___
1 Datum Količina
2 1-2
3 2-2
4 3-2

E sad količina za datum 1-2 bi trebala biti jednaka ćeliji A1 iz fajla k 1-2.xls, količina za datum 2-2 bi trebala biti jednaka ćeliji A1 iz fajla k 2-2.xls i tako redom. Znam da se to može postići formulom ='[k 1-2.xls]Sheet1'!$A$1 ali kao se može postići da ovaj dio u formuli 1-2 bude zamjenjen sa adresom ćelije A2, a da rezultat ostane isti.

Odgovor na temu

Jpeca
Predrag Jovanović
poslovni analitičar
Gowi
Pančevo

Moderator
Član broj: 25683
Poruke: 2268
212.200.27.*

Sajt: www.gowi.rs

+109 Profil

Re: [Excel] Povezivanje ćelija iz različitih fajlova

^{13.02.2007. u 14:10 - pre 209 meseci}

Ako sam dobro razumeo treba da formiraš string koji umesto naziva fajla uzima sadržaj iz ćelije A2, A3...
Onda primeniš funkciju INDIRECT koja taj string interpretira kao adresu:

Code:
=INDIRECT("[k " & A2 & ".xls]Sheet1'!$A$1")

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Odgovor na temu

paja_odi

Član broj: 54344
Poruke: 29
*.broadband.blic.net.

Profil

Re: [Excel] Povezivanje ćelija iz različitih fajlova

^{13.02.2007. u 14:58 - pre 209 meseci}

Ova formula ne radi.
Treba da u formuli ='[k 1-2.xls]Sheet1'!$A$1 dio 1-2 zamjenim sa adresom ćelije A2 da kad tu formulu ='[k 1-2.xls]Sheet1'!$A$1 kopiram u ćeliju B3 ne trebam ručno mijenjati ovaj dio 1-2 u 2-2 nego da sama uzme vrijednost iz ćelije A3.

Odgovor na temu

Jpeca
Predrag Jovanović
poslovni analitičar
Gowi
Pančevo

Moderator
Član broj: 25683
Poruke: 2268
212.200.27.*

Sajt: www.gowi.rs

+109 Profil

Re: [Excel] Povezivanje ćelija iz različitih fajlova

^{13.02.2007. u 15:10 - pre 209 meseci}

Da tako sam i mislio. Ja sam poceo od tvoje formule pa sam zaboravio da izbacim ' ispred !. Probaj bez toga ili dodaj ' ispred [

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Odgovor na temu

paja_odi

Član broj: 54344
Poruke: 29
*.broadband.blic.net.

Profil

Re: [Excel] Povezivanje ćelija iz različitih fajlova

^{13.02.2007. u 21:22 - pre 209 meseci}

Prepravio sam ovako kao što si rekao, ali formula radi samo kad su otvoreni i fajlovi k 1-2.xls, k 2-2.xls itd.

Odgovor na temu

Jpeca
Predrag Jovanović
poslovni analitičar
Gowi
Pančevo

Moderator
Član broj: 25683
Poruke: 2268
212.200.27.*

Sajt: www.gowi.rs

+109 Profil

Re: [Excel] Povezivanje ćelija iz različitih fajlova

^{14.02.2007. u 08:07 - pre 209 meseci}

Formula INDIRECT radiće samo ako je radna sveska iz koje se referenciraju podaci otvorena. Ukoliko koristiš uvek isti skup radnih sveski možeš sve da ih otvoriš zajedno sa rezultujućom pa da snimiš kao Workspace.

Alternativa je da kreiraš makro, koji će na otvaranje radne sveske (Workbook Open) učitati potreban podatke iz druge radne sveske.

Code:

Private Sub Workbook_Open()
' Prenosi vrednost celije A1 iz radne sveske upisane u A2 u tekucu radn svesku
   Dim ref As Workbook

   Set ref = Workbooks.Open(ThisWorkbook.Path & "\k " & ThisWorkbook.Sheets(1).Range("A2") & ".xls")
   ThisWorkbook.Sheets(1).Range("B2").Value = ref.Sheets(1).Range("A1")
   ref.Close
    ' itd za ostale radne sveske
End Sub

Ovo rešenje podrazumeva da radne svekse k 1-2 itd ostaju neizmenjene dok radiš sa rezultujućom radnom sveskom.

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Odgovor na temu

adasevic
Aleksandar Adasevic
Beograd

Član broj: 130177
Poruke: 18
*.pmlucas.com.

Profil

Re: [Excel] Povezivanje ćelija iz različitih fajlova

^{21.02.2007. u 15:00 - pre 208 meseci}

Citat:

Jpeca:

Code:

Private Sub Workbook_Open()
' Prenosi vrednost celije A1 iz radne sveske upisane u A2 u tekucu radn svesku
   Dim ref As Workbook

   Set ref = Workbooks.Open(ThisWorkbook.Path & "\k " & [b]ThisWorkbook.Sheets(1).Range("A2")[/b] & ".xls")
   ThisWorkbook.Sheets(1).Range("B2").Value = ref.Sheets(1).Range("A1")
   ref.Close
    ' itd za ostale radne sveske
End Sub

Probao sam da unesem ovakav kod. Javlja mi da ne moze da nadje fajl na putanji koja mu je zadata. U stvari prikaze sve sem naziva fajla, pa kapiram da ovde ne radi onaj deo koji sam boldovao.

Drugi problem je sto mi kada neke stvari izmenim javlja da ne moze da pristupi folderu u kome se nalazi xls. Svi xls-ovi u njemu nisu read-only, ali folder jeste i ne mogu da mu to otkvacim. Probao sam i sa preuzimanjem ownershipa ali dzaba...

Odgovor na temu

Jpeca
Predrag Jovanović
poslovni analitičar
Gowi
Pančevo

Moderator
Član broj: 25683
Poruke: 2268
212.200.27.*

Sajt: www.gowi.rs

+109 Profil

Re: [Excel] Povezivanje ćelija iz različitih fajlova

^{21.02.2007. u 15:29 - pre 208 meseci}

Šta ti se nalazi u ćeliji A2 aktivne radne sveske?
Uključi prikaz Immidate Window iz View menija.
U ovom prozoru otkucaj:
?ThisWorkbook.Path & "\k " & ThisWorkbook.Sheets(1).Range("A2") & ".xls"

Trebalo bi da bude pun naziv radne sveske koju povezuješ (uključujući i putanju). Ukoliko nije, nadam se da ćeš pronaći gde je greška.

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Odgovor na temu

adasevic
Aleksandar Adasevic
Beograd

Član broj: 130177
Poruke: 18
*.pmlucas.com.

Profil

Re: [Excel] Povezivanje ćelija iz različitih fajlova

^{21.02.2007. u 15:46 - pre 208 meseci}

izbaci mi D:\TS\k .xls u Immidiate window...

Zasto nece da izbaci 1-1, 1-2... posle k nego izbaci blanko... Gde gresim?

Odgovor na temu

Jpeca
Predrag Jovanović
poslovni analitičar
Gowi
Pančevo

Moderator
Član broj: 25683
Poruke: 2268
212.200.27.*

Sajt: www.gowi.rs

+109 Profil

Re: [Excel] Povezivanje ćelija iz različitih fajlova

^{21.02.2007. u 16:34 - pre 208 meseci}

Kao da gledaš prazni ćeliju.
1/ Da li je dobra ćelija. Selektuj ćeliju koja ti treba pa ukucaj u Immediate window
?ActiveCell.Value
Da li dobiješ 1-1 (ili šta već treba)
Da li isto dobiješ i kad otkucaš
?ActiveSheet.Range("A2").Value
2/Da li je to taj list?
Proveri po nazivu. Ukucaj u Immediate window :
?Sheets(1).Name
pa vidi da li se naziv slaže sa onim što imaš

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Odgovor na temu

adasevic
Aleksandar Adasevic
Beograd

Član broj: 130177
Poruke: 18
*.pmlucas.com.

Profil

Re: [Excel] Povezivanje ćelija iz različitih fajlova

^{22.02.2007. u 13:09 - pre 208 meseci}

Uspeo sam da uskladim sve kako treba i sada mi ucitava ok podatke.

Javlja mi se nov problem - posto na ovaj nacin importujem mnogo fajlova (1200) i oni se mogu menjati vremenom, veliki je problem sto mi on za svaki upis trazi da snimim promene u .xls-u iz koga vucem podatke (sta ja tu uopste menjam?)

Ako svaki put moram da kliknem 1200 puta yes (nema yes to all) onda to bas i nije funkcionalno... Inace samo sam kopirao formulu x puta i menjao referentne celije.

Takodje me zanima, posto nisam do sada radio sa makroima kako da podesim da se makro automatski startuje (tj. da se ova funkcija izvrsi bez predhodnog pozivanja?

Odgovor na temu

Jpeca
Predrag Jovanović
poslovni analitičar
Gowi
Pančevo

Moderator
Član broj: 25683
Poruke: 2268
212.200.27.*

Sajt: www.gowi.rs

+109 Profil

Re: [Excel] Povezivanje ćelija iz različitih fajlova

^{22.02.2007. u 13:41 - pre 208 meseci}

Problem sa pitanjem rešićeš ako prilikom zatvaranja tabele staviš da ne prikazuju upozorenja - vidi kod u temi http://www.elitesecurity.org/t245573-0#1477344

Probaj dalje da se sam snađeš koristeći linkove na top temi Rad sa makroima.

Da bi ti se automatski startovao zato sam i rekao da ga ubaciš u Workbook_Open - ovo nije standardni makro nego makro koji se vezuje za određenu radnu svesku. Dupli klik na Thisworkbook u stablu projekta pa iz padajuće liste izaberi Workbook

_{[Ovu poruku je menjao Jpeca dana 22.02.2007. u 14:56 GMT+1]}

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Odgovor na temu

adasevic
Aleksandar Adasevic
Beograd

Član broj: 130177
Poruke: 18
*.pmlucas.com.

Profil

Re: [Excel] Povezivanje ćelija iz različitih fajlova

^{23.02.2007. u 15:54 - pre 208 meseci}

Da kazemo da sam reiso problem. Evo koda makroa ako nekog zanima evo sta sam radio...

Dakle, postoji glavni fajl (master.xls) i n sporednih u formatu Imei Prezimei.xls (i ide od 1 do n). Svi fajlovi su isti i sadrze 12 sheet-ova (jan, feb, mar...). U celiji d49 svakog od ovih fajlova nalazi suma za taj mesec.

Fajl master.xls sadrzi u Sheet1 tabelu u kojoj se u b koloni upisuju imena radnika (koja su ujedno i imena sporednih fajlova)
Tabela ima ovaj oblik:

1 ! B ! C! D! E ! F ! ...
2 ! Ime Prazime ! prazno ! jan ! prazno ! feb ! ...
3 ! ...

Makro u kolonama jan, feb... upisuje sume iz fajlova Imei Prezimei.xls (d49) za svakog radnika. U B3 (tu ide prvi zapis) se unese Ime i Prezime prvog radnika, u B4 drugog itd. Ovde je podeseno za 100 radnika.

Jedino sto se meni ne svidja je sto kada naidje na u B koloni na praznu celiju, ili na naziv fajla koj ne postoji izleti Error 400 i to je nacin na koji se program zaustavlja. Posto bas i nisam vican pravljenju makroa pokusaji da to premostim nisu bili uspesni.

JPeco, jos jednom puno hvala na pomoci.

Evo dole i koda

Code:

Sub Update()

' Prenosi vrednost celije d49 (sumu) iz tabele sa imenom korisnika u celiju d4 ovog sheeta
   Dim ref As Workbook
   Dim i As Integer


   For i = 3 To 102

   Set ref = Workbooks.Open(ThisWorkbook.Path & "\" & ThisWorkbook.Sheets(1).Range("b" & i) & ".xls")
   ThisWorkbook.Sheets(1).Range("d" & i).Value = ref.Sheets(1).Range("d49")
   ThisWorkbook.Sheets(1).Range("f" & i).Value = ref.Sheets(2).Range("d49")
   ThisWorkbook.Sheets(1).Range("h" & i).Value = ref.Sheets(3).Range("d49")
   ThisWorkbook.Sheets(1).Range("j" & i).Value = ref.Sheets(4).Range("d49")
   ThisWorkbook.Sheets(1).Range("l" & i).Value = ref.Sheets(5).Range("d49")
   ThisWorkbook.Sheets(1).Range("n" & i).Value = ref.Sheets(6).Range("d49")
   ThisWorkbook.Sheets(1).Range("p" & i).Value = ref.Sheets(7).Range("d49")
   ThisWorkbook.Sheets(1).Range("r" & i).Value = ref.Sheets(8).Range("d49")
   ThisWorkbook.Sheets(1).Range("t" & i).Value = ref.Sheets(9).Range("d49")
   ThisWorkbook.Sheets(1).Range("v" & i).Value = ref.Sheets(10).Range("d49")
   ThisWorkbook.Sheets(1).Range("x" & i).Value = ref.Sheets(11).Range("d49")
   ThisWorkbook.Sheets(1).Range("z" & i).Value = ref.Sheets(12).Range("d49")
   ref.Close (False)
   Next i

End Sub

Odgovor na temu

Brz_na_tastaturi
Svilajnac

Član broj: 63852
Poruke: 318
217.26.79.*

+1 Profil

Re: [Excel] Povezivanje ćelija iz različitih fajlova

^{23.02.2007. u 21:56 - pre 208 meseci}

Ako malo prepraviš kod rešićeš problem

Code:

Sub Update()

' Prenosi vrednost celije d49 (sumu) iz tabele sa imenom korisnika u celiju d4 ovog sheeta

   On Error GoTo GRESKA

   Dim ref As Workbook
   Dim i As Integer


   For i = 3 To 102

   Set ref = Workbooks.Open(ThisWorkbook.Path & "\" & ThisWorkbook.Sheets(1).Range("b" & i) & ".xls")
   ThisWorkbook.Sheets(1).Range("d" & i).Value = ref.Sheets(1).Range("d49")
   ThisWorkbook.Sheets(1).Range("f" & i).Value = ref.Sheets(2).Range("d49")
   ThisWorkbook.Sheets(1).Range("h" & i).Value = ref.Sheets(3).Range("d49")
   ThisWorkbook.Sheets(1).Range("j" & i).Value = ref.Sheets(4).Range("d49")
   ThisWorkbook.Sheets(1).Range("l" & i).Value = ref.Sheets(5).Range("d49")
   ThisWorkbook.Sheets(1).Range("n" & i).Value = ref.Sheets(6).Range("d49")
   ThisWorkbook.Sheets(1).Range("p" & i).Value = ref.Sheets(7).Range("d49")
   ThisWorkbook.Sheets(1).Range("r" & i).Value = ref.Sheets(8).Range("d49")
   ThisWorkbook.Sheets(1).Range("t" & i).Value = ref.Sheets(9).Range("d49")
   ThisWorkbook.Sheets(1).Range("v" & i).Value = ref.Sheets(10).Range("d49")
   ThisWorkbook.Sheets(1).Range("x" & i).Value = ref.Sheets(11).Range("d49")
   ThisWorkbook.Sheets(1).Range("z" & i).Value = ref.Sheets(12).Range("d49")
   ref.Close (False)
   Next i

Exit sub

GRESKA:
  If Err.Number = 400 Then
     Resume Next
  Else
     Msgbox Err.Number & " " & Err.Description
  End If

End Sub

Možda je tačno da je sve već rečeno, samo nisu svi sve čuli i razumeli ...

Odgovor na temu