[VBA] 일자별 환율 크롤링하기

  오늘은 크게 두가지로 분류하여 작업하였다.

첫째는 체크박스의 선택값과 월을 변경할 때마다 주말의 색상을 변경하는 조건부 서식과

두번째는 대신 증권에서 일자별 환율을 가지고 온후 공휴일에는 값이 없기 때문에 그 빈 값을 이전 값으로

채워주는 코드를 진행하였다.

체크박스의 연동값은 체크박스의 영역에 흰색처리하여 코드를 진행하였다.

여기에 환율과 매출을 연동하여 일 매출을 구하는 구문이지만 여기에서는 조건부서식과 환율정도만 다루려고 한다.

Sub 체크박스_연결()

    Dim chkBox As Excel.CheckBox
    
    Const r& = 1
    Const c& = 1
    
    For Each chkBox In ActiveSheet.CheckBoxes
    
        With chkBox
            .LinkedCell = .TopLeftCell.Address
        End With
    
    Next

End Sub

Sub 환율()

    Dim strUrl$
    Dim rngA As Range
    Dim rngX As Range
    Dim rngAll As Range: Set rngAll = Range([d4], Cells(Rows.Count, "d").End(3))
    Dim UsaRange As Range: Set UsaRange = [m3:m33]
    Dim ChiRange As Range: Set ChiRange = [n3:n33]
    Dim data
    Dim Temp
    Dim Obj_table As Object
    Dim xmlHttp As Object: Set xmlHttp = CreateObject("msxml2.xmlhttp")
    Dim Html As Object: Set Html = CreateObject("HtmlFile")
    Dim i&, N&
    
    Application.ScreenUpdating = False
    
    Sheets("달러").[a1].CurrentRegion.Offset(1).ClearContents               '= 영역 초기화
    Sheets("위안화").[a1].CurrentRegion.Offset(1).ClearContents
    Sheets("쇼핑몰").[m3:n33].ClearContents
  
    For i = 0 To 1
        strUrl = "https://www.daishin.com/g.ds?m=1071&p=2536&v=1875"        '= 대신증권 일자별 고시
        If i = 0 Then
            N = 0                                                           '= 달러
        Else
            N = 14                                                          '= 위안화
        End If
        
        data = "base_nation=" & N & "&exch_nation=1"                        '= Payload Post방식
        
            With xmlHttp                                                    '= xmlHttp를 통해서
             .Open "Post", strUrl, False
             .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
             .Send (data)
             Html.body.innerhtml = .responsetext                            '= Html.body.innerhtml에 담아라
             
        End With
          
           Set Obj_table = Html.getElementByid("excel").getElementsByTagName("tbody")(0).getElementsByTagName("tr")
          '= 고시 환율 테이블을 담아라
           For Each Obj In Obj_table                                        '= 고시환율 테이블을 순환해라
            
               Temp = Split(Obj.innertext, vbCrLf)                          '= 고시환율 테이블에서 날짜 / 환율 / 전일가를 분리해라
               
               If N = 0 Then                                                '= 달러일 경우
                Sheets("달러").Cells(Rows.Count, "a").End(3)(2) = Temp(1)
                Sheets("달러").Cells(Rows.Count, "b").End(3)(2) = Temp(3)
               Else                                                         '= 위안화일 경우
                Sheets("위안화").Cells(Rows.Count, "a").End(3)(2) = Temp(1)
                Sheets("위안화").Cells(Rows.Count, "b").End(3)(2) = Temp(3)
               End If
        
           Next Obj
           
           If N = 0 Then                                                    '= 달러일 경우
           UsaRange = Application.VLookup(Sheets("쇼핑몰").[k3:k33], Sheets("달러").[A2:B500], 2, 0)
          '= 달러시트에서 해당 환율을 가져와라
          
                For Each rngA In UsaRange                                   '= 달러 영역을 순환해라
                
                     If rngA.Formula = "#N/A" Then rngA = "=R[-1]C"         '= 달러값이 에러일 경우 바로 위의 값을 참조해라
                     
                     If rngA = "달러" Or rngA = "위안화" Then rngA = M_chk(rngA, N)
                                                                            '= 환율을 참조한 값이 달러 / 위안화 일경우 지난 환율을 조회해라
                Next rngA
           
           Else                                                              '= 위안화 일우
           ChiRange = Application.VLookup(Sheets("쇼핑몰").[k3:k33], Sheets("위안화").[A2:B500], 2, 0)
          '= 위안화 시트에서 해당 환율을 가져와라
                For Each rngA In ChiRange                                    '= 위안화 영역을 순환해라
                
                     If rngA.Formula = "#N/A" Then rngA = "=R[-1]C"          '= 위안화 값이 에러일 경우 바로 위의 값을 참조해라
                     
                     If rngA.Formula = "달러" Or rngA = "위안화" Then rngA = M_chk(rngA, N)
                                                                             '= 환율을 참조한 값이 달러 / 위안화 일경우 지난 환율을 조회해라
                Next rngA
                
           End If
           
    Next i
    
    Application.ScreenUpdating = True

End Sub


Function M_chk(rngA As Range, N&)
    
    Dim sDate As Range
    Dim Temp As Range
                              
        Set sDate = [k3]                                                    '= 매월의 첫날을 sDate로 선언
        Set Temp = [o3]                                                     '= 환율이 있는 날까지 날짜를 이전으호 회기해야 하는 임시 장소
        Temp = sDate - 1
        Do
            If N = 0 Then                                                          '= 달러일경우
                rngA = Application.VLookup(Temp, Sheets("달러").[A2:B500], 2, 0)   '= 환율값에서 환율값을 가져와라
            Else                                                                   '= 위안화일 겨우
                rngA = Application.VLookup(Temp, Sheets("위안화").[A2:B500], 2, 0) '= 환율값에서 환율값을 가져와라
            End If
            
            If rngA.Formula = "#N/A" Then                                   '= 환율값이 에러이면 / 환율값이 없다면
               Temp = Temp - 1                                              '= 날짜를 하루 이전으로 수정해라
            ElseIf rngA <> "달러" Or rngA <> "위안화" Then                  '= 환율이 표시된다면
               Exit Do                                                      '= Do 문을 탈출해라
            End If
            
        Loop
            
            M_chk = rngA                                                    '= 함수값을 반환해라
        
   
End Function

일별환율(22.10.14).xlsm

0.66MB