Yuav ua li cas rhuav cov txuas hauv Excel

Kev sib txuas lus yog qhov muaj txiaj ntsig zoo hauv Excel. Tom qab tag nrho, feem ntau cov neeg siv yuav tsum tau siv cov ntaub ntawv los ntawm lwm cov ntaub ntawv. Tab sis qee qhov xwm txheej, lawv tuaj yeem ua rau muaj kev phom sij ntau dua li qhov zoo. Tom qab tag nrho, piv txwv li, yog tias koj xa cov ntaub ntawv no los ntawm kev xa ntawv, cov kev sib txuas tsis ua haujlwm. Niaj hnub no peb yuav tham ntau ntxiv txog yuav ua li cas kom tsis txhob muaj teeb meem zoo li no.

Dab tsi yog kev sib raug zoo hauv Excel

Kev sib raug zoo hauv Excel feem ntau siv ua ke nrog cov haujlwm xws li VPRkom tau txais cov ntaub ntawv los ntawm lwm phau ntawv ua haujlwm. Nws tuaj yeem nqa daim ntawv txuas tshwj xeeb uas muaj qhov chaw nyob tsis yog ntawm tes, tab sis kuj yog phau ntawv uas cov ntaub ntawv nyob. Yog li ntawd, xws li ib qho txuas zoo li no: =VLOOKUP(A2;'[Sales 2018.xlsx]Report'!$A:$F;4;0). Los yog, rau kev sawv cev yooj yim, sawv cev rau qhov chaw nyob hauv daim ntawv hauv qab no: ='[Sales 2018.xlsx]Qhia'!$A1. Cia peb txheeb xyuas txhua qhov txuas txuas ntawm hom no:

  1. [Kev muag khoom 2018.xlsx]. Cov fragment no muaj qhov txuas mus rau cov ntaub ntawv uas koj xav tau cov ntaub ntawv. Nws tseem hu ua qhov chaw.
  2. duab. Peb siv lub npe hauv qab no, tab sis qhov no tsis yog lub npe uas yuav tsum yog. Qhov thaiv no muaj lub npe ntawm daim ntawv uas koj yuav tsum nrhiav cov ntaub ntawv.
  3. $A: $F thiab $A1 – qhov chaw nyob ntawm lub xov tooj ntawm tes lossis ntau yam uas muaj cov ntaub ntawv uas muaj nyob hauv daim ntawv no.

Qhov tseeb, cov txheej txheem ntawm kev tsim qhov txuas mus rau ib daim ntawv sab nraud yog hu ua kev sib txuas. Tom qab peb tau sau npe qhov chaw nyob ntawm tes muaj nyob rau hauv lwm cov ntaub ntawv, cov ntsiab lus ntawm "Cov ntaub ntawv" tab hloov. Namely, khawm "Hloov kev sib txuas" ua haujlwm, nrog kev pab los ntawm cov neeg siv tuaj yeem hloov kho cov kev sib txuas uas twb muaj lawm.

Lub ntsiab ntawm qhov teeb meem

Raws li txoj cai, tsis muaj teeb meem ntxiv tshwm sim txhawm rau siv cov kev sib txuas. Txawm hais tias muaj xwm txheej tshwm sim hauv cov hlwb hloov, ces txhua qhov txuas tau raug hloov kho tshiab. Tab sis yog tias koj twb rename phau ntawv ua haujlwm nws tus kheej lossis txav mus rau qhov chaw sib txawv, Excel yuav tsis muaj zog. Yog li ntawd, nws tsim cov lus hauv qab no.

Yuav ua li cas rhuav cov txuas hauv Excel

Ntawm no, tus neeg siv muaj ob txoj hauv kev rau kev ua li cas hauv qhov xwm txheej no. Nws tuaj yeem nyem "Txuas ntxiv" thiab tom qab ntawd cov kev hloov pauv yuav tsis hloov kho, lossis nws tuaj yeem nyem rau ntawm "Change Associations" khawm, uas nws tuaj yeem hloov kho manually. Tom qab peb nyem lub pob no, lub qhov rai ntxiv yuav tshwm sim uas nws yuav muaj peev xwm hloov cov kev sib txuas, qhia qhov twg cov ntaub ntawv raug nyob rau lub sijhawm thiab nws hu ua dab tsi.

