엑셀 데이터를 다른 창 입력란에 자동으로 일괄 붙여넣기

 

 

 

엑셀 시트에 데이터를 생성, 정리해놓고

웹브라우저나 DB시스템 등에 자료를 입력해야하는 상황에서

직접 손으로 입력하기에는 너무 힘들기 때문에

VBA를 이용해서 자동화할 수 있습니다.

 

주로 SendKeys를 이용해서

데이터를 복사하고 붙여넣은 다음 Sleep으로 잠시 대기하고

다음 행의 데이터를 붙여넣는 방식입니다.

 

 

#If VBA7 Then
    Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
    Declare PtrSafe Function IsIconic Lib "user32" (ByVal hwnd As LongPtr) As Long
    Declare PtrSafe Function ShowWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal nCmdShow As Long) As Long
    Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
    Dim hwnd As LongPtr
#Else
    Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
    Declare Function IsIconic Lib "user32" (ByVal hwnd As Long) As Long
    Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
    Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
    Dim hwnd As Long
#End If

Const SW_RESTORE = 9

'   ********
'   주의사항!!
'
'   반드시 붙여넣을 대상 윈도우창에서 '첫번째 입력칸'을 선택한 상태에서 
'   엑셀창에는 붙여넣을 내용이 입력된 첫번째 셀을 선택하고 실행할 것!
'
'   ********

Sub A1_PasteOver()

    Dim sht As Worksheet
    Dim r As Range
    Dim str$, usr$, sTitle$, targetCol$, i%, firstrow&, lastrow&, TabCount%
    
    '크롬창 찾기
    'sTitle = "제목 없는 설문지 - Chrome"
    'hwnd = FindWindow(vbNullString, sTitle) '윈도우 창이름으로 찾기
    'sTitle = "Chrome_WidgetWin_1"
    'hWnd = FindWindow(sTitle, vbNullString)   '클래스 이름으로 찾기
    hwnd = GetPreviousVisibleWindow(2)
    If hwnd = 0 Then MsgBox "대상 윈도우창 찾기 실패", vbCritical: Exit Sub
    
    firstrow = ActiveCell.Row
    Set sht = ActiveSheet
    targetCol = Split(ActiveCell.Address(True, True), "$")(1) '대상 컬럼 기호  (Ex: F열)
    lastrow = sht.Cells(sht.Rows.Count, targetCol).End(xlUp).Row
    
    '확인
    sTitle = GetWindowTitle$(hwnd)
    usr = InputBox("대상 윈도우 창: [" & sTitle & "] 창" & vbNewLine & _
            "(첫번째 입력란이 선택되어 있어야 함)" & vbNewLine & vbNewLine & _
            "소스 데이터: 현재 시트의 " & targetCol & firstrow & "부터 " & targetCol & lastrow & "까지" & vbNewLine & vbNewLine & _
            "각 데이터를 입력한 후 <TAB>키 입력횟수는?" & vbNewLine & "(데이터 사이의 입력필드 개수에 따라 다름)?", _
            "엑셀 데이터 연속 붙여넣기 자동화", 1)
    usr = Trim(usr)
    If Len(usr) = 0 Or Not IsNumeric(usr) Then Exit Sub
    TabCount = CInt(usr)
    
    '대상 윈도우창 활성화
    If IsIconic(hwnd) Then ShowWindow hwnd, SW_RESTORE
    SetForegroundWindow hwnd
    'AppActivate sTitle, True
    
    '입력할 셀 순환
    For Each r In sht.Range(targetCol & firstrow & ":" & targetCol & lastrow)
        
        '내용을 클립보드에 복사
        str = Trim(r.Text)
        SetCB str
        Sleep 100
        
        '기존 입력 내용이 사라지도록 먼저 전체선택
        'Application.SendKeys "^a", True
        'Sleep 100
        
        '입력칸에 붙여넣기
        Application.SendKeys "^v", True
        Sleep 100
                
        'TAB키를 x번 눌러 다음 입력칸으로 이동
        'TabCount = 1
        For i = 1 To TabCount
            Application.SendKeys "{TAB}", True
            Sleep 100
        Next i
        
    Next r
    
End Sub

 
'https://blog.naver.com/program114/220401934980
'// 클립보드에 텍스트 쓰기
Public Function SetCB(ByRef sText As String) As Boolean ' ### 리턴값: 성공 여부
    On Error GoTo nErr
    Dim Clipboard As Object
'    Microsoft Forms 2.0 Object Library
    Set Clipboard = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    Clipboard.SetText sText
    Clipboard.PutInClipboard
    SetCB = True
nErr:
End Function


'// 클립보드의 텍스트 읽기
Public Function GetCB$()
    On Error GoTo nErr
    Dim Clipboard As Object
'    Microsoft Forms 2.0 Object Library
    Set Clipboard = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    Clipboard.GetFromClipboard
    GetCB = Clipboard.GetText
nErr:
End Function

