[VBA] 스프레드시트의 내용을 엑셀로 가져오기

 

 

스프레드시트의 내용을 엑셀로 가져오는 방법에 대해서는 많은 방법이 있다.

파워쿼리로 가져오는 방법, 스프레드시트의 내용을 웹에 게시 후 그것을 파싱하는 방법, 그냥 복붙 하는 방법...

여러가지 방법이 있지만 오늘 소개할 내용이 가장 편하고 쉬운 방법이 아닐까 한다.

 

스프레드시트에서 엑셀로 변환되는 속도 역시 맘에 쏙든다...

 

오늘 예로 보여준 스프레드시트의 내용은 각 시간별로 루프를 돌면서 해당 시간이 되면 텔레그램으로 카카오톡 오픈채팅방에 해당메세지가 발송되는 내용이다.

 

카카오톡으로 챗봇을 만드는 방법은 추후 테스트를 통해서 코드를 공개하려고 한다.(언제일지는 모르겠으나....ㅋㅋ)

 

Option Explicit

Sub ConvertSpreadsheetData()
    Dim url As String: url = "https://docs.google.com/spreadsheets/d/"
    Dim ssid As String: ssid = "스프레드시트ID"
    Dim query As String: query = "/gviz/tq?tqx=out:json"
    Dim strUrl As String: strUrl = url & ssid & query
    Dim jsonData As Object
    
    [a2:c1000].ClearContents    '= 영역 초기화
    
    '= 스프레드시트 데이터 가져오기
    Set jsonData = fetchSpreadsheetData(strUrl)
    
    '= 메시지 처리
    If Not jsonData Is Nothing Then
        Dim rngX As Range: Set rngX = [a2]
        Dim row As Variant
        
        ' 스프레드시트 데이터를 엑셀에 작성
        For Each row In jsonData("table")("rows")
            Dim cellData As Variant
            Set cellData = row("c")
            rngX(1, 1).Value = cellData(1)("f")
            rngX(1, 2).Value = cellData(2)("v")
            rngX(1, 3).Value = cellData(3)("v")
            
            Set rngX = rngX.Offset(1)
        Next row
        
    End If
    MsgBox "스프레드시트 변환을 완료했습니다."
End Sub

Function fetchSpreadsheetData(ByVal url As String) As Object
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", url, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
        .Send
        
        '= HTTP 요청이 성공하면 JSON 데이터를 반환
        If .Status = 200 Then
            Set fetchSpreadsheetData = JsonConverter.ParseJson(.responseText)
        Else
            Set fetchSpreadsheetData = Nothing
        End If
    End With
End Function

 

굳이 이렇게 왜 가져와야 하는지에 대해서 궁금해할 분들도 계시지만 한마디로 스프레드 시트를 서버로 사용하면서 그 안의 데이터를 Query문으로 데이터의 CRUD가 가능해진다.

 

앞서 이 코드의 이용에 대해서 주의할 점은 두가지가 있다. 

첫째, 스프레드시트를 작성할때 불필요한 열을 삭제한다.

스프레드 시트는 엑셀과는 다르게 사용하는 영역만 보이게 하여 메모리 부하를 줄일 수 있다.

 

두번째, 스프레드시트의 id를 가져와야한다.

세번째, JsonConverter의 부분 수정 또는 전처리 과정이 필요하다.물론 본인의 해당파일을 바로 사용하면 되지만 공개되어 있는 JsonConverter를 사용하려면 전처리과정이 필요하다.

 