Yuav ua li cas rhuav cov txuas hauv Excel

Tsis tas li ntawd, koj tuaj yeem hloov kho cov kev sib txuas los ntawm cov khawm sib txuas nyob rau ntawm "Cov ntaub ntawv" tab. Tus neeg siv kuj tuaj yeem pom tias qhov kev sib txuas tau tawg los ntawm #LINK qhov yuam kev, uas tshwm sim thaum Excel tsis tuaj yeem nkag mus rau cov ntaub ntawv nyob ntawm qhov chaw tshwj xeeb vim qhov tseeb tias qhov chaw nyob nws tus kheej tsis raug.

Yuav ua li cas unlink hauv Excel

Ib txoj hauv kev yooj yim los daws qhov teeb meem tau piav qhia saum toj no yog tias koj tsis tuaj yeem hloov kho qhov chaw ntawm cov ntaub ntawv txuas koj tus kheej yog rho tawm qhov txuas nws tus kheej. Qhov no tshwj xeeb tshaj yog yooj yim ua yog tias cov ntaub ntawv tsuas muaj ib qhov txuas xwb. Txhawm rau ua qhov no, koj yuav tsum ua cov kauj ruam hauv qab no:

  1. Qhib "Cov ntaub ntawv" zaub mov.
  2. Peb pom ntu "Kev Sib Txuas", thiab muaj - qhov kev xaiv "Hloov kev sib txuas".
  3. Tom qab ntawd, nyem rau ntawm "Unlink".

Yog tias koj xav xa phau ntawv no mus rau lwm tus neeg, nws raug nquahu kom koj ua ua ntej. Tom qab tag nrho, tom qab tshem tawm cov kev sib txuas, tag nrho cov txiaj ntsig uas muaj nyob rau hauv lwm daim ntawv yuav cia li muab tso rau hauv cov ntaub ntawv, siv hauv cov qauv, thiab hloov chaw nyob ntawm tes, cov ntaub ntawv hauv cov xov tooj sib txuas yuav tsuas yog hloov mus rau qhov tseem ceeb. .

Yuav ua li cas unlink tag nrho cov phau ntawv

Tab sis yog tias tus naj npawb ntawm cov kev sib txuas dhau los dhau lawm, kev tshem tawm manually tuaj yeem siv sijhawm ntev. Txhawm rau daws qhov teeb meem no hauv ib qho mus, koj tuaj yeem siv cov macro tshwj xeeb. Nws yog nyob rau hauv VBA-Excel addon. Koj yuav tsum qhib nws thiab mus rau lub tab ntawm tib lub npe. Yuav muaj "Txuas" ntu, uas peb yuav tsum tau nyem rau ntawm "Txhua qhov txuas" khawm.

Yuav ua li cas rhuav cov txuas hauv Excel

VBA code

Yog tias nws tsis tuaj yeem qhib qhov add-on no, koj tuaj yeem tsim macro koj tus kheej. Txhawm rau ua qhov no, qhib Visual Basic editor los ntawm nias lub Alt + F11 yuam sij, thiab sau cov kab hauv qab no hauv cov lej nkag.

Sub UnlinkWorkBooks()

    Dim WbLinks

    Dim thiab As Long

    Xaiv Case MsgBox("Txhua cov ntaub ntawv xa mus rau lwm phau ntawv yuav raug tshem tawm ntawm cov ntaub ntawv no, thiab cov qauv xa mus rau lwm cov phau ntawv yuav raug hloov nrog qhov muaj nuj nqis." & vbCrLf & "Koj puas paub tseeb tias koj xav txuas ntxiv?", 36, "Unlink?" )

    Case 7' Nr

        Tawm Sub

    Xaus Xaiv

    WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    Yog Tsis Yog Empty(WbLinks) Ces

        For i = 1 To UBound(WbLinks)

            ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLinks

        Tom ntej no

    lwm tus

        MsgBox “Tsis muaj kev txuas mus rau lwm phau ntawv hauv cov ntaub ntawv no.”, 64, “Txuas mus rau lwm phau ntawv”

    Xaus Yog

