여러 엑셀파일을 공통적으로 한번에 수정하는 방법

현장에서 엑셀로 문서작업을 많이 합니다. 

그런 엑셀 작업 중 양식은 같은데 내용만 조금 틀리거나 하는 경우가 있습니다. 

그런 비슷한 엑셀 파일이 백개 정도가 있고 모든 엑셀 파일의 내용을 수정해야 한다 하면 

예로 들어서 모든 엑셀 파일의 첫 시트의 A1셀에 내용을 수정해야 할 때 

하나를 금방 수정한다고 해도 백개라면 한 시간은 금방 지나간다고 생각합니다. 

물론 엑셀을 조금 아시는 분들은 VBA로 금방 하시겠지만 

매크로를 새로 짠다는 건 귀찮을 수도 있거든요 

그래서 더 빠르고 효과적으로 수정하는 방법을 소개해 볼까 합니다.

#대상 엑셀파일이 존재하는 디렉터리
$destination = "C:\test\"
#대상 엑셀 파일들의 목록을 추출
$dirName = Get-ChildItem -Name -Filter *.xlsm
$saveAs = $destination + "new\"

foreach($z in $dirName){
    #엑셀 기동
    $excel=New-Object -ComObject Excel.Application
    $excel.visible=$false
    $excel.DisplayAlerts=$false

    #데이터 처리
    #엑셀파일 오픈
    $book=$excel.Workbooks.Open($destination + $z)
    #시트 취득 item(1) 첫번째 시트
    $sheet=$book.Worksheets.item(1)
    #시트 내용을 수정
    $sheet.Cells.Item(1,1)="=now()"
    #저장 및 종료처리
    $book.SaveAs($saveAs + $z)
    $excel.Quit()
    $excel=$null
}

 

위에 스크립트를 실행하면은 new라는 폴더에 수정된 엑셀 파일이 저장됩니다.

단 저셀을 실행시키 전에 new라는 폴더를 만들어 주세요.

 

실행 전 엑셀 파일입니다. A1에 =now() 함수를 삽입시켜서 저장할 것입니다.

 

실행 후 new 폴더 안에 엑셀 파일들은 A1에 now() 함수가 있는 상태로 보존되어 있습니다.

 

지금은 예시로 한 개의 엑셀 파일에 대해서 설명을 드렸습니다.

 

엑셀을 다루는 언어로는 엑셀 VBA언어가 뛰어나지만 윈도우 파일을 다루는 능력은 파워쉘이 더 좋습니다.

만약 엑셀의 여러 곳을 수정을 해야 하는 경우라면은 당연히 VBA로 수정하는 것이 맞다고 생각합니다.

하지만 수정해야 하는 부분이 적다고 하면은 파워쉘을 이용하는 방법도 고려해 볼만 하다고 생각합니다.

물론 VBA에서도 간단하게 디렉터리랑 파일을 다룰 수 있지만 파워쉘이 더 간단하거든요.

또 자세히 보시면 엑셀을 다루는 스크립트는 VBA와 닮아있기 때문에 VBA를 아신다면 여러 곳을 수정하는 데 있어서도 꼭 VBA보다는 나쁘지 않다고 생각합니다.