다른파일 시트의 A, B열의 값을 현재시트로 불러오기

VBA
작성자
이재호
작성일
2021-04-05 20:37
조회
90

안녕하세요.

여러개의 엑셀파일에서 B열의 값이 2,600보다 처음으로 작아지는 행을 찾아 시간 값을 기록해야 합니다.

예를 들어 아래 그림을 보시면 B열의 값이 작아지다가 조금 커졌다가 또 작아지기를 반복하는데

처음으로 2,600보다 작아지기 시작하는 행은 616번이고, 이때의 시간값 552초를 찾아내는 것을 반복적으로 해야합니다.

 

VBA 동영상 강의를 듣고 여러개의 파일을 Open 하는 것까지는 했는데, 그 다음부터 어떻게 해야할지 진도가 안나가서 답답해서 도움을 요청드립니다 .

 

  1. 제일 좋은 방법은 파일을 Open하지 않더라도 다른 파일의 시트의 B열의 값을 조건으로 찾아서 A열의 시간값을 찾는것입니다.
  2. 만일 이게 어렵다면 다른 파일을 열지않고 A,B열의 값이라도 현재 열려있는 엑셀시트에 불러올 수 있다면 좋겠습니다. (이렇게 하면 반자동이지만 일일히 파일을 열어서 확인할 필요는 없기 때문입니다.)

고수님들의 도움 부탁드립니다.

 

