Sub GetAPIData()
    Dim http As Object
    Dim url As String
    Dim response As String
    
    ' HTTP 객체 생성
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    ' API URL 설정
    url = "https://api.example.com/data?key=YOUR_API_KEY"
    
    ' GET 요청 보내기
    http.Open "GET", url, False
    http.send
    
    ' 응답 받기
    response = http.responseText
    
    ' 응답을 셀에 출력
    Range("A1").Value = response
    
    Set http = Nothing
End Sub

async function main(workbook: ExcelScript.Workbook) {
  // API 호출
  const response = await fetch('https://api.example.com/data');
  const data = await response.json();
  
  // 활성 시트 가져오기
  const sheet = workbook.getActiveWorksheet();
  
  // 데이터를 셀에 입력
  sheet.getRange("A1").setValue(data.title);
  sheet.getRange("A2").setValue(data.value);
}

Sub GetSAPData()
    Dim http As Object
    Dim url As String
    Dim apiKey As String
    Dim jsonResponse As String
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    ' API 설정
    apiKey = "YOUR_SAP_API_KEY"
    url = "https://your-sap-server.com/api/sales-data"
    
    ' 헤더와 함께 요청
    http.Open "GET", url, False
    http.setRequestHeader "Authorization", "Bearer " & apiKey
    http.setRequestHeader "Content-Type", "application/json"
    http.send
    
    ' 응답 처리
    If http.Status = 200 Then
        jsonResponse = http.responseText
        ' JSON 파싱 및 데이터 입력 로직
        Call ParseAndInsertData(jsonResponse)
    Else
        MsgBox "API 호출 실패: " & http.Status
    End If
    
    Set http = Nothing
End Sub

Sub GetMailchimpData()
    Dim http As Object
    Dim url As String
    Dim apiKey As String
    Dim datacenter As String
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    ' Mailchimp 설정
    apiKey = "your-api-key"
    datacenter = "us1"  ' API 키에서 확인 가능
    url = "https://" & datacenter & ".api.mailchimp.com/3.0/campaigns"
    
    ' Basic 인증 사용
    http.Open "GET", url, False
    http.setRequestHeader "Authorization", "Basic " & _
        EncodeBase64("anystring:" & apiKey)
    http.send
    
    ' 응답 데이터 처리
    Debug.Print http.responseText
    
    Set http = Nothing
End Sub

Sub GetExchangeRate()
    Dim http As Object
    Dim url As String
    Dim response As String
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    ' 한국은행 API (실제로는 인증키 필요)
    url = "https://www.koreaexim.go.kr/site/program/financial/exchangeJSON" & _
          "?authkey=YOUR_AUTH_KEY&data=AP01"
    
    http.Open "GET", url, False
    http.send
    
    response = http.responseText
    
    ' JSON 파싱 후 셀에 입력
    ' (실제로는 JSON 파싱 라이브러리 필요)
    Range("A1").Value = "USD/KRW"
    Range("B1").Value = ParseExchangeRate(response)
    
    Set http = Nothing
End Sub

https://api.example.com/data?api_key=abc123xyz456

http.setRequestHeader "Authorization", "Bearer YOUR_ACCESS_TOKEN"

http.setRequestHeader "Authorization", "Basic " & _
    EncodeBase64("username:password")

{
  "users": [
    {
      "id": 1,
      "name": "김철수",
      "email": "kim@example.com",
      "orders": [
        {"product": "노트북", "price": 1500000},
        {"product": "마우스", "price": 30000}
      ]
    },
    {
      "id": 2,
      "name": "이영희",
      "email": "lee@example.com",
      "orders": [
        {"product": "키보드", "price": 120000}
      ]
    }
  ]
}

// Power Query M 코드 예시
let
    네이버데이터 = Excel.CurrentWorkbook(){[Name="네이버"]}[Content],
    쿠팡데이터 = Excel.CurrentWorkbook(){[Name="쿠팡"]}[Content],
    통합데이터 = Table.Combine({네이버데이터, 쿠팡데이터})
in
    통합데이터

