매일 변경되는 파일명이 5.11.xlsx 형식인데 vlookup으로 파일을 열지 않고 참조 범위를 자동으로 오늘 날짜의 파일을 가져올 수 있을까요?

함수/공식
작성자
호랑이파워
작성일
2022-05-11 18:06
조회
68
엑셀버전 : 엑셀2016

운영체제 : 윈도우10


안녕하세요.

매일 파일 이름이 [5.11.xlsx] 형식인 단가표가 있는데 여기에 있는 값을 파일을 열지 않고 vlookup으로 가져오고 있습니다.

맨 위에 셀 몇개만 변경하여 자동 채우기를 하면 되지만 자동으로 매일 오늘 날짜 파일의 값을 vlookup으로 가져오고 싶습니다.

그래서 저번에 문의를 드려서 얻은 방법은 VLOOKUP( ) 함수에서 참조할 영역을 INDIRECT( )로 참조하면 매일 바뀌는 수식을 아래와 같이 완성할 수 있었습니다.

ADDRESS(2, 1, 1, 1, "["&TEXT(TODAY(),"m.d")&".xlsx]Sheet1") & ":" & ADDRESS(7, 2)

VLOOKUP("거래처 5", INDIRECT($A$1), 2, 0)

하지만 INDIRECT을 이용한 VLOOKUP은 파일이 열려 있어야만 값을 가져올 수 있어서 다른 방안이 있나 궁금하여 여쭤봅니다.

[A.단가표_DB] 파일이 메인 DB파일입니다.
[B.매일변동_단가표] 파일은 지정된 폴더에 5.11.xlsx 파일 형식으로 매일 업데이트 됩니다

 

+ A파일이 기준 파일이고 B파일의 값을 VLOOKUP으로 파일을 열지 않고 가져오려 합니다.

 

항상 감사드립니다.

회원등급 : 7레벨
포인트 : 491 EP
총질문 : 11 개 (마감율 : 100%)
채택답변 : 0 개
전체 4

  • 2022-05-11 19:20
    채택된 답변

    이렇게 해 보세요.

    먼저, A파일에 시트하나를 "Link"같은 이름으로 추가하고,  각 셀마다 수식으로 B파일로 연결을 합니다.

    A1 = '[5.2.xlsm]상품'!A1

    그럼 A 파일에는 데이터탭에 보면 연결편집이 활성화 되면서 수식으로 연결된 파일이 나옵니다.

    이 연결파일을 직접 바꿔주는 방식으로 다른 파일을 열지 않고도 연결된 시트의 데이터를 읽어 올 수 있습니다.

    ActiveWorkbook.ChangeLink Name:="5.2.xlsm", NewName:="5.4.xlsm", Type:=xlExcelLinks

    Name에 기존 연결파일, NewName에 신규 연결파일을 넣으면 됩니다.

    매크로가 실행되면 실제로 변경된 파일로 값이 Update되는데 조금 시간이 필요합니다.

     

    그럼 이제 "Link"시트에 연결된 B파일의 값이 다 들어와 있으므로 일반 매크로나 수식으로 하시고자 하는 내용을 하시면 됩니다.

     


    • 2022-05-14 09:37

      @원조백수 님 답변주셔서 감사합니다

       

      알려주신 방법으로 5000행이 넘는 데이터를 적용했더니 시간이 꽤 걸려서 사용하긴 어려웠지만 엑셀 공부에 많은 도움이 되었습니다.

      즐거운 주말 보내시길 바랍니다.


      • 2022-05-14 14:45

        @호랑이파워 님 자료가 많다면 아래처럼 ADODB로 가져와 보세요.

        Option Explicit
        
        Sub GetDatawithADODB()
            Dim DB As Object, RS As Object, strQuery As String
            Dim DBPath As String, FileName As String
            Dim SH As Worksheet, iMaxRow As Long
            
            Set DB = CreateObject("ADODB.Connection")
            Set RS = CreateObject("ADODB.Recordset") 
            Set SH = Sheets("Link") '// 자료를 복사해서 붙여넣을 시트 
        
            SH.AutoFilterMode = False
            iMaxRow = SH.Cells(SH.Rows.Count, 1).End(xlUp).Row
            If iMaxRow > 1 Then SH.Range("A2:A" & iMaxRow).EntireRow.Delete xlUp  '// 기존자료 지우기
            
            DBPath = ThisWorkbook.Path  '// 자료가 있는 폴더명 (마지막에 '\'가 없음)
            FileName = "5.2.xlsm"            '// 날짜별 자료명
            
            DB.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & DBPath & Application.PathSeparator & FileName _
                    & "';Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"";"
            
            strQuery = "SELECT * From [상품$] " '// Table은 "[시트명$]"으로
            
            RS.Open strQuery, DB
            SH.Range("A2").CopyFromRecordset RS
            RS.Close
            DB.Close
            
            Set DB = Nothing: Set RS = Nothing
            
        End Sub

         

        첨부파일 : A.단가표_DB.xlsm


        • 2022-05-15 12:23

          @원조백수 님 와~ 감사합니다!

          알려주신 방법으로 해보니 5000행이 넘는 데이터도 1~2초 안에 금방 가져오네요.

          새롭게 생성한 링크 시트에 다른 파일을 금방 복붙을 하여 vlookup도 빠르게 사용할 수 있게 되었습니다.

          알려주신거 잘 응용해서 사용하겠습니다.

          정말 감사합니다.