Sub A2_RemoveBlankLines()
    
    Dim rng As Range
    Dim l As Long
    
    Set rng = ActiveWindow.Selection
    For l = rng.Rows.Count To 1 Step -1
        If rng.Cells(l, 1).Text = "" Then
            rng.Cells(l, 1).Delete Shift:=xlUp
        End If
    Next l
    
End Sub

Sub A3_DeleteEmptyCells()

    On Error Resume Next ' 선택 영역에 빈 셀이 없을 경우 발생하는 에러 방지
    Selection.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
    On Error GoTo 0
    
End Sub

 

사례 1:

 

먼저 웹브라우저에 아래와 같은 설문양식이 있습니다.

각 질문에 차례로 답변을 입력해야 합니다.

 

첫번째 입력란을 선택한 상태에서 엑셀창에서 데이터를 준비합니다.

즉, 웹브라우저 창위에 엑셀창이 있어야 합니다. 

 

 

엑셀 시트에 위에 웹브라우저창에 입력할 내용을 아래와 같이 A1:A10에 정리했습니다.

 

 

Alt+F8을 누르고 PasteOver 매크로를 실행합니다.

 

RemoveBlankLines는 모든 빈 줄을 삭제하고 아래줄을 위로 당깁니다.

DeleteEmptyCells는 모든 빈 셀을 삭제하고 아래 셀을 위로 당깁니다.

 

그러면 아래와 같은 확인창이 뜹니다.

 

대상 윈도우창 제목을 확인하세요. 대상 윈도우창이 맞아야 엑셀 데이터가 해당 창에 붙여넣어집니다.

※ 주의) 반드시 첫번째 입력란이 선택되어 있고 엑셀창 이전 윈도우창이어야 합니다.

 

소스 데이터는 엑셀 시트에 현재 선택된 행부터 맨 아래행까지입니다. 

※ 주의)  엑셀 시트에서 데이터 시작 셀을 선택해야 합니다.

 

그리고 입력 데이터 사이사이마다 <TAB>키를 몇번 입력할 지 숫자를 입력합니다.

 

이 경우는 한 문장 입력하고 탭키를 한 번만 치면 다음 입력란으로 이동하기 때문에 1을 입력합니다.

 

확인을 누르면 아래와 같이 자동으로 10문장을 답란 칸에 차례로 붙여넣습니다.

 

 

 

대상 창의 입력 환경에 따라 입력할 때 수정할 부분은 아래 부분을 고치면 되겠습니다.

예를 들어 기존 데이터를 지우고 붙여넣는다면 ^a  즉 Ctrl+a를 누르고 모두 선택한 상태에서 붙여넣게 합니다.

SendKeys에서 Ctrl은 ^, Alt 는 %, Shift는  + 기호를 사용합니다.  SendKeys "+a" 는  Shift+a 를 입력해줍니다.

또는 지연 시간을 Sleep 100 을 수정합니다. 1000이 1초에 해당합니다.

'입력할 셀 순환
    For Each r In sht.Range(targetCol & firstrow & ":" & targetCol & lastrow)
        
        '내용을 클립보드에 복사
        str = Trim(r.Text)
        SetCB str
        Sleep 100
        
        '기존 입력 내용이 사라지도록 먼저 전체선택
        'Application.SendKeys "^a", True
        'Sleep 100
        
        '입력칸에 붙여넣기
        Application.SendKeys "^v", True
        Sleep 100
                
        'TAB키를 x번 눌러 다음 입력칸으로 이동
        'TabCount = 1
        For i = 1 To TabCount
            Application.SendKeys "{TAB}", True
            Sleep 100
        Next i
        
    Next r

 

SendKeys 에서 활용 가능한 특수키는 아래 링크를 참조하세요.

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/sendkeys-statement

 

 

그리고 참고로 아래는 이전 윈도우창을 찾을 때 사용하는 코드입니다:

현재 엑셀 창 바로 아래에 웹브라우저 등의 윈도우 창이 붙여넣을 대상 윈도우창인데 이 창을 찾기 위한 작업입니다.

EnumWindows로 윈도우창 목록을 배열로 만들고

바로 이전 윈도우의 hWnd 값을 찾아내고

getWindowText로 윈도우 창 타이틀을 가져옵니다.

Option Explicit