Sub AutoRefreshAndSave()
    ' 모든 쿼리 새로 고침
    ActiveWorkbook.RefreshAll
    
    ' 새로 고침 완료 대기
    DoEvents
    Application.Wait Now + TimeValue("00:00:05")
    
    ' 저장
    ActiveWorkbook.Save
    
    ' 엑셀 종료
    Application.Quit
End Sub

@echo off
"C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE" ^
"C:\Users\YourName\Documents\data.xlsm" /x /m "AutoRefreshAndSave"

async function main(workbook: ExcelScript.Workbook) {
  // 모든 쿼리 새로 고침
  await workbook.refreshAllDataConnections();
  
  // 특정 시트 가져오기
  const sheet = workbook.getWorksheet("Dashboard");
  
  // 마지막 업데이트 시간 기록
  const now = new Date();
  sheet.getRange("A1").setValue("마지막 업데이트: " + now.toLocaleString());
  
  return "데이터 새로 고침 완료!";
}

Sub SendEmailNotification()
    Dim objEmail As Object
    Set objEmail = CreateObject("CDO.Message")
    
    With objEmail
        .From = "your-email@company.com"
        .To = "your-phone@carrier.com"  ' SMS 게이트웨이
        .Subject = "엑셀 데이터 업데이트 완료"
        .TextBody = "데이터가 성공적으로 업데이트되었습니다."
        
        .Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
        .Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587
        .Configuration.Fields.Update
        
        .Send
    End With
    
    Set objEmail = Nothing
End Sub

=IFERROR((이번달매출-지난달매출)/지난달매출, 0)

=실제매출/목표매출

=매출원가/(기초재고+기말재고)/2

Sub DebugAPIResponse()
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    http.Open "GET", "https://api.example.com/data", False
    http.send
    
    ' 즉시 실행 창에 출력
    Debug.Print "Status: " & http.Status
    Debug.Print "Response: " & http.responseText
    
    ' 또는 메시지 박스로 확인
    MsgBox http.responseText
    
    Set http = Nothing
End Sub

Sub WriteLog(message As String)
    Dim fso As Object
    Dim logFile As Object
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set logFile = fso.OpenTextFile("C:\Logs\api_log.txt", 8, True)
    
    logFile.WriteLine Now & " - " & message
    logFile.Close
    
    Set logFile = Nothing
    Set fso = Nothing
End Sub

' 사용 예시
WriteLog "API 호출 시작"
' ... API 호출 코드 ...
WriteLog "API 호출 완료"

Sub SafeAPICall()
    On Error GoTo ErrorHandler
    
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    http.Open "GET", "https://api.example.com/data", False
    http.send
    
    If http.Status = 200 Then
        ' 성공 처리
        Range("A1").Value = http.responseText
    Else
        ' 오류 처리
        MsgBox "API 오류: " & http.Status
    End If
    
    Set http = Nothing
    Exit Sub
    
ErrorHandler:
    MsgBox "오류 발생: " & Err.Description
    WriteLog "오류: " & Err.Description
End Sub

Application.ScreenUpdating = False
' ... 작업 수행 ...
Application.ScreenUpdating = True

Application.Calculation = xlCalculationManual
' ... 데이터 입력 ...
Application.Calculation = xlCalculationAutomatic

Dim dataArray(1 To 100, 1 To 5) As Variant
' ... 배열에 데이터 채우기 ...
Range("A1:E100").Value = dataArray

url = "https://api.example.com/data?limit=100&fields=id,name,price"

from flask import Flask, jsonify
import websocket
import json

app = Flask(__name__)
latest_price = 0

def on_message(ws, message):
    global latest_price
    data = json.loads(message)
    latest_price = data['price']

def on_error(ws, error):
    print(error)

def on_close(ws):
    print("Connection closed")

def on_open(ws):
    ws.send(json.dumps({"type": "subscribe", "symbol": "BTCUSDT"}))

@app.route('/price')
def get_price():
    return jsonify({'price': latest_price})

if __name__ == "__main__":
    # WebSocket 연결 (별도 스레드)
    ws = websocket.WebSocketApp("wss://stream.binance.com:9443/ws/btcusdt@trade",
                                on_message=on_message,
                                on_error=on_error,
                                on_close=on_close)
    ws.on_open = on_open
    
    # Flask 서버 실행
    app.run(port=5000)

