메모장 내용을 excel 에 불러와서 값을 추출하고 싶은데요.

함수/공식
작성자
tu1541
작성일
2021-09-29 13:28
조회
105
엑셀버전 : 엑셀2016

운영체제 : 윈도우10

안녕하세요 아래 VBA로 폴더 선택하여 폴더 안에 txt파일을 excel로 불러왔습니다.

Sub 불러오기()
'UpdatebyExtendoffice6/7/2016
Dim xWb As Workbook
Dim xToBook As Workbook
Dim xStrPath As String
Dim xFileDialog As FileDialog
Dim xFile As String
Dim xFiles As New Collection
Dim i As Long
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder [Kutools for Excel]"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
If Right(xStrPath, 1) <> "\" Then xStrPath = xStrPath & "\"
xFile = Dir(xStrPath & "*.txt")
If xFile = "" Then
MsgBox "No files found", vbInformation, "Kutools for Excel"
Exit Sub
End If
Do While xFile <> ""
xFiles.Add xFile, xFile
xFile = Dir()
Loop
Set xToBook = ThisWorkbook
If xFiles.Count > 0 Then
For i = 1 To xFiles.Count
Set xWb = Workbooks.Open(xStrPath & xFiles.Item(i))
xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xWb.Name
On Error GoTo 0
xWb.Close False
Next
End If
End Sub

이후 질문 > txt시트에 No의 맨 뒷자리 숫자와 Total시트에 G4에 숫자가 같다면 txt시트에 Total 값을 Total시트 Total Die에 찍고 싶습니다. (ex)1이면 7200 , 2이면 7100)

<txt시트>

<Total시트>

 

