.
아래 게시글도 함께 확인해보세요!
엑셀 GoogleTranslate 함수를 만드는 전체 과정은 아래 VBA 프로젝트 강의를 확인하세요!
엑셀 GoogleTranslate 함수 코드
👇 아래 코드를 복사한 후, 매크로 편집기에 붙여넣기하면 엑셀에서 GoogleTranslate 함수를 바로 사용할 수 있습니다. 단, 이 코드는 '윈도우'에서만 호환되므로, Mac 사용자분들은 이용할 수 없습니다. 이 점 양해 부탁드립니다.
오빠두Tip : VBA를 처음 사용하시는 분은, 아래 사용 시 주의사항을 꼭 확인해주세요!
'--------------------------------------
'● 이번 강의는 입문자 대상으로, 변수 설정이나 오류처리 없이 최대한 간단하게 준비했습니다.
' 오류처리를 모두 포함한 GoogleTranslate 함수 제작방법은 아래 프리미엄 클래스를 확인하세요!
' https://www.oppadu.com/엑셀-live-72강/
'---------------------------------------
Function GoogleTranslate(OriginalText, Optional sFrom As String = "auto", Optional sTo As String)
Dim sTranslate As String
If sTo = "" And sFrom <> "auto" Then
sTo = sFrom
sFrom = "auto"
End If
If TypeName(OriginalText) = "Range" Then
If OriginalText.Cells.Count > 1 Then
Dim r As Range
For Each r In OriginalText
sTranslate = sTranslate & r.Value & vbNewLine
Next
sTranslate = Left(sTranslate, Len(sTranslate) - 1)
Else
sTranslate = OriginalText.Value
End If
Else
sTranslate = OriginalText
End If
strURL = "https://translate.google.com/m?sl=" & sFrom & "&tl=" & sTo & "&q=" & ENCODEURL(sTranslate)
oResult = GetHttp(strURL)
sResult = Splitter(oResult, "<div class=""result-container"">", "</div>")
If Len(sResult) = 0 Then
sResult = Splitter(oResult, "result-container>", "</DIV>")
End If
GoogleTranslate = sResult
End Function
Function GetHttp(URL, Optional formText As String, _
Optional isWinHttp As Boolean = False, _
Optional RequestHeader As Variant, _
Optional includeMeta As Boolean = False, _
Optional RequestType As String = "GET", _
Optional returnInnerHTML As Boolean = True)
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'▶ GetHttp 함수
'▶ 웹에서 데이터를 받아옵니다.
'▶ 인수 설명
'_____________URL : 데이터를 스크랩할 웹 페이지 주소입니다.
'_____________formText : Encoding 된 FormText 형식으로 보내야 할 경우, Send String에 쿼리문을 추가합니다.
'_____________isWinHttp : WinHTTP 로 요청할지 여부입니다. Redirect가 필요할 경우 True로 입력하여 WinHttp 요청을 전송합니다.
'_____________RequestHeader : RequestHeader를 배열로 입력합니다. 반드시 짝수(한 쌍씩 이루어진) 개수로 입력되어야 합니다.
'_____________includeMeta : TRUE 일 경우 HTML 문서위로 ResponseText를 강제 입력합니다. Meta값이 포함되어 HTML이 작성되며 innerText를 사용할 수 없습니다. 기본값은 False 입니다.
'_____________RequestType : 요청방식입니다. 기본값은 "GET"입니다.
'_____________ReturnInnerHTML : TRUE 일 경우 InnerHTML을 기본으로 반환합니다. 기본값은 TRUE 입니다.
'▶ 사용 예제
'Dim HtmlResult As Object
'Set htmlResult = GetHttp("https://www.naver.com")
'msgbox htmlResult.body.innerHTML
'###############################################################
Dim oHTMLDoc As Object: Dim objHTTP As Object
Dim HTMLDoc As Object
Dim i As Long: Dim blnAgent As Boolean: blnAgent = False
Dim sUserAgent As String: sUserAgent = "Mozilla/5.0 (Linux; Android 6.0; Nexus 5 Build/MRA58N) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.183 Mobile Safari/537.36"
Application.DisplayAlerts = False
If Left(URL, 4) <> "http" Then URL = "http://" & URL
Set oHTMLDoc = CreateObject("HtmlFile")
Set HTMLDoc = CreateObject("HtmlFile")
' 2023-02-22 | 수정 | 윈도우 인증 접속 문제 발생 시 (일부 버전) ServerXMLHTTP -> XMLHTTP 요청으로 변경
' XMLHTTP 요청 시, TimeOut 세팅 불가 (기본값 설정)
' https://stackoverflow.com/questions/11605613/differences-between-xmlhttp-and-serverxmlhttp
On Error GoTo SendError:
'------------------------------
If isWinHttp = False Then
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Else
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
End If
objHTTP.setTimeouts 1200000, 1200000, 1200000, 1200000 '응답 대기시간 120초
' 2023-02-22 | 수정 | 윈도우 인증 접속 문제 발생 시 (일부 버전) ServerXMLHTTP -> XMLHTTP 요청으로 변경
SendRestart:
'------------------------------
objHTTP.Open RequestType, URL, False
If Not IsMissing(RequestHeader) Then
Dim vRequestHeader As Variant
For Each vRequestHeader In RequestHeader
Dim uHeader As Long: Dim Lheader As Long: Dim steps As Long
uHeader = UBound(vRequestHeader): Lheader = LBound(vRequestHeader)
If (uHeader - Lheader) Mod 2 = 0 Then GetHttp = CVErr(xlValue): Exit Function
For i = Lheader To uHeader Step 2
If vRequestHeader(i) = "User-Agent" Then blnAgent = True
objHTTP.setRequestHeader vRequestHeader(i), vRequestHeader(i + 1)
Next
Next
End If
If blnAgent = False Then objHTTP.setRequestHeader "User-Agent", sUserAgent
objHTTP.send formText
If includeMeta = False Then
With oHTMLDoc
.Open
.Write objHTTP.responseText
.Close
End With
Else
oHTMLDoc.body.innerhtml = objHTTP.responseText
End If
If returnInnerHTML = True Then
GetHttp = oHTMLDoc.body.innerhtml
Else
Set GetHttp = oHTMLDoc
End If
Set oHTMLDoc = Nothing
Set objHTTP = Nothing
Application.DisplayAlerts = True
' 2023-02-22 | 수정 | 윈도우 인증 접속 문제 발생 시 (일부 버전) ServerXMLHTTP -> XMLHTTP 요청으로 변경
Exit Function
SendError:
Set objHTTP = CreateObject("MSXML2.XMLHTTP")
On Error GoTo 0
Resume SendRestart:
'------------------------------
End Function
Function ENCODEURL(varText As Variant, Optional blnEncode = True)
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'▶ EncodeURL 함수
'▶ 한글/영문, 특수기호가 포함된 문자열을 웹 URL 표준 주소로 변환합니다.
'▶ 인수 설명
'_____________varTest : 표준 URL 주소로 변환할 문자열입니다.
'_____________blnEncode : TRUE 일 경우 결과값을 출력합니다.
'▶ 사용 예제
's = "http://www.google.com/search=사과"
's = ENCODEURL(s)
'MsgBox s
'###############################################################
Static objHtmlfile As Object
If objHtmlfile Is Nothing Then
Set objHtmlfile = CreateObject("htmlfile")
With objHtmlfile.parentWindow
.execScript "function encode(s) {return encodeURIComponent(s)}", "jscript"
End With
End If
If blnEncode Then
ENCODEURL = objHtmlfile.parentWindow.encode(varText)
End If
End Function
Function Splitter(v As Variant, Cutter As String, Optional Trimmer As String)
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'▶ Splitter 함수
'▶ Cutter ~ Timmer 사이의 문자를 추출합니다. (Timmer가 빈칸일 경우 Cutter 이후 문자열을 추출합니다.)
'▶ 인수 설명
'_____________v : 문자열입니다.
'_________Cutter : 문자열 절삭을 시작할 텍스트입니다.
'_________Trimmer : 문자열 절삭을 종료할 텍스트입니다. (선택인수)
'▶ 사용 예제
'Dim s As String
's = "{sa;b132@drama#weekend;aabbcc"
's = Splitter(s, "@", "#")
'msgbox s '--> "drama"를 반환합니다.
'###############################################################
Dim vaArr As Variant
On Error GoTo EH:
vaArr = Split(v, Cutter)(1)
If Not IsMissing(Trimmer) Then vaArr = Split(vaArr, Trimmer)(0)
Splitter = vaArr
Exit Function
EH:
Splitter = ""
End Function
엑셀 GoogleTranslate 함수 사용법
엑셀로 문서를 작성하면서 다양한 언어로 번역이 필요할 때, 번역사이트를 번갈아가며 작업하려면 상당히 번거로울 수 있습니다. 구글 시트에서 기본으로 제공하는 GoogleTranslate 함수(구글 시트)를 사용하면 번역을 편리하게 할 수 있지만, 엑셀에서는 이러한 번역 함수를 기본으로 제공하지 않아 불편했습니다.
구글 시트에서는 GoogleTranslate 함수를 기본으로 제공합니다.
이번에 소개해드린 VBA 매크로를 사용하면, 누구나 쉽게 복사/붙여넣기로 GoogleTranslate 함수를 엑셀에서 바로 사용할 수 있습니다.
- 구글 번역을 사용할 엑셀 파일을 실행한 후, 단축키 Alt + F11 을 눌러서 매크로 편집기를 실행합니다.
단축키 Alt + F11 을 눌러 매크로 편집기를 실행합니다.
오빠두Tip : 또는 리본 메뉴를 마우스로 우클릭한 후, [리본 메뉴 사용자 지정]에서 [개발도구]를 활성화해서, [개발도구] 탭의 [Visual Basic] 버튼을 클릭해서 매크로 편집기를 실행할 수 있습니다.
- 매크로 편집기가 실행되면, [삽입] 탭 - [모듈]을 클릭해서 새로운 모듈을 추가합니다.
매크로 편집기에서 [삽입] - [모듈]을 클릭해서 새 모듈을 삽입합니다.
- 홈페이지에 남겨드린 GoogleTranslate 함수 코드를 복사한 후, 모듈 안에 붙여넣기 합니다.
홈페이지에 작성된 GoogleTranslate 함수 코드를 복사하여 모듈 안에 붙여넣기 합니다.
- 매크로 편집기를 종료한 후, 엑셀 시트에서 "=Goo"를 입력하면 GoogleTranslate 함수가 등록된 것을 확인할 수 있습니다.
이제 시트에서 "=Goo"를 입력하면 GoogleTranslate 함수가 목록에 표시됩니다.
- GoogleTranslate 함수의 사용법은 아래와 같으며, 인수 2-3개로 간편하게 사용할 수 있습니다.
<div style="text-align: center;">=GoogleTranslate(번역할단어,[출발어,도착어])
· 출발어의 기본값은 '자동감지'이며, 도착어의 기본값은 '영어'입니다.
· 출발어와 도착어 중 하나만 입력하면 해당 언어코드는 도착어로 간주되며, 출발어는 '자동감지'로 설정됩니다.
· 매크로 함수에는 인수 설명이 표시되지 않습니다.
예)
=GoogleTranslate("안녕하세요!","ja") → 출발어: 자동감지, 도착어: 일본어
=GoogleTranslate("Hello!","en","ko") → 출발어: 영어, 도착어: 한국어
=GoogleTranslate("Hello!","ko") → 출발어: 자동감지, 도착어: 한국어
예제파일에서 D8셀에 아래와 같이 수식을 입력한 후, 아래로 자동채우기하면 언어을 자동으로 감지하여 영어로 번역합니다.
=GoogleTranslate(B8,"en")
'출발어/도착어 중 하나만 입력했으므로, 출발어는 '자동감지', 도착어는 'en(영어)'로 설정됩니다.
수식을 입력한 후, 자동채우기해서 셀 안의 내용은 편리하게 번역할 수 있습니다.
실무에서 자주 사용하는 언어 코드는 아래와 같습니다. 구글 번역에서 제공하는 언어 전체 목록은 실습파일의 언어코드 시트 또는 구글 공식홈페이지 링크를 확인하세요.
· 한국어 : ko
· 영어 : en
· 일본어 : ja
· 중국어 : zh-CN
· 러시아어 : ru
· 베트남어 : vi
· 태국어 : th
엑셀 파일에서 GoogleTranslate 함수를 일회성으로 사용할 경우, 3번 단계에서 추가한 모듈을 삭제합니다. 단축키 Alt + F11 을 눌러 매크로 편집기를 실행한 후, 모듈을 우클릭 - [제거]를 클릭한 후, '제거하기전에 모듈을 내보내시겠습니까?'라는 안내창이 나오면 [아니오] 버튼을 클릭해서 모듈을 제거합니다.
현재 엑셀 파일에서 GoogleTranslate 함수를 계속 사용하려면, '매크로 포함 통합문서(*.xlsm)' 형태로 파일을 저장합니다. 단축키 F12 키를 누르거나, [파일] 탭 - [다른 이름으로 저장] 에서 파일 형식으로 '매크로 사용 통합 문서(*.xlsm)' 으로 선택한 후 파일을 저장합니다.
엑셀 GoogleTranslate 함수를 추가기능으로 만들면, 모든 엑셀파일에서 GoogleTranslate 함수를 편리하게 사용할 수 있습니다. 추가기능 파일을 만들고 등록하는 방법은 아래 30분 총정리 강의를 확인하세요!