전체 6,490
번호 카테고리 제목 작성자 작성일 추천 조회
알림
[🏆 진짜쓰는 실무엑셀 스터디] 1기 - 우수 졸업생 및 후기, 축하합니다! (8)
오빠두엑셀 | 2022.04.30 | 추천 10 | 조회 1284
오빠두엑셀 2022.04.30 10 1284
공지사항 함수/공식
⭐ [더 나은 커뮤니티 문화를 위한 Q&A 글 작성 규칙] ⭐ (65)
오빠두엑셀 | 2021.10.28 | 추천 81 | 조회 4611
오빠두엑셀 2021.10.28 81 4611
34494 파워쿼리/피벗
New 엑셀 계정별원장 시트 합치기 방법 문의 드립니다.
우렁각시탈 | 16:41 | 추천 0 | 조회 7
우렁각시탈 16:41 0 7
34492 VBA
New 엑셀에 드래그 앤 드롭으로 그림파일 삽입 (4)
banarchy | 15:55 | 추천 0 | 조회 11
banarchy 15:55 0 11
34477 문서서식
New 수식 복사 질문드려요.. ㅠㅠ 불금 퇴근하고싶습니다... 첨부파일 (3) 답변완료
김동률 | 2022.05.27 | 추천 0 | 조회 36
김동률 2022.05.27 0 36
34473 VBA
New 비고란에서 휴대폰번호 추출하기에 관해 문의드립니다. 첨부파일 (1)
수메리안 | 2022.05.27 | 추천 0 | 조회 32
수메리안 2022.05.27 0 32
34472 기능/도구
New 스핀단추에서 증분변경시 0.1 씩 증감할려면 어떻게 해야 할까요? (1)
033cola | 2022.05.27 | 추천 0 | 조회 26
033cola 2022.05.27 0 26
34471 함수/공식
New 조건부서식 중복 2개이상 및 2개일때 서식좀 봐주세요 궁금합니다. 첨부파일 (3)
김주현 | 2022.05.27 | 추천 0 | 조회 37
김주현 2022.05.27 0 37
34469 문서서식
New 표의 행렬 변경 첨부파일 (4)
김학준 | 2022.05.27 | 추천 0 | 조회 36
김학준 2022.05.27 0 36
34465 차트/그래프
New 간트차트에 오늘 표시하는 방법 질문드려요
yoyoy**** | 2022.05.27 | 추천 0 | 조회 29
yoyoy**** 2022.05.27 0 29
34464 VBA
New Vba로 수식을 입력하려는데 오류가 납니다. (3)
silverf**** | 2022.05.27 | 추천 0 | 조회 34
silverf**** 2022.05.27 0 34
34451 함수/공식
New 제품별 코드 중복/단독 확인함수 첨부파일 (2)
다은지후아빠 | 2022.05.27 | 추천 -1 | 조회 47
다은지후아빠 2022.05.27 -1 47
34450 VBA
New VBA를 활용한 최단거리 계산(Pathfinder,카카오맵) 첨부파일
darc**** | 2022.05.27 | 추천 0 | 조회 33
darc**** 2022.05.27 0 33
34448 함수/공식
New countif 다양한 조건으로 만들고 싶어요~ 첨부파일 (2) 답변완료
김수달 | 2022.05.27 | 추천 0 | 조회 46
김수달 2022.05.27 0 46
34444 기능/도구
New 엑셀 빠른 도구 모음에 있는 '조명'이 무슨 기능인지 아시는분 계신가요?
월마리아 | 2022.05.26 | 추천 0 | 조회 46
월마리아 2022.05.26 0 46
34440 함수/공식
New 고객 리텐션, 접속 일수에 맞춰 데이터를 합산하는 함수/VBA 문의! 첨부파일
은혼 | 2022.05.26 | 추천 0 | 조회 22
은혼 2022.05.26 0 22
34439 차트/그래프
New 체크박스 선택에 따라 값이 바뀌는 차트의 데이터레이블 표시 문제 관련 첨부파일
Qwerty | 2022.05.26 | 추천 0 | 조회 25
Qwerty 2022.05.26 0 25
34437 함수/공식
New 국가만 찾아 올련면 첨부파일 (4) 답변완료
구름나그네 | 2022.05.26 | 추천 0 | 조회 41
구름나그네 2022.05.26 0 41
34433 함수/공식
New 함수관련 질문있습니다.
섭섭섭 | 2022.05.26 | 추천 0 | 조회 31
섭섭섭 2022.05.26 0 31
34427 함수/공식
New 특정 공휴일 제외 후 2달 뒤 날짜를 구할수 있는 함수 식이 있을까요? (1)
김태우 | 2022.05.26 | 추천 0 | 조회 42
김태우 2022.05.26 0 42
34417 피벗테이블
New 피벗테이블 외부 데이터 참조 후 새로고침 할 때 자꾸 암호를 입력하라고 떠요.. (2) 답변완료
forest choi | 2022.05.26 | 추천 0 | 조회 35
forest choi 2022.05.26 0 35
34416 함수/공식
New 날짜/시간 차이를 구하려면 어떻게 하면 좋을까요? 첨부파일 (2)
aron007 | 2022.05.26 | 추천 0 | 조회 41
aron007 2022.05.26 0 41
34415 VBA
New VBA코드로 자동채우기 할때 유동범위는 어떻게 하나요 첨부파일 (2)
nam**** | 2022.05.26 | 추천 1 | 조회 41
nam**** 2022.05.26 1 41
34414 함수/공식
New 금지어 삭제하는 함수 (특정텍스트 범위내의 값을 제거 후 값 불러오기) 첨부파일 (1)
Jasper | 2022.05.26 | 추천 0 | 조회 42
Jasper 2022.05.26 0 42
34406 함수/공식
New sum 함수 오류? 첨부파일 (1)
saechang | 2022.05.25 | 추천 0 | 조회 52
saechang 2022.05.25 0 52
34405 VBA
New 중복데이터가 많은데 맨 위에 있는 행의 데이터만 남길 수 있는 방법이 있을까요? 첨부파일 (3) 답변완료
오구 | 2022.05.25 | 추천 0 | 조회 67
오구 2022.05.25 0 67
34402 VBA
vba 특정문자 입력시 현재시간 구동 관련 첨부파일 (2)
1412 | 2022.05.25 | 추천 0 | 조회 46
1412 2022.05.25 0 46
34400 함수/공식
다른 셀 값 참조시 결과값이 0으로만 나오는 현상 문의 첨부파일 (3) 답변완료
forest choi | 2022.05.25 | 추천 0 | 조회 46
forest choi 2022.05.25 0 46
34395 VBA
조건이 맞을 경우 범위데이터 이동 or 복사,삭제 문의 첨부파일 (2) 답변완료
com**** | 2022.05.25 | 추천 0 | 조회 54
com**** 2022.05.25 0 54
34392 함수/공식
다른 파일을 참조하여 합계를 구하는데, 그 참조 파일의 이름이 바뀐다면? (1)
풀빵이네 | 2022.05.25 | 추천 0 | 조회 35
풀빵이네 2022.05.25 0 35
34391 기능/도구
F1 키 누르면 나오는 도움말 끄는 단축키는 없나요? (5)
우렁각시탈 | 2022.05.25 | 추천 0 | 조회 48
우렁각시탈 2022.05.25 0 48
34390 함수/공식
셀이 빈 경우 텍스트조인이 실행되지 않도록 하는 방법 첨부파일 (6) 답변완료
forest choi | 2022.05.25 | 추천 0 | 조회 58
forest choi 2022.05.25 0 58