Telefonszámunk: 1-472-0679

Excelre hivatkozó Word körlevél abszolút hivatkozásának relatívvá tétele

Kezdőlap Fórumok Excel programozás Excelre hivatkozó Word körlevél abszolút hivatkozásának relatívvá tétele

Ennek a témakörnek tartalma 16 hozzászólás, 4 résztvevő. Utolsó frissítés:  Sicamber 3 hete, 3 napja telt el a bejegyzés óta.

15 bejegyzés megtekintése - 1-15 / 17
  • Szerző
    Bejegyzés
  • #4560

    Sicamber
    Résztvevő

    Sziasztok!

    Inkább Word-probléma mint Excel, de hátha mégis van itt helye a témának…
    Hivatali környezetben kell megoldani többféle, azonos adatokat használó űrlap kezelését.
    Pl. Gipsz Jakabnak küldhetünk meghívót is, értesítést is, és kérdőívet is. Persze nemcsak iratmintából, de címzettből is sok van.

    Ez a Word eszközeivel meg is oldható úgy, hogy az összes iratminta egy excel fájlra hivatkozik amiben benne van az összes címzett. Egy iratmintát egyszerre csak egy címzettnek küldünk ki, de az excel címzettjei szűrhetők a Word-ből a levelezés menü “címzettlista szerkesztése” parancsával.

    Eddig rendben is lenne a dolog, csakhogy:
    Több hivatali dolgozó használná az iratmintákat saját gépén külön külön, ráadásul néha az iratmintákat is frissíteni kell. Ilyenkor gond az, hogy a Word abszolút módon hivatkozik az Excel fájlra, tehát nem másolhatók és nem áthelyezhetők az iratminták.
    Egy iratminta hivatkozása könnyen átállítható a Word-ből, de sok iratmintánál sok számítógépen ez elég sok macera.

    VBA makrós megoldást találtam rá a az alábbi címen, de talán verziós problémák miatt nálam nem működik. Az Office-ok 2007-esnél újabbak.
    https://www.office-forums.com/threads/mail-merge-relative-reference-to-data-source.1879546/

    Segítségeteket előre is köszönöm!
    János

    #4563

    Titok
    Résztvevő

    Saját belső hálózatot kell használni, abba közösen elérhető helyre tenni az xls-t, meg a sablont is. Így biztosítva lesz, hogy a legfrissebbet használod. Lementeni le lehet a kész körlevelet is. De szerintem felhőbe is működik.

    #4564

    Sicamber
    Résztvevő

    Köszi a választ!

    A probléma az, hogy az excel fájlba sok adatot kell kitölteni, és 10-15 dolgozó egyszerre nem fogja tudni töltögetni az excelt mert várniuk kellene egymásra.
    Legalább az excelből mindenkinek külön példányok kellenének.
    Mondjuk az lehet, hogy a külön excel fájlok lesznek a végén valahogy összefűzve egy közös excellé. -Azon még nem gondolkodtam, hogy az megoldható-e..

    #4565

    Titok
    Résztvevő

    Szenvedtünk mi is ezzel, és igazi megoldás nem volt. Mivel egyszeri feladat volt, csak több embernek kellett nyomtatni, íyg közösen elérhető tárhelyről megoldódott.
    Az excelek összefűzése makróval elég egyszerűen megoldható. Még a munkafüzet neveket is ki lehet olvastatni, és összemásolás után akár ki is lehet üríteni a munkalapokat, esetleg a fájlokat törölni KILL-el.
    Másik ötlet: muszály word-öt használni? Nem lehet Excelbe létrehozni a sablonokat és onnan nyomtatni amit kell? (Ha nem kell túl sok spéci formázgatás, azért excelbe is lehet sablonszerű kinézeteket generálni!

    #4566

    Sicamber
    Résztvevő

    Sajnos itt az irodában mindenki word-ben dolgozik, és nehéz lenne ezen változtatni.
    Viszont a makrós-összefűzős megoldás szimpatikus. Azt hiszem, megpróbálom azt, és ha nagyon elakadnék, akkor lehet hogy kérdezek még.

    Köszönöm a segítséged!

    #4567

    Titok
    Résztvevő

    Ez adott könyvtárból minden fájlt összemásol:
    Indulásbnak elég jó lesz 🙂
    wb = ActiveWorkbook.Path

    Dim FSO As Object
    Dim Folder As Object
    Dim File As Object
    Dim i As Integer
    Dim MyDate
    Dim LastRow As Long
    Dim kirdatum As Date
    Dim kirdatum2 As String
    Dim kd As String

    Set FSO = CreateObject(“Scripting.FileSystemObject”)
    Set Folder = FSO.GetFolder(wb)
    MyDate = Date
    Munka2.Activate
    Sheets(“mappa”).Range(“A2:A100000”).Delete

    i = 1
    For Each File In Folder.Files
    Cells(i + 1, 1) = File.Name
    i = i + 1
    Next File

    Munka3.Activate

    idoszak = Cells(11, 3).Value
    datums = Cells(12, 3).Value
    datum2 = Cells(13, 3).Value
    filter_kir_adat = Cells(14, 3).Value

    ‘Sheets(idoszak).Range(“A2:E100000”).Delete

    For i = 2 To 200

    Munka2.Activate

    fns = “A” & i
    kivege = i
    fn = Range(fns).Value
    If fn = “” Then GoTo kanyec

    kate = Right(fn, 9)
    filter_kir = Left(fn, 3)
    eleje = Left(fn, 1)

    Sheets(idoszak).Activate

    If eleje = “0” And kate = “2018.xlsx” Then

    Sheets(idoszak).Activate
    sor = Application.Match(filter_kir, Range(“B1:B1000”), 0)

    wbn = wb & “\” & fn
    Workbooks.Open Filename:=wbn
    cel = “pe0.xlsm”
    Workbooks(fn).Activate

    Sheets(idoszak).Activate

    If WorksheetFunction.CountA(Cells) > 0 Then
    LastRow = Cells.Find(What:=”*”, After:=[A1], _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    End If

    For g = 2 To LastRow

    Workbooks(fn).Sheets(idoszak).Activate

    kirdatum = Range(“B” & g).Value
    kd = Format(kirdatum, “YYYY.MM.DD”)

    Workbooks(cel).Sheets(idoszak).Activate

    oszlop = Application.Match(kd, Range(“A1:BE1”), 0)

    Workbooks(cel).Sheets(idoszak).Cells(sor, oszlop).Value = Workbooks(fn).Sheets(idoszak).Range(“G” & g).Value
    Workbooks(cel).Sheets(idoszak).Cells(sor, oszlop + 1).Value = Workbooks(fn).Sheets(idoszak).Range(“I” & g).Value

    van = Workbooks(fn).Sheets(idoszak).Range(“I” & g).Value
    If van <> emty Then

    Cells(sor, oszlop).Interior.ColorIndex = 6
    Cells(sor, oszlop + 1).Interior.ColorIndex = 6

    Else

    Cells(sor, oszlop).Interior.ColorIndex = 8
    Cells(sor, oszlop + 1).Interior.ColorIndex = 8

    End If

    Next g

    End If

    If eleje = “0” Then

    Workbooks(fn).Close

    Else
    End If

    Next i

    #4569

    delila
    Résztvevő

    Egy másik módszer az adott könyvtárban lévő Excel fájlok tartalmának az összemásolására:

    Sub Osszemasolas()
        Dim utvonal As String, FN, lap As Integer, WSO As Worksheet, usor As Long
        
        Set WSO = ActiveWorkbook.Sheets("Munka1")   'Aktív füzet Munka1 lapja, ide gyűjtjük az adatokat
        utvonal = "D:\Mappa\"   'Útvonal, ahonnan megnyitjuk másolásra a füzeteket
        FN = Dir(utvonal & "*.xls*")
        
        Do While FN <> ""
            Workbooks.Open utvonal & FN 'A mappában lévő fájlok megnyitása
            
            'Végig megyünk a megnyitott fájl lapjain
            For lap = 1 To Sheets.Count
                usor = WSO.Range("A" & Rows.Count).End(xlUp).Row + 1    'A gyűjtő lap első üres sora
                
                'Másoljuk és beillesztjük a lapokon lévő adatokat, címsor nélkül (offset)
                'Ha feltehetően vannak üres sorok is, a CurrentRegion helyett mást kell használnunk.
                Sheets(lap).Range("A1").CurrentRegion.Offset(1).Copy WSO.Range("A" & usor)
            Next
            
            ActiveWindow.Close False 'A megnyitott fájl mentés nélküli bezárása
            FN = Dir()
        Loop
    End Sub
    #4570

    Sicamber
    Résztvevő

    Köszönöm a makrókat!

    A problémám az, hogy ha a gyűjtőfájlban van a makró, akkor a gyűjtőfájlt is mindig meg kell nyitni és lefuttatni a makrót ha új adatot írunk bele.

    Ezért a fentiekből összeollóztam egy olyat, amit az egyéni adatfájlokba kell tenni, és azok írják bele az adatokat a gyűjtőfájlba mentéskor. Úgy csináltam a kódott, hogy ugyanabban a mappában kell legyen a gyűjtő és az összes egyéni fájl is, illetve hogy csak a gyűjtő fájl megfelelő füleibe tudja az egyéni fájl bemásolni magát. Ezzel elkerülhető, hogy egy másolat pluszban írjon be adatokat. (persze a gyűjtő fájlban így összegezni kell majd a füleket, de ez már megoldható képletekkel.

    A problémám az alábbi makróval az, hogy a hibakezelés nem működik: az nem akasztja ki, ha már meg van nyitva a gyűjtőfájl, de a gyűjtő fájl bezárása meg nálam hibát okoz, így akkor is hibát jelez, ha sikerült az átmásolás.

    Private Sub Workbook_AfterSave(ByVal Success As Boolean)

    Dim utvonal As String, FN, lap As Integer, WSO As Worksheet, WB As Workbook, usor As Long

    On Error GoTo Hibauzenet
    Set WB = ActiveWorkbook ‘Aktív másolandó egyéni fájl, innen gyűjtjük az adatokat

    utvonal = ActiveWorkbook.Path & “\” ‘Útvonal, ami az összes excel fájlt tartalmazza
    FN = Dir(utvonal & “osszes.xls*”) ‘A gyűjtőfájl neve
    Workbooks.Open utvonal & FN ‘A gyüjtőfájl megnyitása

    ‘Végig megyünk a másolandó egyéni fájl lapjain
    For lap = 1 To WB.Sheets.Count
    Set WSO = ActiveWorkbook.Sheets(WB.Name & “_” & WB.Sheets(lap).Name) ‘A gyűjtő füzet előre elnevezett lapja, ide másoljuk 1 füzet 1 lapját
    WSO.Range(“A2:BA100000”).Delete ‘A gyűjtő lap törlése
    ‘Másoljuk és beillesztjük a lapon lévő adatokat, címsor nélkül (offset)
    ‘Ha feltehetően vannak üres sorok is, a CurrentRegion helyett mást kell használnunk.
    WB.Sheets(lap).Range(“A1”).CurrentRegion.Offset(1).Copy WSO.Range(“A2”)
    Next

    ActiveWorkbook.Close True ‘A megnyitott gyűjtőfájl bezárása mentéssel

    Hibauzenet:
    MsgBox “Az adatmásolás nem sikerült, lehet hogy épp meg volt nyitva a gyűjtőfájl. Próbáld meg újra!”

    End Sub

    #4571

    delila
    Résztvevő

    Szia!

    Ezzel az a baj, hogy a gyűjtő füzet lapjain mindig csak az utoljára bemásolt füzet adatai lesznek meg. A meglévő adatokat a

    WSO.Range("A2:BA100000").Delete

    sor törli.
    Másoláskor be kellene vinni egy üres oszlopba annak a fájlnak a nevét, ahonnan másolsz, és az új másolás előtt csak ezeket a sorokat kellene törölni. A tartományt megkeresheted a Match függvénnyel.

    #4572

    Sicamber
    Résztvevő

    Szia!

    A törlés szándékos, mert úgy gondoltam, hogy minden egyedi excel az “osszes.xls” fájl egy külön lapjára másolja az adatait. Ezeket a füleket később egy 0. lapon összesíteném képletekkel.

    Így az osszes.xls fájlnak (egyedi excelek száma X egyedi excelek lapszáma) darab lapja lenne, mind előre elnevezve “egyediExcelNeve.xlsm_egyediExcelLapneve” névre. Ha egy mappában vannak(a makró ezt meg is követeli) akkor csak az adott nevű és lapnevű excelek tudnak adatokat bemásolni, az esetleges biztonsági mentések, stb.. nem, mert nem találnak megfelelő lapot maguknak az összesít.xls fájlban.

    A gondom a hibakezeléssel van, mert hibára kellene futtatnom a prg-t, ha épp egyszerre akarnak az összes.xls-be feltölteni adatot (tehát már meg van nyitva az osszes.xls amikor fut egy többedik egyedi excel makrója. – Ez egyelőre nem sikerült. Ráadásul az utolsó sor, a “ActiveWorkbook.Close True ” viszont hibát okoz nálam annak ellenére hogy bezárja és menti az osszes.xls fájjlt. – ezt talán azért csinálja, mert felugrik bezáráskor egy biztonsági értesítés – lehet, hogy az alert-eket le kell majd tiltanom…

    #4575

    delila
    Résztvevő

    Pedig megírtam, hogy az osszes.xlsx lapjairól csak azokat a sorokat törölje, amelyik füzetnek amelyik lapján lévő adatokat másolod éppen be.

    A hibaüzenet fölött ki kell lépned a makróból : Exit Sub

    #4576

    Sicamber
    Résztvevő

    “Pedig megírtam…”
    Igen, most már értem. Azt szeretném, ha egy lapra csak egy munkafüzetből másolnám be a frissített adatokat, és egybe a teljeset, nem csak az újakat. Így a törléseket, módosításokat is át lehet vezetni az osszes.xls fájlba.
    A gyűjtő füzetből csak azokat a lapokat törli amelyeket az éppen a másolást végző egyéni excel használ.
    Az még nincs megoldva, hogy mi van, ha az egyéni excel neve jó, de a lapjai között vannak olyanok is amik nem tudnak feltöltődni, mert most az első ilyennél hibaüzenettel kilép.

    Köszi a hibaüzenetes javítást, este megpróbálom majd rendbeszedni a prg-t, meg jobban bekommentezni.
    Mégegyszer… Köszi!

    #4577

    delila
    Résztvevő

    Meg kell nézni, van-e x nevű lap az osszes.xlsx-ben. Ha nics, létre kell hozni a Sheets.Add utasítással.

    `On Error Resume Next
    Set a = Sheets(“x”)
    If Err.Number <> 0 Then
    MsgBox “Nincs ilyen”
    End If
    On Error Goto 0

    #4583

    horvimi
    Kulcsmester

    Próbáltad már azt, hogy ha a címzettek Access-ben vannak, és a címlistákat lekérdezésekkel készíted elő?
    A Wordből lehetne az Access lekérdezésekhez kapcsolni a körlevelet (iratmintát).
    Az Access elvileg alkalmas konkurens használatra.
    Így az iratminták is lehetnek a hálózaton, és lehet őket központilag karbantartani, és a címlista is lehet ugyanott, és egy helyen tartod karban.

    Nem próbáltam több user-rel, de elvileg működnie kell.

    Imre

    #4649

    Sicamber
    Résztvevő

    Sziasztok!

    Hosszú próbálkozások után végül sikerült az eredeti koncepciót megcsinálni, azaz a wordbe írni olyan makrót, ami relatívan adja meg a körleveles adatforrás kódját
    (két helyen problémás volt az elérési útvonal és a munkalapnév változóként történő beillesztése, de végül sikerült valami ilyesmivel: “& változó &”
    Office verzióktól függően lehet, hogy valakinek újra fel kell vennie makróként a táblázat csatolását(levelezés\címzettek kiválasztása\meglévő lista használata), és azt módosítani.(nekem office2013 és office365-nél működik)
    A word makróban az sql-lekérdezést(strSql) kiegészítettem a where [o1] <> 0 résszel, hogy excelből szűrhető legyen a word-ben megjelenő táblázat.(így most ami excelben el van rejtve az a word címzettlistában sem jelenik meg) – aki próbálta már word-ben szűrni a címzett-listát (levelezés\címzettlista szerkesztése), az érti e fáradozásom okát..
    (Az adat1 és adat2 excelekben ehhez kell az A oszlopban lévő trükkös képlet, amitől az elrejtett sorok első cellái 0 értékűek lesznek. Az o1 oszlopnevet viszont így csak akkor változtassuk meg, ha a word makrójában az strSql változó szövegében is javítjuk a[o1] kifejezést, különben nem tudja csatlakoztatni a word-makró az excelt. Szintén a Word-makró sql képlete miatt az A oszlop csak számokat tartalmazhat)

    Rendbe tettem az összesítő makrót is, amik az adat1 és adat2 excelekben vannak, illetve az osszes.xls-ben képlettel összesítettem a füleket. (a makróban érték szerinti beillesztést használtam, hogy a szűrést jelző A oszlopok eredménye átkerüljön az osszes.xls-be is)
    Most a következőképpen működik a négy fájl:
    adat1 és adat2 átküldi az adatait (értékbeillesztéssel) makróval az osszes.xls megfelelő lapjaira.(A makró a fájl-mentési esemény után fut le)
    Az osszes.xls az osszesit lapon összesíti a lapokat képlettel.
    A word az osszesit.xls-re hivatkozik a makróval relatívan(tehát mindig saját mappájában keresi az excelt).

    Ennek így nincs sok értelme, mert vagy az adat1-re kéne hivatkoznia a word-nek relatívan, vagy az osszes.xls-re abszolút hivatkozással, ahogy a fentebbi bejegyzésekben is akartuk.
    A word makrója az elején a változókkal mindkét megoldásra könnyen átalakítható.
    Az egy excelre hivatkozó több word-körlevél valószínűleg problémákat okoz még úgy is, ha Ctrl+í-be itt betettem a kapcsolat-helyreállító makrót.
    Ha relativan hivatkozunk közvetlenül adat1-re(ez az ajánlott!), akkor érdemes adat1 makróit mellőzni, és .xls formátumba menteni, mert a word makrója csak akkor tud nyitott excel-t kezelni, ha az .xls formátumban van.- ez utóbbi pedig erős kényelmi szempont.

    A csatolt fájlokat egy közös mappába kell tenni.
    A mappa elérési útvonala ne legyen 35-40 karakternél több, mert akkor nem fog működni a word makrója, ami megnyitáskor(és Ctrl+í billentyűkombinációra) csatlakoztatja az osszes.xls fájlt a körlevél adatforrásaként.
    A word-be tettem két hasznos mezőkódos példát, amik másolhatók.

    Adatforrásként nekem az excel szimpatikusabb az access-nél már azért is, mert a word mezőkódjainak lehetőségei elég korlátozottak, így nem lehet olyan változtatásokat(pl ragozások követése) megcsinálni benne amit excelben viszont lehet.
    Az Accessbe lehet egyszerre több helyről bevinni adatokat, de tapasztalatom szerint a word-körlevél ugyanúgy nem szereti a nyitott access-t mint a nyitott .xlsx vagy .xlsm fájlokat, bár lehet, hogy access-ből is a régi fájlformátummal kellett volna próbálkoznom.

    Köszönöm mindenkinek a segítséget, és remélem hogy így egy helyre összegyűjtve a témába vágó megoldásokat többen is tudnak meríteni belőle.

    Attachments:
    You must be logged in to view attached files.
15 bejegyzés megtekintése - 1-15 / 17

Be kell jelentkezni a hozzászóláshoz.