전체 5

  • 2021-04-05 21:50
    채택된 답변

    저도 VBA는 그렇게 잘 하지는 못해서 파일을 열지않고 값을 불러오는 방법은 잘 모르겠어서 파일을 연 후 그 파일의 데이터를 가지고 코딩하는 방법을 초점으로 VBA를 작성했습니다.

    그런데 파일을 오픈하셨던 것 까지는 하셨는데 지금 주신 예제 파일을 보면 그냥 FileDialog창만 열어서 그 곳에 선택된 값들을 Split을 통해서 Vars에 넣는것 까지만 하셨더라구요 아마 해당 강의를 계속 보시면 Open하는 명령문도 같이 있을 겁니다. 그것까지는 동일하게 해 주신다고 보시면 됩니다. Open하는 명령문도 For Each를 써서 Open을 하게 될 텐데 그 For Each안에 중첩 For 을 써서 Open한 엑셀파일에서의 명령문을 작성해주셔야해요...

    지금 이렇게 말로만 적어드리는 이유는 지금 열고자 하시는 파일의 Flot Data형태가 전부 같은지, 시트의 이름이 어떻게 되어있는지 모르기 때문입니다.

    열고자 하시는 파일의 시트가 모두 1개이며, 파일의 Flot Data형태가 전부 동일하다는 가정이라면

    Sub Multiple_FileDialog()
    
    Dim FDG As FileDialog
    Dim SELECTED As Integer: Dim i As Integer
    Dim ReturnStr As String
    Dim Vars As Variant: Dim Var As Variant
    Dim NewFile As Workbook '//새로 열은 파일
    Dim NewFileRow As Long '// 새로 열은 파일의 마지막 행 번호
    Dim j As Long '//For문을 위한 변수
    
    SelectAgain:
    
    Set FDG = Application.FileDialog(msoFileDialogFilePicker)
    With FDG
      .Title = "파일을 선택하세요. (HRR 또는 DEVC)"
      .Filters.Add "엑셀파일", "*.XLS, *.XLSX, *.XLSM"
      .InitialView = msoFileDialogViewList
      .InitialFileName = ThisWorkbook.Path
      '.AllowMultiSelect = False '한개 파일만 불러올때
      .AllowMultiSelect = True '다수의 파일을 불러올때
        
      SELECTED = .Show
      
      
      If SELECTED = 0 Then
        MsgBox "선택되지 않았습니다. 다시 선택하세요"
        GoTo SelectAgain:
      ElseIf SELECTED = -1 Then
        For i = 1 To .SelectedItems.Count - 1 '다수의 파일을 불러올때
      '   ReturnStr .SelectedItems(1) '한개파일만 불러올때
          ReturnStr = ReturnStr & .SelectedItems(i) & ", "
       '   MsgBox ReturnStr '중간에 확인하고 싶을때
          Next
          ReturnStr = ReturnStr & .SelectedItems(.SelectedItems.Count)
          
      
          MsgBox ReturnStr
      End If
        
    End With
    
    Vars = Split(ReturnStr, ", ")
    For Each Var In Vars
    'Application.Workbooks.Open (Var)
    
    Set NewFile = Application.Workbooks.Open(Var)
    
    NewFileRow = NewFile.Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
    
    For j = 1 To NewFileRow
    
        If NewFile.Worksheets(1).Cells(j, 2).Value < 2600 Then
        '//여기까지가 해당 파일의 B열에서 2600이하의 값을 찾는 코드입니다. 이 이후에는 결과를 어떻게 만들어 드려야 하는지 모르기에 알아서 작성해주셔야 하는 부분입니다.
        End If
    Next
    
    Next
    
    
    End Sub

    새로 두 가지 변수를 더 추가하였습니다.

    파일을 열고 그 파일에 대하여 작업을 하기 위한 변수 하나와 For문을 위한 변수 하나 더 추가하였습니다.

    화이팅하십쇼^^


    • 2021-04-06 11:05

      감사합니다. 동영상강의를 보고 답변해주신 부분까지 만들었었는데요. 그 다음부터를 어떻게 해야할지 몰라서 질문을 드렸었습니다. 이것 저것 찾아서 다시한번 해보고 안되면 다시 질문을 드려야 할것 같습니다. ^^


  • 2021-04-06 11:06

    그런데 제가 위에 질문드린 1,2번은 해결이 안되었는데요. 1번은 어려울수도 있지만 2번은 가능할것 같은데요. 어떻게 해야하는지 방법만이라도 알려주시면 제가 찾아서 해볼수 있을텐데요. ㅠㅡ


    • 2021-04-06 17:11

      기존에 답변 드렸던 내용과 거의 비슷하게 매크로가 구성됩니다.

      예제 파일을 받아서 보면 Split이후에는 없습니다.

      그래서 저는 그 이후의 방향성에 대해서 적어드린 것입니다.

      여기서 문제는 열고자 하시는 파일이 어떤식으로 되어있는지 모른다는 것입니다.

      시트가 하나만 존재하는지 그리고 시트가 여러개라면 값을 불러오고자 하는 시트는 이름이 동일한건지 위치가 동일한건지 등등 여러가지 변수가 있어서 그 이후의 매크로는 알아서 작성하셔야한다고 적었습니다...

      만약 열어서 값을 비교할 파일의 시트가 하나고 모두 동일하게 A행에는 시간 B열에는 비교할 값이 들어있다고 가정하고 매크로를 만들면

      Sub Multiple_FileDialog()
      
      Dim FDG As FileDialog
      Dim SELECTED As Integer: Dim i As Integer
      Dim ReturnStr As String
      Dim Vars As Variant: Dim Var As Variant
      Dim NewFile As Workbook '//새로 열은 파일
      Dim NewFileRow As Long '// 새로 열은 파일의 마지막 행 번호
      Dim j As Long '//For문을 위한 변수
      Dim NSheet As Worksheet '//2600이하의 값을 표현할 새로운 시트
      Dim Sh As Worksheet '//For문을 위한 변수
      Dim k As Long '//새로운 시트에 값을 넣을때 행을 위한 변수
      SelectAgain:
      
      k = 1
      
      Application.DisplayAlerts = False
      
      For Each Sh In ThisWorkbook.Worksheets '//New Sheet라는 이름을 가진 시트 없에기
      
      If Sh.Name = "New Sheet" Then
          Sh.Delete
      End If
      
      Next
      
      Set NSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)) '//새로운 값을 받을 시트를 다시 생성
      NSheet.Name = "New Sheet"
      
      Set FDG = Application.FileDialog(msoFileDialogFilePicker)
      With FDG
        .Title = "파일을 선택하세요. (HRR 또는 DEVC)"
        .Filters.Add "엑셀파일", "*.XLS, *.XLSX, *.XLSM"
        .InitialView = msoFileDialogViewList
        .InitialFileName = ThisWorkbook.Path
        '.AllowMultiSelect = False '한개 파일만 불러올때
        .AllowMultiSelect = True '다수의 파일을 불러올때
          
        SELECTED = .Show
        
        
        If SELECTED = 0 Then
          MsgBox "선택되지 않았습니다. 다시 선택하세요"
          GoTo SelectAgain:
        ElseIf SELECTED = -1 Then
          For i = 1 To .SelectedItems.Count - 1 '다수의 파일을 불러올때
        '   ReturnStr .SelectedItems(1) '한개파일만 불러올때
            ReturnStr = ReturnStr & .SelectedItems(i) & ", "
         '   MsgBox ReturnStr '중간에 확인하고 싶을때
            Next
            ReturnStr = ReturnStr & .SelectedItems(.SelectedItems.Count)
            
        
            MsgBox ReturnStr
        End If
          
      End With
      
      Vars = Split(ReturnStr, ", ")
      For Each Var In Vars
      'Application.Workbooks.Open (Var)
      
      Set NewFile = Application.Workbooks.Open(Var) '//FileDialog에서 선택한 파일 하나씩 열기
      
      NewFileRow = NewFile.Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row '//새로 열은 파일 B행의 마지막 행 찾기
      
      For j = 1 To NewFileRow
          If NewFile.Worksheets(1).Cells(j, 2).Value < 2600 Then '//A행에서 2600보다 작은 값 찾기
          NSheet.Cells(k, 1).Value = NewFile.Worksheets(1).Cells(j, 1).Value '//2600보다 작을 때 해당 행의 A행 값 NSheet에 넣기
          k = k + 1
          Exit For
          End If
      Next
      
      NewFile.Close '//열었던 파일 닫기
      
      Next
      
      Application.DisplayAlerts = True
      
      End Sub

      이런식으로 됩니다.

      원하시는 서식에 따라, 열고자 했던 파일의 개수에 따라 그에 따른 변수와 매크로의 형태가 바뀌기에 처음 댓글과 좀 달라집니다.

      이후 서식은 원하시는 방향으로 다듬으시면 됩니다.


      • 2021-04-09 11:43

        수정해주신 내용을 기반으로 1,2번에 해당하는 파일을 만들었습니다. 많이 배웠습니다. 감사합니다. ^^


