[VBA] 엑셀시트를 바탕화면에 저장하기 WScript.Shell

안녕하세요 케이네입니다.

업무중에 사용하는 시트를 업체에서 엑셀시트로 요청하는 경우가 있어요. 엑셀파일 전체를 보낼수는 없으니 해당하는 시트만 따로 저장해서 이름을 새로 설정하고 보내는 일이 종종 있는데요. 이것도 하루에 몇건씩 되다보면 귀찮은 작업이지요. 그리고 수정을 해서 다시 보내달라고 해서 예상치 못한 업무량이 늘어날때도 있고요. 

 

VBA로 현재의 시트를 바탕화면에 자동으로 이름을 붙여서 저장하는 방법을 알아보겠습니다. 첨부파일은 임의로 만들어본 팩킹리스트 입니다. 매번 팩킹리스트를 엑셀파일로 보내달라고 하는 거래처가 있을때 유용하겠지요? 엑셀파일에 새로운 시트를 추가해서 매번 새로운 팩킹리스트를 만드는데 파일 전체를 보내게 되면 메일의 용량이 너무 커지기 때문에 해당하는 파일만 엑셀파일로 저장해서 보내는게 좋겠습니다.

 

 

 

Sub Sheet_SaveExcel()
    Dim sht As Worksheet '새로 저장할 시트를 넣을 공간
    Dim FileName As String '파일이름을 저장해놓을 공간
    Dim WSH As Variant '바탕화면 경로를 불러오기 위한 공간
    
    Set WSH = CreateObject("WScript.Shell") '바탕화면을 저장하기 위해서 WScript.Shell 라는게 사용해야 한다고 해요
    Path = WSH.SpecialFolders("Desktop") & "\" '바탕화면의 경로는 모두 상이하지요. 왜냐하면 USER 이름이 다르니까요. 근데 WScript.Shell 에서는 각자의 바탕화면 경로를 지정해 준다고 하네요.

    Application.ScreenUpdating = False '일단 모든 엑셀 작업을 중단시킵니다.
    Set sht = ThisWorkbook.ActiveSheet '엑셀시트를 현재 활성화된 시트로 지정해요 (새로 저장할 엑셀시트요)
    With ActiveSheet
            FileName = Path & sht.Range("B4") & ".xlsx" '파일이름을 지정된 셀로 선택하면서 경로도 같이 이름에 넣어줍니다.
            sht.Copy '현재 활성화된 시트를 복사해요

            With ActiveWorkbook
                 .SaveAs FileName:=FileName '워크북 하나를 새로 만드는겁니다. 이름은 지정한 대로해주고요
                 .Close '새로 저장된 파일이 자동으로 열리지 않도록 닫아줍니다
            End With

    End With
End Sub

 

VBA를 돌리고 나면, 아래 그림과 같이 바탕화면에 저장이 됩니다.

 

VBA에서 워크시트나 워크북으로 어디까지 컨트롤이 가능할지는 마이크로소프트에서 아래와 같이 소개해 놓은곳이 있었네요. 참고해 보시는것도 좋을것 같아요! 아직은 어렵고 멀고먼 VBA의 세계네요.. 케이네도 열심히 업무 효율화를 위해 노력해야겠어요. 

 

https://docs.microsoft.com/en-us/office/vba/api/excel.workbook

Workbook object (Excel)

 

현재시트를엑셀파일로바탕화면에저장.xlsm

0.02MB