엑셀 매크로로 이미지나 첨부 파일을 URL에서 바로 내려받아 폴더에 저장해야 할 때 쓰는 함수입니다. 상품 이미지 목록을 시트에 정리해 두고 한 번에 받는다든지, 매일 올라오는 보고서 파일을 받아 오는 식의 작업에 씁니다.

Function downloadFile(URL, localPath) As String
    On Error GoTo goErr
    Set winhttp = CreateObject("WinHttp.WinHttpRequest.5.1")
    winhttp.Open "GET", URL, False
    winhttp.Send
    Set objStream = CreateObject("ADODB.Stream")
    objStream.Open
    objStream.Type = 1
    objStream.Write winhttp.responseBody
    objStream.SaveToFile localPath, 2
    objStream.Close
    Exit Function
goErr:
    downloadFile = Err.Description
End Function

어떻게 동작하나

WinHttp.WinHttpRequest.5.1 로 GET 요청을 보내고, 세 번째 인자 False 로 동기 방식을 지정해서 Send 가 응답을 다 받을 때까지 기다리게 합니다. 응답을 responseText 가 아니라 responseBody 로 꺼내는 게 중요합니다. responseText 는 문자열로 해석하는 과정에서 이미지나 압축 파일 같은 바이너리 데이터를 망가뜨리지만, responseBody 는 받은 바이트 배열 그대로입니다.

광고

그 바이트를 파일로 쓰는 데 ADODB.Stream 을 씁니다. Type = 1 은 바이너리 모드(adTypeBinary), SaveToFile 의 두 번째 인자 2 는 같은 이름의 파일이 있으면 덮어쓰기(adSaveCreateOverWrite)입니다. 이 값을 1로 두면 파일이 이미 있을 때 오류가 납니다.

반환값은 문자열인데, 성공하면 아무것도 넣지 않으니 빈 문자열이 돌아오고, 실패하면 오류 설명이 돌아옵니다. 그래서 호출하는 쪽에서는 이렇게 씁니다.

Dim r As String
r = downloadFile("https://example.com/sample.jpg", "C:\temp\sample.jpg")
If r = "" Then
    Debug.Print "완료"
Else
    Debug.Print "실패: " & r
End If

쓰다 보면 막히는 곳

변수를 선언하지 않고 바로 Set 하고 있어서, 모듈 맨 위에 Option Explicit 이 있으면 컴파일이 안 됩니다. 그리고 서버가 404나 500을 돌려줘도 그 오류 페이지 HTML이 그대로 파일로 저장돼서, 확장자는 .jpg 인데 열리지 않는 파일이 생깁니다. 상태 코드를 확인하고 선언을 넣은 버전은 이렇습니다.

Function downloadFile(URL, localPath) As String
    Dim winhttp As Object, objStream As Object
    On Error GoTo goErr
    Set winhttp = CreateObject("WinHttp.WinHttpRequest.5.1")
    winhttp.Open "GET", URL, False
    winhttp.Send
    If winhttp.Status <> 200 Then
        downloadFile = "HTTP " & winhttp.Status
        Exit Function
    End If
    Set objStream = CreateObject("ADODB.Stream")
    objStream.Open
    objStream.Type = 1
    objStream.Write winhttp.responseBody
    objStream.SaveToFile localPath, 2
    objStream.Close
    Exit Function
goErr:
    downloadFile = Err.Description
End Function

저장할 폴더가 없으면 SaveToFile 에서 "파일을 쓸 수 없습니다" 류의 오류가 나니 폴더는 미리 만들어 둬야 합니다. 일부 사이트는 User-Agent 가 없는 요청을 막기도 해서, 다운로드가 계속 403으로 실패하면 Send 전에 winhttp.SetRequestHeader "User-Agent", "Mozilla/5.0" 같은 헤더를 넣어 보세요.

동기 방식이라 큰 파일을 받는 동안에는 엑셀이 멈춘 것처럼 보입니다. 여러 파일을 반복해서 받을 때는 루프 안에서 DoEvents 를 한 번씩 불러 주고, 상태 표시줄(Application.StatusBar)에 진행 상황을 찍어 두면 사용자가 덜 답답해합니다.

시트에 정리한 URL 목록을 한 번에 받기

A열에 URL을 쭉 적어 두고 B열에 결과를 받는 형태로 돌리면, 끝난 뒤 B열이 비어 있는 행은 성공이고 글자가 있는 행만 다시 보면 됩니다. 파일 이름은 URL의 마지막 / 뒤쪽을 그대로 씁니다.

Sub DownloadList()
    Dim i As Long, url As String, fName As String
    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        url = Cells(i, "A").Value
        fName = Mid(url, InStrRev(url, "/") + 1)
        Cells(i, "B").Value = downloadFile(url, "C:\temp\" & fName)
        Application.StatusBar = i & "행 처리 중"
        DoEvents
    Next i
    Application.StatusBar = False
End Sub

URL 끝에 ?w=500 같은 쿼리 문자열이 붙어 있으면 그 부분까지 파일 이름에 들어가는데, ? 는 파일 이름에 쓸 수 없는 문자라 저장에서 실패합니다. 이런 주소가 섞여 있다면 InStr 로 ? 위치를 찾아 앞부분만 잘라 쓰거나, 행 번호로 이름을 따로 붙이는 편이 낫습니다. 같은 이름의 파일이 여러 URL에서 나오면 덮어쓰기 모드라 마지막 것만 남는다는 점도 기억해 두세요.

응답을 기다리다 멈추는 경우

WinHttpRequest 는 시간 제한 기본값이 연결 60초, 송신·수신 각 30초입니다. 응답이 없는 서버를 만나면 그 시간 동안 엑셀이 그대로 멈춰 있습니다. 목록이 길다면 Open 앞에 winhttp.SetTimeouts 5000, 5000, 10000, 10000 처럼 밀리초 단위로 이름 확인·연결·송신·수신 제한을 줄여 두면, 죽은 주소 하나 때문에 전체가 오래 묶이지 않습니다. 시간이 넘으면 오류로 빠지니 반환값에 시간 초과 메시지가 담깁니다.

responseBody 는 받은 내용을 통째로 메모리에 올립니다. 수백 MB짜리 파일을 이 방식으로 받으면 32비트 오피스에서는 메모리 부족이 날 수 있어서, 그 정도 크기는 브라우저나 전용 도구로 받는 게 현실적입니다.

오래된 Windows 7 환경에서 HTTPS 주소만 "보안 채널 지원에서 오류가 발생했습니다"로 실패한다면, 운영체제의 WinHttp가 TLS 1.2를 기본으로 쓰지 않아서인 경우가 많습니다. 코드 문제가 아니라 윈도 업데이트와 레지스트리 설정 쪽 문제라서 매크로만 고쳐서는 해결되지 않습니다.