전체 2,796
번호 카테고리 제목 작성자 작성일 추천 조회
2785 VBA
New VBA로 SQL 형식의 관계형 데이터베이스 구축
루카스 | 2021.04.17 | 추천 0 | 조회 11
루카스 2021.04.17 0 11
2784 함수/공식
New 엑셀 버튼(?)만들기 관련 질문드려요! (2) 답변완료
Philiip Rho | 2021.04.17 | 추천 1 | 조회 34
Philiip Rho 2021.04.17 1 34
2783 기능/도구
New 이중유효성목록상자의 콤보박스 활용 관련 질문입니다. (1)
Butterfly | 2021.04.17 | 추천 1 | 조회 18
Butterfly 2021.04.17 1 18
2782 함수/공식
New 텍스트 나누기 (2)
정기🎵 | 2021.04.17 | 추천 1 | 조회 27
정기🎵 2021.04.17 1 27
2781 VBA
New [VBA] 단어진도표를 활용한 각 개인별 단어엑셀파일 만들기 첨부파일 (1)
가람 | 2021.04.17 | 추천 0 | 조회 18
가람 2021.04.17 0 18
2780 VBA
New vba버튼 표 만들기 요청의건 첨부파일
작은소녀 | 2021.04.16 | 추천 0 | 조회 39
작은소녀 2021.04.16 0 39
2779 VBA
New [vba] 엑셀 표에 있는 지정된 행에 있는 이름의 파일을 순서대로 열고 닫는 코드만들기 (2) 답변완료
가람 | 2021.04.16 | 추천 1 | 조회 31
가람 2021.04.16 1 31
2778 함수/공식
New xFilter 유사일치 적용 가능한 함수 (1)
정길이 | 2021.04.16 | 추천 -1 | 조회 24
정길이 2021.04.16 -1 24
2777 문서서식
New 원가계산이 너무 어렵습니다.. (1)
kai | 2021.04.16 | 추천 -1 | 조회 62
kai 2021.04.16 -1 62
2776 함수/공식
New 값 중복 제외 함수를 알고싶습니다. (1)
오리 | 2021.04.16 | 추천 0 | 조회 41
오리 2021.04.16 0 41
2775 VBA
New 'naverfinancehistory' VBA 관련 질문 남겨보아요 (1)
구릉이 | 2021.04.16 | 추천 0 | 조회 18
구릉이 2021.04.16 0 18
2774 기능/도구
New ' 표시 (1)
유리 | 2021.04.16 | 추천 0 | 조회 31
유리 2021.04.16 0 31
2773 함수/공식
New 중복된 데이터 제거 후 불러오기 첨부파일 (3) 답변완료
seisai | 2021.04.15 | 추천 0 | 조회 74
seisai 2021.04.15 0 74
2772 차트/그래프
New 가로형 차트에서 세로축의 항목이 2개 이상일 때 텍스트 방향 변경 어떻게 하나요? (1)
k**** | 2021.04.15 | 추천 0 | 조회 18
k**** 2021.04.15 0 18
2771 함수/공식
New 중복값 제거 및 축출 함수 첨부파일 (2)
3victor | 2021.04.15 | 추천 0 | 조회 48
3victor 2021.04.15 0 48
2770 기능/도구
New 엑셀 셀안에 내용을 메모삽입후 셀복사+삽입된 메모에 붙여넣기 가능할까요? (3)
엑셀공부 | 2021.04.15 | 추천 1 | 조회 42
엑셀공부 2021.04.15 1 42
2769 함수/공식
New 엑셀 왕초보 질문 2개 영역의 데이터 목록을 순차적으로 자동배치 문의 드립니다 첨부파일 (4)
atoz | 2021.04.15 | 추천 -3 | 조회 34
atoz 2021.04.15 -3 34
2768 함수/공식
추천인 등수와 신규회원 데이터 정리 방법을 어떻게 시작해야할지 모르겠어요ㅠ (2) 답변완료
뿌드득 | 2021.04.14 | 추천 0 | 조회 37
뿌드득 2021.04.14 0 37
2767 함수/공식
다른 시트의 항목값중 한개 입력하면 여러항목 같이 불러오기 첨부파일 (1)
정미니 | 2021.04.14 | 추천 0 | 조회 26
정미니 2021.04.14 0 26
2766 함수/공식
지정한 기간+지정한 조건 사이를 만족하는 값의 갯수를 구할떄 함수질문드려요 (2)
냥냥 | 2021.04.14 | 추천 1 | 조회 32
냥냥 2021.04.14 1 32
2765 함수/공식
다중 조건과 텍스트 추출 첨부파일 (4)
강혜원 | 2021.04.14 | 추천 0 | 조회 103
강혜원 2021.04.14 0 103
2764 VBA
vba 질문 (뒤로가기 버튼 구현) (2)
자신 | 2021.04.13 | 추천 0 | 조회 48
자신 2021.04.13 0 48
2763 VBA
여러 항목별 중 반복되는 특정시각에 맞는 항목 찾기. 논리적 규칙? 방향을 못잡겠습니다. (4)
6ra70 | 2021.04.13 | 추천 0 | 조회 45
6ra70 2021.04.13 0 45
2762 함수/공식
XLOOKUP 함수 활용한 세트구성 원가 합 구하기 첨부파일 (1)
고래 | 2021.04.13 | 추천 0 | 조회 49
고래 2021.04.13 0 49
2761 함수/공식
텍스트 및 수식 불러오기 함수 요청의 건 첨부파일 (3) 답변완료
seisai | 2021.04.13 | 추천 0 | 조회 56
seisai 2021.04.13 0 56
2760 함수/공식
리본메뉴 단축버튼 만들기 첨부파일 (5) 답변완료
SAM | 2021.04.13 | 추천 0 | 조회 47
SAM 2021.04.13 0 47
2759 피벗테이블
피벗테이블 - 계산필드 첨부파일 (5)
나도오빠두한다 | 2021.04.13 | 추천 0 | 조회 46
나도오빠두한다 2021.04.13 0 46
2758 기능/도구
엑셀2007 필터링 및 데이터 붙여넣기, 삭제 관련 첨부파일 (2)
엑셀초보 | 2021.04.13 | 추천 0 | 조회 25
엑셀초보 2021.04.13 0 25
2757 함수/공식
어떤 수식을 써야할지 감이 안잡힙니다... 3탄... 첨부파일 (4) 답변완료
벳남외노자 | 2021.04.13 | 추천 1 | 조회 78
벳남외노자 2021.04.13 1 78
2756 함수/공식
경력의 80페센트를 구하는 수식을 알고 싶습니다. (1)
eggs**** | 2021.04.13 | 추천 0 | 조회 59
eggs**** 2021.04.13 0 59