VBA Code: 엑셀 파일 연결 끊기가 안될 때 문제 해결

지난번 포스팅에서 엑셀 파일을 새롭게 생성하지 않고 여러사람이 작업을 하고, 또 작업본을 다른 이름으로 저장해서 사용할 때 시트복사시 이름충돌에 대한 문제를 해소할 수 있는 VBA Code 를 소개해 드렸는데요, 오늘은 엑셀파일 링크가 깨져서 데이터 연결을 끊어도 끊어지지 않는 문제를 해결할 VBA Code를 소개해 드립니다.

 

경험상 해당 문제는 회사 파일 서버에 위치한 엑셀 파일을 링크하거나, 웹 드라이브에 있는 엑셀 파일에 링크가 걸려있는 파일에서 문제(ex. 서버의 특정 디렉토리에 파일이 저장되어 있다가 해당 파일이 다른 곳으로 옮겨진 경우)가 되는데요, 아래에 소개해 드릴 코드로 간단히 해결할 수 있습니다.

 

먼저 파일 연결에 문제가 있는 파일을 열면 아래와 같은 경고를 만나게 됩니다.

파일 연결 경고

 

'콘텐츠 사용'을 클릭하면 아래와 같이 지정된 경로에서 파일을 찾을 수 없다는 메세지가 뜹니다.

(경로 부분은 저희회사 내부 경로라서 모자이크 처리 했습니다.)

 

확인 버튼을 누르면 아래와 같이 연결 편집 옵션이 주어지는데요, 여기서 계속 버튼을 누르면 어떤 파일은 5분이상 기존 경로를 찾으려고 시도하고 엑셀창이 하얗게 되면서 사용자의 간담을 서늘하게 합니다.(여기서 엑셀이 뻗으면 다른 작업중인 파일도 날아가거든요 ㅠㅠ)

 

위의 대화창에서 연결편집 버튼을 누르고 다시 아래 대화창에서 연결 끊기 버튼을 눌러도 연결이 끊어지지 않는 문제가 오늘의 해결 과제 입니다.

 

위의 대화창을 닫고 데이터 탭을 클릭해 보면 '연결 편집' 버튼이 활성화되어 있는 것을 볼 수 있는데요, 이제 우리의 해결사 VBA로 이 문제를 해결해 보겠습니다.

 

VBA 창을 열고 아래 코드를 차례로 실행해 줍니다.(ShowNames 실행 후 DeleteDataValidation 실행)

Sub ShowNames()

    Dim areaName As Name

   

    For Each areaName In ThisWorkbook.Names

        areaName.Visible = True

        areaName.Delete

    Next

   

End Sub

Sub DeleteDataValidation()

    Dim ws As Worksheet

   

    For Each ws In ThisWorkbook.Sheets

        On Error Resume Next

        With ws.Cells.Validation

            .Delete

        End With

        On Error GoTo 0

    Next ws

   

End Sub

 

위의 코드 실행 후 엑셀 파일을 저장한 다음, 다시 열면 문제가 해결 됩니다.

 

불완전한 파일 링크가 정상적으로 끊어져서 '연결 편집' 버튼이 비활성화 된것을 보실 수 있습니다.

연결 편집 에러가 해결된 모습

 

위의 코드로 문제가 해결이 되는 이유는, 엑셀의 연결 끊기 버튼으로는 데이터 유효성에 걸려있는 링크에러를 지울 수 없기 때문입니다. 이걸 찾느라고 허비한 시간을 생각하면 좀 허탈합니다만... 여러분은 같은 문제가 발생했을 때 빠르게 해결하는데 도움이 되셨길 바랍니다.

연결 끊기 버튼으로는 데이터 유효성 링크에러를 지울수가 없음

 

감사합니다.