재질문 VBA 폴더내 모든 파일 통합

VBA
작성자
H.B
작성일
2021-12-20 16:49
조회
123
엑셀버전 : M365

운영체제 : 윈도우10


안녕하세요.

답변이 없어서 다시 문의드립니다.

폴더 내의 모든 파일을 경로지정하여 나름대로 데이터를 가져와 현재 SHEET에 붙여넣는데 성공하였습니다!!!

다만, 문제는 모폴더 내의 자폴더의 파일이 몇백개가 넘어서 모두 열고 닫고하면서 데이터를 가져오는게 엄청난 시간이

걸립니다..... 단순히 파일을 열어서 COPY, Pastespacial을 하는 방법말고 ARRAY를 활용하여, ARRAY에 데이터를 넣어서

오빠두님의 ARRAYTORNG를 써써 한번에 뿌려주면 속도가 빨라지지 않을까 생각중입니다만.....

방법이 생각나지를 않네요 ㅠㅠ 첨부파일 보시고 조언 부탁드립니다 ㅠ

Sub testchu()
 
Dim sPath As String
Dim arr As Variant
Dim WB As Workbook, WS As Worksheet
Dim i As Long
Dim motolng As Long, copylng As Long
 
 
sPath = ActiveSheet.Range("b2").Value
arr = ListFiles(sPath, True, True)  '폴더 경로내의 파일 list
 
For i = LBound(arr) To UBound(arr)
 
     Set WB = Application.Workbooks.Open(arr(i), UpdateLinks:=False, ReadOnly:=True) '데이터 복사 붙이기
            motolng = WB.Sheets(1).Range("a3000").End(xlUp).Row
            copylng = ThisWorkbook.Sheets(2).Range("a3000").End(xlUp).Row
 
             WB.Sheets(1).Range(Cells(5, 1), Cells(motolng, 313)).Copy
             ThisWorkbook.Sheets(2).Range("a" & copylng + 1).PasteSpecial xlPasteValuesAndNumberFormats
            WB.Close
 
Next i
End Sub

 

전체 4

  • 2021-12-22 09:58

    감사합니다 야휘님^^


  • 2021-12-20 23:22

    특정 폴더내 하위폴더를 포함한 엑셀파일을 병합하는 루프에서는
    직접 복사하는 방식이 가장 빠른 방식 입니다.

    다만, 복사하는 파일의 원래 용도가 무엇인지는 모르겠으나
    단순히 XLS 파일이라면, (또는 시스템에 의해 저장되는 데이터라면)
    TXT 나 CSV 로 형식 전환하는 프로세스를 선행하십시요.

    XLS 과 TXT 의 병합속도는 파일이 많아질 수록
    비교할 수 없을만큼 속도차기아 나기 때문입니다. (수십분 vs 수십초)
    (VBA 가 아니라, SHELL 등으로 간단하게 진행할 수도 있는 장점이 있음)

    부가적으로
    통합적으로 복사된 데이터에
    함수 적용시 cells 참조가 아닌 array 참조는 속도 향상을 가질 수 있습니다.


  • 2021-12-21 10:08

    답변 감사합니다 야휘님. 근데... 구체적으로 csv나 txt로 변환하려면 어떤 방법을 써야할런지.... 파일의 용도는 xls파일이고요. 동일 데이터가 모델별, 기간별로 폴더마다 나뉘어 있어서 그걸 한파일에 취합하려고 합니다만, 500개가 넘다보니 단순 복사붙이기로는 너무 시간이 오래걸리네요.... ㅜㅜ 구체적인 방법이 없을까요?


  • 2021-12-21 23:26
    채택된 답변

    다시 생각해 보면,
    파일을 열고 하나의 시트에 복사하는 작업을 500회 정도 반복하는데,
    파일을 열고 닫는 시간은 동일한 시간이 반복될 거고
    하나로 복사된 파일이 매번 최대 3000행이 복사되고 있으니까
    통합되고 있는 파일의 크기가 커지면서
    점진적으로 속도가 늦어지는 문제로 보여 집니다.

    그렇다면,
    1. 엑셀파일을 열고
    2. 데이터를 시트로 복사하지 말고 txt 파일로 저장합니다.
    (엑셀 버전에 따라 차이가 있으나, 최대행은 약 백만행 입니다)

    3. 위의 작업이 모두 끝나면
    txt 파일을 열고, 필요한 작업을 한 후 저장하면 되지 않을까 싶습니다.
    - 이때, array 로 셋팅하고 사용하는 방법으로 하세요.

    코딩작업을
    1번은 이미 갖고 있고
    txt 파일 저장 코드는 아래 참조하세요.
    https://www.oppadu.com/question/?pageid=7&mod=document&uid=11265

    추가적으로
    다수의 사용자들이?
    동일 데이터를 모델별, 기간별로 폴더를 나누어서 엑셀파일로 저장하는 절차를 개선할 여지가 있습니다.
    해당 파일이 단순히 데이터 저장 (서식, 함수가 없는) 목적이라면
    꼭 엑셀을 사용해야 한다면, 개인별이 아닌 팀별로 권한을 설정해서
    파일의 갯수를 줄이는 것입니다.

    또한 통합적 작업이 중요한 업무라면
    엑셀이 아니라 PC용 DB (예, 엑세스) 에 저장하게 하는 방식으로 개선하는 것입니다.

    정답은 없는 것이니, 참조만 하세요.