/*O_o*/ google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"113788560","table":{"cols":[{"id":"A","label":"시간","type":"datetime","pattern":"hh\u0022:\u0022mm"},{"id":"B","label":"오픈채팅방","type":"string"},{"id":"C","label":"메세지","type":"string"}],"rows":[{"c":[{"v":"Date(1899,11,30,6,0,0)","f":"06:00"},{"v":"유튜브 \u0027엑사남\u0027의 Excel VBA 함께하기"},{"v":"/전국날씨"}]},{"c":[{"v":"Date(1899,11,30,7,30,0)","f":"07:30"},{"v":"유튜브 \u0027엑사남\u0027의 Excel VBA 함께하기"},{"v":"/실검"}]},{"c":[{"v":"Date(1899,11,30,9,0,0)","f":"09:00"},{"v":"유튜브 \u0027엑사남\u0027의 Excel VBA 함께하기"},{"v":"/운세"}]},{"c":[{"v":"Date(1899,11,30,12,0,0)","f":"12:00"},{"v":"유튜브 \u0027엑사남\u0027의 Excel VBA 함께하기"},{"v":"식사 맛있게 하세요"}]},{"c":[{"v":"Date(1899,11,30,15,0,0)","f":"15:00"},{"v":"유튜브 \u0027엑사남\u0027의 Excel VBA 함께하기"},{"v":"/실검"}]},{"c":[{"v":"Date(1899,11,30,18,0,0)","f":"18:00"},{"v":"유튜브 \u0027엑사남\u0027의 Excel VBA 함께하기"},{"v":"오늘하루도 수고 하셨습니다."}]},{"c":[{"v":"Date(1899,11,30,6,0,0)","f":"06:00"},{"v":"엑셀VBA (심화학습)/(요청)/(질문) 운영방"},{"v":"/전국날씨"}]},{"c":[{"v":"Date(1899,11,30,7,30,0)","f":"07:30"},{"v":"엑셀VBA (심화학습)/(요청)/(질문) 운영방"},{"v":"/실검"}]},{"c":[{"v":"Date(1899,11,30,9,0,0)","f":"09:00"},{"v":"엑셀VBA (심화학습)/(요청)/(질문) 운영방"},{"v":"/운세"}]},{"c":[{"v":"Date(1899,11,30,12,0,0)","f":"12:00"},{"v":"엑셀VBA (심화학습)/(요청)/(질문) 운영방"},{"v":"식사 맛있게 하세요"}]},{"c":[{"v":"Date(1899,11,30,15,0,0)","f":"15:00"},{"v":"엑셀VBA (심화학습)/(요청)/(질문) 운영방"},{"v":"/실검"}]},{"c":[{"v":"Date(1899,11,30,18,0,0)","f":"18:00"},{"v":"엑셀VBA (심화학습)/(요청)/(질문) 운영방"},{"v":"오늘하루도 수고 하셨습니다."}]}],"parsedNumHeaders":1}});

 

구글 시트의 내용을 가져오면 Json 파일 형태에서 [앞의 47번째 글자] Json [뒤에서 2번째 글자] 를 없애주면 Json 형태가 나오게 된다.

 

'= 구글 문서일 경우
JsonString = VBA.Replace(VBA.Replace(VBA.Replace(JsonString, VBA.vbCr, ""), VBA.vbLf, ""), VBA.vbTab, "")

If InStr(JsonString, "/*O_o*/google.visualization.Query.setResponse") > 0 Then
    endIndex = Len(JsonString) - 2
    JsonString = Mid(JsonString, startIndex, endIndex - startIndex + 1)
End If

 

 

이부분을 공개된 JsonConverter에 삽입하거나 전처리하거나 아님 그냥 본인이 배포한 JsonConver를 그냥 사용하면 된다.

Json 구조자체가 table이 있고 cols와 rows가 있는데, 헤더값은 cols의 label 값을 가져오면 되고 각 행의 데이터값은 순환하면서 v 값 또는 f 값을 가져오면 된다.

이 사진은 이해를 돕기위해 appsScript의 사진을 가져온것이다.

 

 

해당코드가 바로 설명한 내용을 코드화 한 것이다.

 

자, 정리해보자

 

1. 스프레드시트의 불필요한 열을 삭제한다. 행은 안 지워도 된다.

2. 스프레드시트의 Id를 가져온다.

3. 가져온 ID를 공유한 VBA에 붙혀넣는다.

4. 열의 갯수에 맞게 순환문을 조정한다.

 

이게 끝..이다.

 

 

코드를 알려주고 싶은건 엄청 많은데, 요즘 너무 게을러져서...ㅋㅋ

 

암튼 다음에도 불친절한 VBA는 계속된다.

ConvertSpreadshet_Excel(수정본).xlsm

0.06MB