End Sub

Yuav ua li cas tawg ties tsuas yog nyob rau hauv lub xaiv ntau yam

Txij lub sij hawm, tus naj npawb ntawm cov kev sib txuas yog loj heev, thiab cov neeg siv ntshai tias tom qab rho tawm ib qho ntawm lawv, nws yuav tsis tuaj yeem rov qab txhua yam rov qab yog tias qee qhov yog superfluous. Tab sis qhov no yog ib qho teeb meem uas yooj yim zam. Txhawm rau ua qhov no, koj yuav tsum xaiv qhov ntau yam uas yuav rho tawm cov txuas, thiab tom qab ntawd rho tawm lawv. Txhawm rau ua qhov no, koj yuav tsum ua cov haujlwm hauv qab no:

  1. Xaiv cov ntaub ntawv uas yuav tsum tau hloov kho.
  2. Nruab VBA-Excel add-on, thiab tom qab ntawd mus rau qhov tsim nyog tab.
  3. Tom ntej no, peb pom cov ntawv qhia zaub mov "Txuas" thiab nyem rau ntawm "Luam kev txuas hauv cov kab xaiv" khawm.

Yuav ua li cas rhuav cov txuas hauv Excel

Tom qab ntawd, tag nrho cov kev sib txuas hauv cov txheej txheem xaiv yuav raug muab tshem tawm.

Yuav ua li cas yog tias txoj hlua khi tsis tawg

Tag nrho cov saum toj no suab zoo, tab sis nyob rau hauv kev xyaum muaj ib txwm ib co nuances. Piv txwv li, tej zaum yuav muaj qhov xwm txheej uas tsis muaj kev sib raug zoo. Nyob rau hauv rooj plaub no, lub dialog box tseem tshwm sim hais tias nws tsis tuaj yeem hloov kho cov kev sib txuas. Yuav ua li cas hauv qhov xwm txheej no?

  1. Ua ntej, koj yuav tsum xyuas seb puas muaj cov ntaub ntawv muaj nyob hauv cov npe npe. Txhawm rau ua qhov no, nias qhov tseem ceeb ua ke Ctrl + F3 lossis qhib "Formula" tab - "Npe Manager". Yog tias cov ntaub ntawv npe puv, ces koj tsuas yog yuav tsum hloov kho lossis tshem nws tag nrho. Ua ntej rho npe cov npe, koj yuav tsum luam cov ntaub ntawv mus rau lwm qhov chaw kom koj tuaj yeem rov qab mus rau qhov qub version yog tias cov kauj ruam tsis raug.
  2. Yog tias koj tsis tuaj yeem daws qhov teeb meem los ntawm kev tshem tawm cov npe, koj tuaj yeem tshawb xyuas cov kev cai formatting. Cov xovtooj ntawm lwm lub rooj tuaj yeem raug xa mus rau hauv cov cai tswj hwm kev cai. Txhawm rau ua qhov no, nrhiav cov khoom sib txuas ntawm "Tsev" tab, thiab tom qab ntawd nyem rau ntawm "Cov Ntaub Ntawv Tswj" khawm. Yuav ua li cas rhuav cov txuas hauv Excel

    Feem ntau, Excel tsis muab koj lub peev xwm los muab qhov chaw nyob ntawm lwm phau ntawv ua haujlwm nyob rau hauv kev cai formatting, tab sis koj ua yog tias koj xa mus rau cov npe uas muaj npe nrog rau lwm cov ntaub ntawv. Feem ntau, txawm tias tom qab qhov txuas raug tshem tawm, qhov txuas tseem nyob. Tsis muaj teeb meem hauv kev tshem tawm qhov txuas no, vim tias qhov txuas yog qhov tseeb tsis ua haujlwm. Yog li ntawd, tsis muaj dab tsi phem yuav tshwm sim yog tias koj tshem nws.