전체 5,191
번호 카테고리 제목 작성자 작성일 추천 조회
알림
하루 딱 "1분", 진짜 쓰는 엑셀 꿀팁 👉 "오빠두엑셀" 인스타그램 오픈! (29)
트로피 오빠두엑셀 | 2021.10.12 | 추천 23 | 조회 788
트로피 오빠두엑셀 2021.10.12 23 788
공지사항 공지사항
⭐ [더 나은 커뮤니티 문화를 위한 Q&A 글 작성 규칙] ⭐ (21)
오빠두엑셀 | 2021.10.28 | 추천 30 | 조회 2466
트로피 오빠두엑셀 2021.10.28 30 2466
26295 파워쿼리/피벗
New 피벗테이블 '각 필드에 유지할 항목 수'에서 질문입니다. (1)
월마리아 | 20:12 | 추천 0 | 조회 6
월마리아 20:12 0 6
26294 함수/공식
New 합계값이 일치하지 않는 값 구하기! (1)
정해인 | 19:39 | 추천 0 | 조회 11
정해인 19:39 0 11
26290 함수/공식
New 기간구분 IF함수 에러 원인이 뭔지 모르겠어요 (4)
마운틴 | 17:38 | 추천 0 | 조회 20
마운틴 17:38 0 20
26285 함수/공식
New 이번주 시작일(월요일), 마지막일(금요일) yyyy-mm-dd 형식으로 출력하는 방법 (1)
joeee | 15:15 | 추천 0 | 조회 28
joeee 15:15 0 28
26284 피벗테이블
New 피벗 테이블 날짜인식 및 월별 그룹화 관련 첨부파일 (1)
baechu9 | 14:56 | 추천 0 | 조회 22
baechu9 14:56 0 22
26278 피벗테이블
New 피벗테이블 영상보면서 따라해보고 있는데요 잘안되요 ㅠ 첨부파일 (1)
희야맘 | 11:47 | 추천 0 | 조회 21
희야맘 11:47 0 21
26276 문서서식
New [조건부 서식] 아직 데이터가 입력되지 않은 칸에 조건부 서식 적용하지 않도록 하기 첨부파일
jo2 | 10:35 | 추천 0 | 조회 25
jo2 10:35 0 25
26268 함수/공식
New 조건부서식 내에서 INDIRECT 함수와 AND 함수를 함께 사용할 수 없나요? (6)
나는나 | 2022.01.22 | 추천 0 | 조회 44
나는나 2022.01.22 0 44
26260 함수/공식
New 각 지점별 연,월 매출의 합을 구하고 싶습니다. 첨부파일 (4) 답변완료
창이 | 2022.01.22 | 추천 1 | 조회 39
창이 2022.01.22 1 39
26246 함수/공식
New 엑셀 월 구하기 주 구하기 질문 입니다. 첨부파일 (3)
천고마비 | 2022.01.22 | 추천 0 | 조회 40
천고마비 2022.01.22 0 40
26243 함수/공식
New 특정 조건 행 제외 후 다른 시트에 리스트화 첨부파일 (4) 답변완료
상큼한도로시 | 2022.01.21 | 추천 0 | 조회 46
상큼한도로시 2022.01.21 0 46
26239 VBA
New 재고관리 프로그램에서 검색할 때 체크박스 사용하는 방법이 궁금합니다. (1)
엑셀열공 | 2022.01.21 | 추천 0 | 조회 44
엑셀열공 2022.01.21 0 44
26237 피벗테이블
New 시간표시막대 질문 (1)
최재영 | 2022.01.21 | 추천 0 | 조회 39
최재영 2022.01.21 0 39
26236 함수/공식
New 컨트롤 f (찾기) 로 함수의 결과값 검색방법이 있나요? (1)
정지훈 | 2022.01.21 | 추천 0 | 조회 32
정지훈 2022.01.21 0 32
26234 함수/공식
New 함수를 대량변경하고싶은데 가능한지 문의드립니다. (1)
정지훈 | 2022.01.21 | 추천 0 | 조회 38
정지훈 2022.01.21 0 38
26229 함수/공식
New 함수를 어떤걸 써야 될지 모르겠습니다. 첨부파일 (3) 답변완료
조커 | 2022.01.21 | 추천 0 | 조회 51
조커 2022.01.21 0 51
26223 VBA
New 버튼이 클릭이 되지 않습니다. 방법을 모르겠어요. 첨부파일 (4)
다르마 | 2022.01.21 | 추천 0 | 조회 41
다르마 2022.01.21 0 41
26222 VBA
New 자료찾기 vba 문의드립니다. (1)
ㅈㄱㄹ | 2022.01.21 | 추천 0 | 조회 36
ㅈㄱㄹ 2022.01.21 0 36
26220 함수/공식
New 엑셀 셀값 동일 데이터를 순서대로 카운터 하고 싶습니다. (3)
sickp**** | 2022.01.21 | 추천 0 | 조회 37
sickp**** 2022.01.21 0 37
26217 VBA
New 엑셀 매크로 렉걸림해결방법문의 (1)
dud**** | 2022.01.21 | 추천 -1 | 조회 33
dud**** 2022.01.21 -1 33
26214 차트/그래프
New 항목 개수가 변하는 엑셀 차트 그리는 거 도와주세요...ㅠ.ㅠ (15)
태양비 | 2022.01.21 | 추천 0 | 조회 41
태양비 2022.01.21 0 41
26215 함수/공식
New 엑셀 수식숨기기 이후 텍스트입력 문의 (1)
dud**** | 2022.01.21 | 추천 -1 | 조회 32
dud**** 2022.01.21 -1 32
26206 VBA
New 문자자동보내기 편보고 문의 드립니다.
히히후후 | 2022.01.20 | 추천 0 | 조회 30
히히후후 2022.01.20 0 30
26205 함수/공식
New 한 줄 데이터 여러 줄로 나누는 방법 (5초컷 가능) 관련 질문 (2) 답변완료
레논 | 2022.01.20 | 추천 0 | 조회 39
레논 2022.01.20 0 39
26201 VBA
New 슬라이서 선택시 연관된 이미지를 불러오는 방법이 있을까요? 첨부파일 (2) 답변완료
쿠쿠 | 2022.01.20 | 추천 0 | 조회 41
쿠쿠 2022.01.20 0 41
26197 기능/도구
문서간 시트복사이동시, 대상통합문서가 안떠요. 하나밖에
배부른 소크라테스 | 2022.01.20 | 추천 0 | 조회 25
배부른 소크라테스 2022.01.20 0 25
26194 기능/도구
엑셀에 적합한 하드웨어스펙이라는 것이 있을까요? (2) 답변완료
풀빛처럼 | 2022.01.20 | 추천 0 | 조회 46
풀빛처럼 2022.01.20 0 46
26192 함수/공식
엑셀에서 3차 함수 푸는방법 (9) 답변완료
박우성 | 2022.01.20 | 추천 0 | 조회 64
박우성 2022.01.20 0 64
26190 함수/공식
자재관리 대장 만들기 첨부파일 (1)
마른막대기 | 2022.01.20 | 추천 0 | 조회 65
마른막대기 2022.01.20 0 65
26189 함수/공식
고급필터 매크로 자동화 중 0 으로 입력된 값을 empty(빈칸)으로 인식가능한지요 (1)
119구조대 | 2022.01.20 | 추천 0 | 조회 22
119구조대 2022.01.20 0 22