엑셀 파일 합치는 코드를 유튜브에서 보고 한셀에 적용하려고 하는데, Set book = Workbooks.Open(sfile, ReadOnly:=True) 여기에서 런타임 1004가 뜨네요... 지역구문으로 적용해야 한다면서... 어떻게 수정해야할까요?'----------------------------------------------------------------------------------------Sub consolidate_1()'---------------------------------------------------------------------------------------- Dim files As Variant files = Application.GetOpenFilename("Excel Files(*.xls*),*.xlsx", MultiSelect:=True) If TypeName(files) = "Boolean" Then Exit Sub Dim cSht As Worksheet: Set cSht = ActiveSheet Application.ScreenUpdating = False Call paste_data(files, cSht) Application.ScreenUpdating = TrueEnd Sub'----------------------------------------------------------------------------------------Sub paste_data(files As Variant, cSht As Worksheet)'---------------------------------------------------------------------------------------- Dim book As Workbook Dim sht As Worksheet Dim i As Long: i = 0 Dim rngX As Range Dim sfile As Variant For Each sfile In files Set book = Workbooks.Open(sfile, ReadOnly:=True) For Each sht In book.Worksheets 'Stop i = i + 1 If i = 1 Then sht.Range("A1").CurrentRegion.Copy cSht.Range("A1") Else Set rngX = sht.Range("A1").CurrentRegion Set rngX = rngX.Offset(1).Resize(rngX.Rows.Count - 1) rngX.Copy cSht.Range("A1").End(xlDown).Offset(1) End If Next sht book.Close Next End Sub#VBA#consolidate
문제가 없는듯 합니다
'---------------------------------------------------------------------------------------- Sub consolidate_1() '---------------------------------------------------------------------------------------- Dim files As Variant files = Application.GetOpenFilename("Excel Files(*.xls*),*.xlsx", MultiSelect:=True) If TypeName(files) = "Boolean" Then Exit Sub Dim cSht As Worksheet: Set cSht = ActiveSheet Application.ScreenUpdating = False Call paste_data(files, cSht) Application.ScreenUpdating = True End Sub '---------------------------------------------------------------------------------------- Sub paste_data(files As Variant, cSht As Worksheet) '---------------------------------------------------------------------------------------- Dim book As Workbook Dim sht As Worksheet Dim i As Long: i = 0 Dim rngX As Range Dim sfile As Variant For Each sfile In files Set book = Workbooks.Open(sfile, ReadOnly:=True) For Each sht In book.Worksheets 'Stop i = i + 1 If i = 1 Then sht.Range("A1").CurrentRegion.Copy cSht.Range("A1") Else Set rngX = sht.Range("A1").CurrentRegion Set rngX = rngX.Offset(1).Resize(rngX.Rows.Count - 1) rngX.Copy cSht.Range("A1").End(xlDown).Offset(1) End If Next sht book.Close False''닫을때 저장하지 않고 닫기 Next End Sub
참아야지! 참아라! 그러면 잘 되어 갈 걸세. 친구여, 정말 자네 말이 맞네. 세상 사람들 틈에 끼여 날마다 일에 쫓기며, 다른 사람들이 하는 일과 그들의 행동을 보기 시작한 이후로 나는 나 지신과 휠씬 더 잘 타협할 수 있게 되었네. 젊은 베르테르의 슬픔 - 괴테