회원등급 : 새싹등급
포인트 : 78 EP
총질문 : 6 개 (마감율 : 67%)
채택답변 : 0 개
전체 9

  • 2021-09-29 15:39

    Private Sub AutomationStart()
    Dim i As Integer, lastRow As Integer
    Dim dict As Object
    Dim vRng As Variant

    Set dict = CreateObject("Scripting.Dictionary")

    lastRow = Sheets("txt시트").Cells(Rows.Count, 3).End(xlUp).Row
    vRng = Sheets("txt시트").Range("D2:F" & lastRow).Value

    ' # dictionary에 value insert
    For i = 1 To UBound(vRng, 1)
    If InStr(CStr(vRng(i, 1)), "-") Then
    Dim No As Integer, Total As Integer

    No = CInt(Split(CStr(vRng(i, 1)), "-")(2))
    Total = CInt(vRng(i, 3))

    If Not dict.Exist(No) Then
    dict.Add No, Total
    End If
    End If
    Next

    ' # 여기서 부턴 dictionary에 저장된 값을 Total 시트 매치하여 붙여넣는 부분
    Dim lastColumn As Integer
    lastColumn = Sheets("Total시트").Cells(6, Columns.Count).End(xlToLeft).Column

    if lastColumn < 7 Then: MsgBox "G열 데이터 미작성 오류": Exit Sub

    For i = 7 To lastColumn
    With Sheets("Total시트")
    Dim key As Integer
    key = CInt(.Cells(4, i))

    If dict.Exist(key) Then
    .Cells(6, i) = dict(key)
    End If
    End With
    Next

    MsgBox "끝", vbInformation, "끝"

    Set dict = Nothing
    Erase vRng
    End Sub

     

     

    질문에 정확한 raw data를 확인 할 수없어 임의로 작성 했습니다.

    데이터 범위를 알 수 없어 유동적으로 RawData 범위를 산정했고

    Dictionary 이용해서 해당 Data Key가 있으면 작성되게 하였습니다.


    • 2021-09-29 15:40

      저 Raw Data가 오름차순 혹은 내림차순 정렬이 되어 나온다면 굳이 위와같이 dictionary로 하지않고 Collection 사용하여 리스트에 담아 하는 방법도 가능하겠습니다.


    • 2021-09-29 16:17

      첨부 파일에 test로 파일 올렸습니다. (혹시 VBA말고 함수로는 방법이 없을까요? 해당 값 말고도 다른 값들도 추출 해야 하는 부분이 많아서요.)

      If Not dict.Exist(No) Then > 이 부분에서 438 런타임 ERROR가 발생합니다...

      첨부파일 : test.xlsm


      • 2021-09-29 16:24
        채택된 답변

        Private Sub AutomationStart()

        Dim i As Integer, lastRow As Integer
        Dim dict As Object
        Dim vRng As Variant

        Set dict = CreateObject("Scripting.Dictionary")

        lastRow = Sheets("txt").Cells(Rows.Count, 4).End(xlUp).Row
        vRng = Sheets("txt").Range("D2:F" & lastRow).Value

        ' # dictionary에 value insert
        For i = 1 To UBound(vRng, 1)
        If InStr(CStr(vRng(i, 1)), "-") Then
        Dim No As Integer, Total As Integer

        No = CInt(Split(CStr(vRng(i, 1)), "-")(2))
        Total = CInt(vRng(i, 3))

        If Not dict.Exists(No) Then
        dict.Add No, Total
        End If
        End If
        Next

        ' # 여기서 부턴 dictionary에 저장된 값을 Total 시트 매치하여 붙여넣는 부분
        Dim lastColumn As Integer
        lastColumn = Sheets("Total").Cells(4, Columns.Count).End(xlToLeft).Column

        If lastColumn < 7 Then: MsgBox "G열 데이터 미작성 오류": Exit Sub

        For i = 7 To lastColumn
        With Sheets("Total")
        Dim key As Integer
        key = CInt(.Cells(4, i))

        If dict.Exists(key) Then
        .Cells(6, i) = dict(key)
        End If
        End With

        Next

        MsgBox "끝", vbInformation, "끝"

        Set dict = Nothing
        Erase vRng
        End Sub

         

         

        메모장으로 코드를 작성 하다 보니... 오타 여서 작동이 안됐습니다.

        지금 작성한 이 코드로 한번 해보세요

        Raw Data가 지금처럼 'x-x-no' 형태로 일정하다면 문제없이 작동할것입니다.


        • 2021-09-29 16:37

          추가로 VBA 버튼 매크로 말고 함수식으로 사용 하고 싶다면 아래와 같이 사용하시면 되겠습니다.

           

          모듈에 밑에 소스 붙여넣기 후 Excel 에선

          '=MatchValue(I4)' 와 같이 수식을 입력
          값이 있는 경우 Total 값 반환,
          없는 경우 -1 반환

           

          Public Function MatchValue(rng As Range) As Integer
          Dim vRng As Variant, lastRow As Integer, i As Integer
          Dim find As Integer, result As Integer

          result = -1
          find = CInt(rng.Value)

          lastRow = Sheets("txt").Cells(Rows.Count, 4).End(xlUp).Row
          vRng = Sheets("txt").Range("D2:F" & lastRow).Value

          For i = 1 To UBound(vRng, 1)
          Dim Header As String
          Header = CStr(vRng(i, 1))

          If InStr(Header, "-") Then
          Dim v As String
          v = IIf(UBound(Split(Header, "-")) = 2, Split(Header, "-")(2), vbNullString)

          If (Not v = vbNullString) And CInt(Split(Header, "-")(2)) = find Then

          result = CInt(vRng(i, 3))
          Exit For
          End If
          End If
          Next

          Erase vRng
          MatchValue = result
          End Function


          • 2021-09-29 17:36

            와...함수 식 이거 잘 돼네요

            위 소스 이해해보고 싶은데 주석으로 설명 적어주시면 감사하겠습니다!!

            그리고 첨부파일 있는데 제가 하려고 하는 부분 적어 놨는데 함수 식으로 이 부분 가능할까요?

            첨부파일 : test.xlsm


            • 2021-09-29 17:54

              모듈 아래에 MatchValue1 을 새로 선언해서 result = CInt(vRng(i, 4)) 해당 숫자 가지고 올 열로 바꿔주니까 됐습니다.

              감사합니다!

              이제..위에 첨부 파일에 있는 불러올 때 같은 값들을 합하여 불러 오는 부분이 있는데...너무 어렵네요...ㅠㅠ


              • 2021-09-29 21:46

                vRng = Sheets("txt").Range("D2:F" & lastRow).Value

                vRng(y, x)로 생각하시면 됩니다

                D:J로 하면 x는 6까지 가능하구요. 이건 직접 테스트 해보세요

                 

                합치는건 새로 함수식 여러개 만드셔서 =MatchValue1(rng) + MatchValue2(rng) 요렇게 합하시면 되겠네요


                • 2021-09-30 09:42

                  위 내용으로 다 완성했습니다. 정말 감사합니다!

                  한가지 만 더 질문 드려도 될까요?

                  합 칠때 알려주신 대로 MatchValue1(rng) + MatchValue2(rng) 이게 아니라 D:AS범위에서 특정 문자열이 있으면

                  해당 문자열들을 합치게 할 수는 없을까요?예를 들어 아래 표 처럼 있을 때 D:H범위에서 POLLUTION[P]를 찾고 해당 열에 25+26+21합쳐지고 SCRATCH[P]가 있으면 42+12합

                  -이게 어떤 파일은 POLLUTION[P]가 3줄이 있고 어떤 파일은 4줄이 있어 랜덤 이여서 알려 주신대로 합치려면 파일마다 코드를 추가 삭제를 해야 해서요...

                  D E F G H
                  1 POLLUTION[P] POLLUTION[P] POLLUTION[P] SCRATCH[P] SCRATCH[P]
                  2 25 26 21 42 12
                  3 12 26 4 5 20

                   


