[Excel] Povezivanje ćelija iz različitih fajlova

[Excel] Povezivanje ćelija iz različitih fajlova 13.02.2007. u 14:03 · pre 240 meseci #1

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.

Re: [Excel] Povezivanje ćelija iz različitih fajlova 13.02.2007. u 15:10 · pre 240 meseci #2

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")

Potpis

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

Re: [Excel] Povezivanje ćelija iz različitih fajlova 13.02.2007. u 15:58 · pre 240 meseci #3

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.

Re: [Excel] Povezivanje ćelija iz različitih fajlova 13.02.2007. u 16:10 · pre 240 meseci #4

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

Potpis

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

Re: [Excel] Povezivanje ćelija iz različitih fajlova 13.02.2007. u 22:22 · pre 240 meseci #5

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.

Re: [Excel] Povezivanje ćelija iz različitih fajlova 14.02.2007. u 09:07 · pre 239 meseci #6

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.

Potpis

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

Re: [Excel] Povezivanje ćelija iz različitih fajlova 21.02.2007. u 16:00 · pre 239 meseci #7

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...

Re: [Excel] Povezivanje ćelija iz različitih fajlova 21.02.2007. u 16:29 · pre 239 meseci #8

Š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.

Potpis

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

Re: [Excel] Povezivanje ćelija iz različitih fajlova 21.02.2007. u 16:46 · pre 239 meseci #9

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

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

Re: [Excel] Povezivanje ćelija iz različitih fajlova 21.02.2007. u 17:34 · pre 239 meseci #10

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š

Potpis

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

Re: [Excel] Povezivanje ćelija iz različitih fajlova 22.02.2007. u 14:09 · pre 239 meseci #11

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?

Re: [Excel] Povezivanje ćelija iz različitih fajlova 22.02.2007. u 14:41 · pre 239 meseci #12

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

Potpis

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

Re: [Excel] Povezivanje ćelija iz različitih fajlova 23.02.2007. u 16:54 · pre 239 meseci #13

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

Re: [Excel] Povezivanje ćelija iz različitih fajlova 23.02.2007. u 22:56 · pre 239 meseci #14

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

Potpis

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

Forum kao aplikacija na telefonu

Srodne teme