#If VBA7 Then
    Declare PtrSafe Function EnumWindows Lib "user32" _
        (ByVal lpEnumFunc As LongPtr, ByVal lParam As LongPtr) As Long
    Declare PtrSafe Function IsWindowVisible Lib "user32" _
        (ByVal hwnd As LongPtr) As Long
    Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
        (ByVal hwnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As Long
#Else
    Declare Function EnumWindows Lib "user32" _
        (ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long
    Declare Function IsWindowVisible Lib "user32" _
        (ByVal hwnd As Long) As Long
    Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
        (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
#End If

#If VBA7 Then
    Public gHWNDs() As LongPtr
#Else
    Public gHWNDs() As Long
#End If

Dim gCount As Long

#If VBA7 Then
Public Function EnumWindowsProc(ByVal hwnd As LongPtr, ByVal lParam As LongPtr) As Long
#Else
Public Function EnumWindowsProc(ByVal hwnd As Long, ByVal lParam As Long) As Long
#End If

    ' 보이는 창만 수집
    If IsWindowVisible(hwnd) <> 0 And Len(GetWindowTitle(hwnd)) > 0 And hwnd <> Application.hwnd Then
        gCount = gCount + 1
        ReDim Preserve gHWNDs(1 To gCount)
        gHWNDs(gCount) = hwnd
    End If

    EnumWindowsProc = 1 ' 계속 열거
End Function

#If VBA7 Then
Public Function GetPreviousVisibleWindow(ByVal n As Long) As LongPtr
#Else
Public Function GetPreviousVisibleWindow(ByVal n As Long) As Long
#End If

    gCount = 0
    Erase gHWNDs

    ' 모든 창 열거
    EnumWindows AddressOf EnumWindowsProc, 0

    ' n번째 창 반환
    If gCount >= n Then
        GetPreviousVisibleWindow = gHWNDs(n)
    Else
        GetPreviousVisibleWindow = 0 ' n번째 창 없음
    End If
End Function

#If VBA7 Then
Public Function GetWindowTitle(tHwnd As LongPtr) As String
#Else
Public Function GetWindowTitle(tHwnd As Long) As String
#End If
    Dim title As String * 256
    Dim lenTitle As Long
    
    If tHwnd = 0 Then
        GetWindowTitle = ""
        Exit Function
    End If

    lenTitle = GetWindowText(tHwnd, title, 256)
    GetWindowTitle = Left$(title, lenTitle)
    GetWindowTitle = Replace(GetWindowTitle, Chr(0), "") 'NULL문자 제거
End Function

Private Sub getPreviousVisibleWindow_Test()
#If VBA7 Then
    Dim hwnd2 As LongPtr
#Else
    Dim hwnd2 As Long
#End If
    Dim title2 As String

    'Module2.ListAllWindows
    
    ' 2번째 보이는 창
    hwnd2 = GetPreviousVisibleWindow(2)
    title2 = GetWindowTitle(hwnd2)

    If hwnd2 <> 0 Then

        Debug.Print "======= 2번째 보이는 창 hwnd = "; hwnd2
        Debug.Print "타이틀 = "; title2
    Else
        Debug.Print "2번째 보이는 창 없음"
    End If
End Sub

 

 

VBA가 포함된 샘플 파일 첨부합니다.

 

PasteOver1.xlsm

0.03MB

 

 

 

사례2:

 

아래한글 문서에 아래와 같은 표 양식이 있습니다. 성명란에 이름을 쭉 입력하려고 합니다.

 

방문자 명단.hwp

0.03MB

 

 

아래한글 도구 메뉴 > 환경설정에서 표 안에서 <TAB>키로 다음 셀로 이동할 수 있게 설정되어 있어야 합니다.

 

한글창 바로 위에 엑셀창이 있고

엑셀시트에는 아래처럼 이름이 정리되어 있습니다.

Alt+F8을 누르고 PasteOver 매크로를 실행합니다.

 

 

이 경우는 <Tab>키를 4번 치면 다음 이름칸에 도착하므로 4를 입력합니다.

 

확인을 누른 결과입니다.

 

다음 국적 데이터를 선택하고 한 번 더 실행하면 되겠습니다.

아니면 코드를 약간 더 수정해서 여러열을 붙여넣게 할 수 있습니다.

 

물론 아래한글의 경우 한글 HWP 컨트롤을 이용해서 더 정확하게 자동화할 수 있지만

간단히 SendKeys로 이렇게 일괄 입력 작업을 처리할 수 있습니다.

 

 

✅🔎 활용 예시:

- 엑셀 데이터를 웹브라우저 설문조사 입력란에 일괄로 붙여넣기

- 학원/학교에서 학생 평가자료나 평가문을 출력 양식 프로그램에 일괄로 입력하기

- 아래한글 메일머지 대신 봉투 주소/수신자 등 일괄 입력

- 대규모 DB작업

- 실험 데이터 일괄 입력

- 기타 일괄 입력이 어려운 프로그램창에 엑셀데이터 일괄 입력

 

 

🔍🛜 향후 개선 사항:

- 키보드 매크로에 가까운 방법이라 한계가 있음

- 데이터가 정확히 해당란에 입력되는지 확인 필요. 중간에 누락된 명단이 있다면 입력 오류 발생.

- 웹의 경우 Json 이나 POST 데이터로 서버에 보내는 방식이 더 정교한 입력방식임

- 시스템의 상황에 따라 키보드로 붙여넣을 때 엉뚱한 창에 데이터가 붙여넣어질 수 있음

 

 

✔️❌주의사항:

- 위 내용은 데이터 일괄 입력방법에 대한 순수한 교육적 목적의 자료임

- 불법적인 목적으로 오남용하지 않아야 함.

- 책임은 사용자에게 있음

- 개인적인 용도와 작업에만 사용해야 함