Dim updateTimer As Double

Sub StartRealTimeUpdate()
    UpdatePrice
End Sub

Sub UpdatePrice()
    Dim http As Object
    Dim response As String
    Dim price As Double
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", "http://localhost:5000/price", False
    http.send
    
    response = http.responseText
    ' JSON 파싱 (간단한 방법)
    price = Mid(response, InStr(response, ":") + 1)
    price = Replace(price, "}", "")
    
    ' 셀에 업데이트
    Range("B2").Value = CDbl(price)
    Range("C2").Value = Now
    
    ' 1초 후 다시 실행
    updateTimer = Now + TimeValue("00:00:01")
    Application.OnTime updateTimer, "UpdatePrice"
    
    Set http = Nothing
End Sub

Sub StopRealTimeUpdate()
    On Error Resume Next
    Application.OnTime updateTimer, "UpdatePrice", , False
End Sub

Sub SendWeeklyReport()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim reportRange As Range
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    ' 리포트 범위 설정
    Set reportRange = Range("A1:F20")
    
    With OutMail
        .To = "team@company.com"
        .CC = ""
        .BCC = ""
        .Subject = "주간 판매 리포트 - " & Format(Date, "yyyy-mm-dd")
        .HTMLBody = "안녕하세요,<br><br>" & _
                    "이번 주 판매 리포트를 공유드립니다.<br><br>" & _
                    RangetoHTML(reportRange) & _
                    "<br>감사합니다."
        .Send  ' 또는 .Display (미리보기)
    End With
    
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Function RangetoHTML(rng As Range)
    ' 범위를 HTML 테이블로 변환
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
    
    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With
    
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.readall
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")
    
    TempWB.Close savechanges:=False
    Kill TempFile
    
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

Sub ProtectSheet()
    ActiveSheet.Protect Password:="your_password", _
                        UserInterfaceOnly:=True, _
                        AllowFiltering:=True, _
                        AllowSorting:=True
End Sub

Function CallAPI(url As String, method As String, _
                  Optional apiKey As String = "", _
                  Optional body As String = "") As String
    
    Dim http As Object
    Dim response As String
    
    On Error GoTo ErrorHandler
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    ' HTTP 요청 설정
    http.Open method, url, False
    
    ' 헤더 설정
    If apiKey <> "" Then
        http.setRequestHeader "Authorization", "Bearer " & apiKey
    End If
    http.setRequestHeader "Content-Type", "application/json"
    
    ' 요청 전송
    If body <> "" Then
        http.send body
    Else
        http.send
    End If
    
    ' 응답 확인
    If http.Status >= 200 And http.Status < 300 Then
        CallAPI = http.responseText
    Else
        CallAPI = "Error: " & http.Status & " - " & http.statusText
    End If
    
    Set http = Nothing
    Exit Function
    
ErrorHandler:
    CallAPI = "Error: " & Err.Description
End Function

' 사용 예시
Sub Example_CallAPI()
    Dim result As String
    
    result = CallAPI("https://api.example.com/data", "GET", "your_api_key")
    Range("A1").Value = result
End Sub