전체 4,296
번호 카테고리 제목 작성자 작성일 추천 조회
4279 함수/공식
New 재고관리 첨부파일
유리 | 01:07 | 추천 0 | 조회 5
유리 01:07 0 5
4278 VBA
New 이런 유형의 사이트에서도 웹크롤링이 가능할까요?
스일이 | 2021.10.26 | 추천 0 | 조회 10
스일이 2021.10.26 0 10
4277 함수/공식
New FILTER 값을 다른 시트에 TEXT로 표시
키달아찌 | 2021.10.26 | 추천 0 | 조회 5
키달아찌 2021.10.26 0 5
4276 함수/공식
New 한셀에서 한글자만 삭제 가능한지요. 도움 꼭 부탁드립니다.
큐큐큐 | 2021.10.26 | 추천 0 | 조회 7
큐큐큐 2021.10.26 0 7
4275 문서서식
New 외부데이터(인터넷페이지) 값을 엑셀 시트에 값만 붙여넣고 싶어요. 첨부파일 (2)
로얌 | 2021.10.26 | 추천 0 | 조회 9
로얌 2021.10.26 0 9
4274 기능/도구
New 엑셀 텍스트나누기 '텍스트한정자'에 대해서 질문드립니다.
월마리아 | 2021.10.26 | 추천 0 | 조회 9
월마리아 2021.10.26 0 9
4273 함수/공식
New 결제건수에서 중복이름 제거한 뒤 인원수표기 첨부파일 (1)
드래군 | 2021.10.26 | 추천 0 | 조회 18
드래군 2021.10.26 0 18
4272 VBA
New WORKBOOK_OPEN 에 두 가지 모듈 입력하는 법...
tu1541 | 2021.10.26 | 추천 0 | 조회 14
tu1541 2021.10.26 0 14
4271 함수/공식
New 중복 문자 제거후 정렬 방법 첨부파일 (1)
붉은머리샹크스 | 2021.10.26 | 추천 0 | 조회 17
붉은머리샹크스 2021.10.26 0 17
4270 함수/공식
New 몇일을 고민하다 이렇게 도움을 구합니다. (예제파일 첨부드렸습니다. 첨부파일 (1)
JS_SHIN | 2021.10.26 | 추천 0 | 조회 25
JS_SHIN 2021.10.26 0 25
4269 VBA
New VBA for문에 다른시트 vlookup 하기! 질문입니다! (1)
mir**** | 2021.10.26 | 추천 0 | 조회 11
mir**** 2021.10.26 0 11
4268 함수/공식
New 2개의 시트에 데이터 동시 입력 (2)
홍병주 | 2021.10.26 | 추천 0 | 조회 29
홍병주 2021.10.26 0 29
4267 함수/공식
New 가격 평균을 구하고 싶습니다. 첨부파일 (2)
워싱턴 | 2021.10.26 | 추천 2 | 조회 31
워싱턴 2021.10.26 2 31
4266 VBA
New 기능 추가 관련 질문입니다. (1)
노랑토끼 | 2021.10.26 | 추천 0 | 조회 21
노랑토끼 2021.10.26 0 21
4265 차트/그래프
New 동적차트 문제가 생겼어요 첨부파일 (2)
두찌아빠 | 2021.10.26 | 추천 0 | 조회 21
두찌아빠 2021.10.26 0 21
4264 함수/공식
New 원하는 범위만 지정해서 정렬하는게 가능할까요? 첨부파일 (2)
롤로랄라 | 2021.10.26 | 추천 0 | 조회 22
롤로랄라 2021.10.26 0 22
4263 함수/공식
New 중복제거 인원수 첨부파일 (5)
드래군 | 2021.10.26 | 추천 1 | 조회 26
드래군 2021.10.26 1 26
4262 함수/공식
New B시트에 있는 값을 A 시트에 불러오는 함수 알려주실 수 있나요....(예제파일 있음) 첨부파일 (5)
이주 | 2021.10.26 | 추천 0 | 조회 37
이주 2021.10.26 0 37
4261 함수/공식
New 도와주세요~ 두가지 질문입니다ㅠㅠ 첨부파일 (2)
엑셀왕초보 | 2021.10.26 | 추천 0 | 조회 26
엑셀왕초보 2021.10.26 0 26
4260 VBA
New 재고관리폼을 이용하여 LOT로 제품을 등록하고싶습니다. 첨부파일
가을전어 | 2021.10.26 | 추천 0 | 조회 24
가을전어 2021.10.26 0 24
4259 함수/공식
New 엑셀 배열 조건 합계 수식 관련해서 도와주세요 첨부파일 (1)
OoooPs | 2021.10.25 | 추천 0 | 조회 31
OoooPs 2021.10.25 0 31
함수/공식
New Re:엑셀 배열 조건 합계 수식 관련해서 도와주세요 (1)
Yukon | 2021.10.25 | 추천 0 | 조회 28
Yukon 2021.10.25 0 28
4258 함수/공식
New 함수값 표시방법 문의드려요~ 첨부파일 (4) 답변완료
바람투유 | 2021.10.25 | 추천 0 | 조회 25
바람투유 2021.10.25 0 25
4257 VBA
New ms 365 쓰시는분 계신가요? 사내 엑셀 동기화 + vba 관련 (4) 답변완료
백일호 | 2021.10.25 | 추천 1 | 조회 38
백일호 2021.10.25 1 38
4256 VBA
New 도와주세요 ㅠㅠ어떻게해야될까요 (1)
dud**** | 2021.10.25 | 추천 0 | 조회 37
dud**** 2021.10.25 0 37
4255 문서서식
New 사용자 지정서식 관련하여 앞에 문자 고정으로 하고, 뒤에 숫자 입력후 " -00"등을 붙이면 앞의고정 문자가 사라지지 않는 방법 (2) 답변완료
아빠의꿈 | 2021.10.25 | 추천 0 | 조회 25
아빠의꿈 2021.10.25 0 25
4254 함수/공식
New indirect 함수 관련 질문 입니다 해결방법좀 가르쳐주세요 (4)
밍장군 | 2021.10.25 | 추천 0 | 조회 29
밍장군 2021.10.25 0 29
4253 VBA
New QR바코드 한글작성 및 내용추가편집기능
H.B | 2021.10.25 | 추천 1 | 조회 18
H.B 2021.10.25 1 18
4252 VBA
New 크롤링 실행이 안되는 문제가 있습니다 (1)
미르르르르르르르릉 | 2021.10.25 | 추천 0 | 조회 21
미르르르르르르르릉 2021.10.25 0 21
4251 VBA
New 재고관리 VBA 강의 중 Insert_Record에 관해서 문의드립니다. (1)
올마이타 | 2021.10.25 | 추천 0 | 조회 27
올마이타 2021.10.25 0 27
4250 차트/그래프
New 일주일, 24시간 단위의 차트를 만드는 방법을 알고 싶습니다. 첨부파일 (2)
겨울 | 2021.10.25 | 추천 -1 | 조회 30
겨울 2021.10.25 -1 30