Option Explicit
Public Sub 複数ブック統合()
Dim twb As Workbook '新規ブック
Dim sheet_names As Variant 'シート名一覧
Dim sh_name As String 'シート名
Dim i As Long
Dim folder As String '参照先フォルダ名
Dim outfolder As String '出力先フォルダ名
Dim count As Long 'ファイル件数
Dim bookname As String 'マージ元ブック名
Dim new_bookname As String '統合ブック名
Dim ks As Worksheet '管理シート
Set ks = Worksheets("管理")
folder = ks.Range("B5").Value
outfolder = ks.Range("B9").Value
If folder = "" Then
MsgBox ("参照先フォルダ名未設定")
Exit Sub
End If
If outfolder = "" Then
MsgBox ("出力先フォルダ名未設定")
Exit Sub
End If
bookname = Dir(folder & "\*.xlsx")
If bookname = "" Then
MsgBox (folder & "内にブックが存在しません。")
Exit Sub
End If
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'新規ブック及びシートの作成
'sheet_names = Array("Sheet1", "Sheet2", "シート1", "シート2")
sheet_names = Array("Sheet1", "元データ", "元データ2")
Set twb = Workbooks.Add
'全てのシートを作成する
For i = 0 To UBound(sheet_names)
Call add_sheet(twb, sheet_names(i))
Next
'余分なシートを削除する
For i = twb.Worksheets.count To 1 Step -1
sh_name = twb.Worksheets(i).Name
Call del_sheet(twb, sh_name, sheet_names)
Next
'全てのブックを処理する
count = 0
new_bookname = "マージ" & Left(bookname, Len(bookname) - 8) & ".xlsx"
Do While bookname <> ""
count = count + 1
Call MergeBook(twb, sheet_names, count, folder, bookname)
bookname = Dir()
Loop
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = False
twb.SaveAs (outfolder & "\" & new_bookname)
Application.DisplayAlerts = True
twb.Close
MsgBox ("完了")
End Sub
'新規ブックへ1シートを追加する
Private Sub add_sheet(ByVal twb As Workbook, ByVal sheet_name As String)
Dim i As Long
For i = 1 To twb.Worksheets.count
If LCase(sheet_name) = LCase(twb.Worksheets(i).Name) Then
twb.Worksheets(i).Move after:=twb.Worksheets(twb.Worksheets.count)
Exit Sub
End If
Next
twb.Worksheets.Add after:=twb.Worksheets(twb.Worksheets.count)
twb.Worksheets(twb.Worksheets.count).Name = sheet_name
End Sub
'新規ブックから余分な1シートを削除する
Private Sub del_sheet(ByVal twb As Workbook, ByVal sheet_name As String, ByVal sheet_names As Variant)
Dim i As Long
For i = 0 To UBound(sheet_names)
If LCase(sheet_name) = LCase(sheet_names(i)) Then
Exit Sub
End If
Next
Application.DisplayAlerts = False
twb.Worksheets(sheet_name).Delete
Application.DisplayAlerts = True
End Sub
'新規ブック1ブックをマージする
Public Sub MergeBook(ByVal twb As Workbook, ByVal sheet_names As Variant, ByVal count As Long, ByVal folder As String, ByVal bookname As String)
Dim wb As Workbook
Dim sh_name As String
Dim maxrow_src As Long
Dim maxcol_src As Long
Dim maxrow_trg As Long
Dim ecol As String
Dim i As Long
Set wb = Workbooks.Open(folder & "\" & bookname)
For i = 0 To UBound(sheet_names)
sh_name = sheet_names(i)
maxrow_src = wb.Worksheets(sh_name).Cells(Rows.count, "A").End(xlUp).Row 'A列 最終行を求める
maxcol_src = wb.Worksheets(sh_name).Cells(1, Columns.count).End(xlToLeft).Column '1行目の最終列を求める
maxrow_trg = twb.Worksheets(sh_name).Cells(Rows.count, "A").End(xlUp).Row 'A列 最終行を求める
ecol = ConvertToLetter(maxcol_src)
If count = 1 Then
wb.Worksheets(sh_name).Range("A1:" & ecol & "3").Copy twb.Worksheets(sh_name).Range("A1")
maxrow_trg = 3
End If
If maxrow_src > 3 Then
wb.Worksheets(sh_name).Range("A4:" & ecol & maxrow_src).Copy twb.Worksheets(sh_name).Range("A" & maxrow_trg + 1)
End If
Next
wb.Close
End Sub
'列番号をA~Zの文字に変換
Function ConvertToLetter(iCol As Long) As String
Dim a As Long
Dim b As Long
a = iCol
ConvertToLetter = ""
Do While iCol > 0
a = Int((iCol - 1) / 26)
b = (iCol - 1) Mod 26
ConvertToLetter = Chr(b + 65) & ConvertToLetter
iCol = a
Loop
End Function