Koj tseem tuaj yeem siv "Cov Ntaub Ntawv Tshawb Xyuas" ua haujlwm kom paub seb puas muaj cov kev txuas tsis tsim nyog. Cov kev sib txuas feem ntau nyob twj ywm yog tias siv "Tsev" hom ntaub ntawv siv tau. Tab sis yuav ua li cas yog tias muaj ntau lub hlwb? Puas yog nws tsim nyog los xyuas txhua tus ntawm lawv ib ntus? Tau kawg tsis. Tom qab tag nrho, nws yuav siv sij hawm ntev heev. Yog li ntawd, koj yuav tsum siv tus lej tshwj xeeb kom txuag tau nws.

Option Explicit

'————————————————————————————————————

' Sau: The_Prist (Shcherbakov Dmitry)

' Kev txhim kho kev tshaj lij ntawm daim ntawv thov rau MS Office ntawm txhua qhov nyuaj

' Ua kev cob qhia ntawm MS Excel

' https://www.excel-vba.ru

' [email tiv thaiv]

'WebMoney—R298726502453; Yandex.Money - 41001332272872

' Lub hom phiaj:

'————————————————————————————————————

Sub FindErrLink()

    'peb yuav tsum tau saib hauv Cov Ntaub Ntawv -Hloov cov txuas txuas mus rau cov ntaub ntawv qhov chaw

    'thiab muab cov lus tseem ceeb ntawm no ua tus ntawv me (ib feem ntawm cov ntaub ntawv npe)

    'asterisk tsuas yog hloov ib tus lej ntawm cov cim yog li koj tsis tas yuav txhawj xeeb txog lub npe tiag

    Const sToFndLink$ = "* muag 2018*"

    Dim rr As Range, rc As Range, rres As Range, s$

    ' txhais tag nrho cov cell nrog cov ntaub ntawv validation

    On Yuam kev Resume Tom ntej

    Teem rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)

    Yog tsis muaj dab tsi ces

        MsgBox "Tsis muaj lub hlwb nrog cov ntaub ntawv siv tau ntawm daim ntawv nquag", vbInformation, "www.excel-vba.ru"

        Tawm Sub

    Xaus Yog

    Ntawm qhov yuam kev GoTo 0

    'xyuas txhua lub xov tooj rau kev sib txuas

    Rau Txhua rc Hauv rr

        ' tsuas yog nyob rau hauv rooj plaub, peb hla qhov yuam kev - qhov no kuj tuaj yeem tshwm sim

        'tab sis peb cov kev sib txuas yuav tsum tsis muaj lawv thiab lawv yuav pom tseeb

        s = «»

        On Yuam kev Resume Tom ntej

        s = rc.Validation.Formula1

        Ntawm qhov yuam kev GoTo 0

        'pom - peb sau txhua yam hauv ib qho kev sib cais

        Yog LCase(s) nyiam sToFndLink Ces

            Yog rres Tsis muaj dab tsi ces

                Set rres = rc

            lwm tus

                Set rres = Union(rc, rres)

            Xaus Yog

        Xaus Yog

    Tom ntej no

    'yog tias muaj kev sib txuas, xaiv txhua lub cell nrog cov ntaub ntawv kuaj xyuas

    Yog Tsis rres Tsis muaj dab tsi ces

        rres.Xaiv

' rres.Interior.Color = vbRed 'yog tias koj xav ua kom pom tseeb nrog xim

    Xaus Yog

End Sub