Function ParseJSON(jsonString As String, key As String) As String
    Dim startPos As Long
    Dim endPos As Long
    Dim value As String
    
    ' 키 찾기
    startPos = InStr(jsonString, """" & key & """:")
    If startPos = 0 Then
        ParseJSON = ""
        Exit Function
    End If
    
    ' 값 시작 위치
    startPos = InStr(startPos, jsonString, ":")
    startPos = startPos + 1
    
    ' 공백 건너뛰기
    Do While Mid(jsonString, startPos, 1) = " "
        startPos = startPos + 1
    Loop
    
    ' 값 타입 확인
    If Mid(jsonString, startPos, 1) = """" Then
        ' 문자열 값
        startPos = startPos + 1
        endPos = InStr(startPos, jsonString, """")
        value = Mid(jsonString, startPos, endPos - startPos)
    Else
        ' 숫자 또는 불린 값
        endPos = InStr(startPos, jsonString, ",")
        If endPos = 0 Then endPos = InStr(startPos, jsonString, "}")
        value = Trim(Mid(jsonString, startPos, endPos - startPos))
    End If
    
    ParseJSON = value
End Function

' 사용 예시
Sub Example_ParseJSON()
    Dim json As String
    Dim name As String
    Dim price As String
    
    json = "{""name"":""노트북"",""price"":1500000}"
    
    name = ParseJSON(json, "name")
    price = ParseJSON(json, "price")
    
    Range("A1").Value = name
    Range("B1").Value = price
End Sub

Sub ValidateAndLogData(dataRange As Range, logSheet As String)
    Dim cell As Range
    Dim errorCount As Long
    Dim logRow As Long
    Dim ws As Worksheet
    
    errorCount = 0
    Set ws = ThisWorkbook.Sheets(logSheet)
    logRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    
    ' 로그 헤더 작성 (처음 실행 시)
    If logRow = 2 Then
        ws.Range("A1:D1").Value = Array("시간", "셀 주소", "값", "오류 유형")
    End If
    
    For Each cell In dataRange
        ' 빈 값 체크
        If IsEmpty(cell.Value) Then
            ws.Cells(logRow, 1).Value = Now
            ws.Cells(logRow, 2).Value = cell.Address
            ws.Cells(logRow, 3).Value = "빈 값"
            ws.Cells(logRow, 4).Value = "필수 값 누락"
            logRow = logRow + 1
            errorCount = errorCount + 1
            cell.Interior.Color = RGB(255, 200, 200)  ' 연한 빨강
        
        ' 숫자 범위 체크 (예: 0~100)
        ElseIf IsNumeric(cell.Value) Then
            If cell.Value < 0 Or cell.Value > 100 Then
                ws.Cells(logRow, 1).Value = Now
                ws.Cells(logRow, 2).Value = cell.Address
                ws.Cells(logRow, 3).Value = cell.Value
                ws.Cells(logRow, 4).Value = "범위 초과 (0-100)"
                logRow = logRow + 1
                errorCount = errorCount + 1
                cell.Interior.Color = RGB(255, 200, 200)
            Else
                cell.Interior.Color = xlNone  ' 정상
            End If
        End If
    Next cell
    
    ' 결과 메시지
    If errorCount = 0 Then
        MsgBox "모든 데이터가 유효합니다! ✅", vbInformation
    Else
        MsgBox errorCount & "개의 오류가 발견되었습니다. " & _
               logSheet & " 시트를 확인하세요. ⚠️", vbExclamation
    End If
End Sub

' 사용 예시
Sub Example_ValidateData()
    Call ValidateAndLogData(Range("A2:A100"), "ErrorLog")
End Sub

Private lastCheckTime As Date
Private lastRowCount As Long

Sub MonitorDataChanges()
    Dim currentRowCount As Long
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Data")
    currentRowCount = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' 초기 실행
    If lastRowCount = 0 Then
        lastRowCount = currentRowCount
        lastCheckTime = Now
        Exit Sub
    End If
    
    ' 변경 감지
    If currentRowCount <> lastRowCount Then
        Dim changeType As String
        Dim changeCount As Long
        
        If currentRowCount > lastRowCount Then
            changeType = "추가"
            changeCount = currentRowCount - lastRowCount
        Else
            changeType = "삭제"
            changeCount = lastRowCount - currentRowCount
        End If
        
        ' 알림 표시
        MsgBox changeCount & "개의 행이 " & changeType & "되었습니다!" & vbCrLf & _
               "이전: " & lastRowCount & " 행" & vbCrLf & _
               "현재: " & currentRowCount & " 행", _
               vbInformation, "데이터 변경 감지"
        
        ' 로그 기록
        Call WriteLog("데이터 " & changeType & ": " & changeCount & "개 행")
        
        ' 상태 업데이트
        lastRowCount = currentRowCount
        lastCheckTime = Now
    End If
End Sub

' 1분마다 자동 체크
Sub StartMonitoring()
    Application.OnTime Now + TimeValue("00:01:00"), "MonitorDataChanges"
End Sub

이 글이 도움이 되었다면, 재능넷의 다른 지식 콘텐츠도 확인해보세요! 📚
여러분의 성장을 응원합니다! 🌱