Nws yog ib qho tsim nyog los ua tus qauv module hauv macro editor, thiab tom qab ntawd ntxig cov ntawv no rau hauv. Tom qab ntawd, hu rau lub qhov rais macro siv qhov tseem ceeb ua ke Alt + F8, thiab tom qab ntawd xaiv peb cov macro thiab nyem rau ntawm "Khiav" khawm. Muaj ob peb yam uas yuav tsum nco ntsoov thaum siv cov cai no:

  1. Ua ntej koj tshawb nrhiav qhov txuas uas tsis cuam tshuam ntxiv lawm, koj yuav tsum xub txiav txim siab seb qhov txuas uas nws tau tsim zoo li cas. Txhawm rau ua qhov no, mus rau "Cov ntaub ntawv" ntawv qhia zaub mov thiab nrhiav "Hloov Txuas" cov khoom nyob ntawd. Tom qab ntawd, koj yuav tsum saib cov npe ntawm cov ntaub ntawv, thiab qhia nws hauv cov lus qhia. Piv txwv li, zoo li no: Const sToFndLink$ = "* muag 2018*"
  2. Nws tuaj yeem sau lub npe tsis puv, tab sis tsuas yog hloov cov cim tsis tsim nyog nrog lub hnub qub. Thiab nyob rau hauv quotes, sau cov ntaub ntawv npe nyob rau hauv cov ntawv me me. Hauv qhov no, Excel yuav pom txhua cov ntaub ntawv uas muaj cov hlua zoo li no thaum kawg.
  3. Cov cai no tsuas yog tuaj yeem tshawb xyuas cov kev txuas hauv daim ntawv uas tam sim no ua haujlwm.
  4. Nrog rau qhov macro no, koj tsuas tuaj yeem xaiv cov hlwb uas nws tau pom. Koj yuav tsum rho tawm txhua yam manually. Qhov no yog qhov ntxiv, vim tias koj tuaj yeem kuaj xyuas txhua yam ntxiv.
  5. Koj tuaj yeem ua kom cov hlwb tseem ceeb hauv cov xim tshwj xeeb. Ua li no, tshem lub apostrophe ua ntej kab no. rres.Interior.Color = vbRed

Feem ntau, tom qab koj ua tiav cov kauj ruam tau piav qhia hauv cov lus qhia saum toj no, yuav tsum tsis muaj kev sib txuas ntxiv lawm. Tab sis yog tias muaj qee yam ntawm lawv hauv cov ntaub ntawv thiab koj tsis tuaj yeem tshem tawm lawv rau ib qho lossis lwm qhov (ib qho piv txwv yog kev ruaj ntseg ntawm cov ntaub ntawv hauv ib daim ntawv), ces koj tuaj yeem siv ntau qhov sib txawv ntawm kev ua. Cov lus qhia no tsuas siv tau rau versions 2007 thiab siab dua.

  1. Peb tsim ib daim ntawv luam theej ntawm daim ntawv.
  2. Qhib cov ntaub ntawv no siv lub archiver. Koj tuaj yeem siv txhua yam uas txhawb nqa ZIP hom, tab sis WinRar kuj tseem yuav ua haujlwm, nrog rau qhov tsim rau Windows.
  3. Nyob rau hauv lub archive uas tshwm sim, koj yuav tsum nrhiav xl nplaub tshev, thiab ces qhib externalLinks.
  4. Cov ntawv tais ceev tseg no muaj tag nrho cov kev txuas sab nraud, txhua qhov sib raug rau cov ntaub ntawv ntawm daim ntawv externalLink1.xml. Tag nrho cov ntawm lawv tsuas yog tus lej, thiab yog li ntawd tus neeg siv tsis muaj lub sijhawm los nkag siab tias qhov kev sib txuas zoo li cas. Txhawm rau nkag siab zoo li cas kev sib txuas, koj yuav tsum qhib _rels nplaub tshev, thiab saib nws nyob ntawd.
  5. Tom qab ntawd, peb tshem tawm tag nrho lossis cov txuas tshwj xeeb, raws li qhov peb kawm hauv cov ntaub ntawv externalLinkX.xml.rels.
  6. Tom qab ntawd, peb qhib peb cov ntaub ntawv siv Excel. Yuav muaj cov ntaub ntawv hais txog qhov yuam kev xws li "Kev ua yuam kev hauv ib feem ntawm cov ntsiab lus hauv Phau Ntawv." Peb muab kev tso cai. Tom qab ntawd, lwm lub dialog yuav tshwm sim. Peb kaw nws.

Tom qab ntawd, tag nrho cov txuas yuav tsum tau muab tshem tawm.

Sau ntawv cia Ncua