{"id":217,"date":"2024-12-18T21:35:55","date_gmt":"2024-12-18T12:35:55","guid":{"rendered":"https:\/\/anbtksh.com\/?p=217"},"modified":"2024-12-18T21:35:57","modified_gmt":"2024-12-18T12:35:57","slug":"%e3%83%95%e3%82%a9%e3%83%ab%e3%83%80%e5%86%85%e3%81%ae%e3%83%96%e3%83%83%e3%82%af%e3%82%92%e6%8c%87%e5%ae%9a%e3%81%97%e3%81%9f%e3%83%96%e3%83%83%e3%82%af%e3%81%ae%e3%82%b7%e3%83%bc%e3%83%88%e3%81%a8","status":"publish","type":"post","link":"https:\/\/anbtksh.com\/?p=217","title":{"rendered":"\u30d5\u30a9\u30eb\u30c0\u5185\u306e\u30d6\u30c3\u30af\u3092\u6307\u5b9a\u3057\u305f\u30d6\u30c3\u30af\u306e\u30b7\u30fc\u30c8\u3068\u3057\u3066\u307e\u3068\u3081\u308b\u3002"},"content":{"rendered":"\n

\u2605\u6307\u5b9a\u3057\u305f\u30d5\u30a9\u30eb\u30c0\u5185\u306e\u30d6\u30c3\u30af\u306b\u304a\u3044\u3066\u3001\u73fe\u5728\u958b\u3044\u3066\u3044\u308b\u30a2\u30af\u30c6\u30a3\u30d6\u30b7\u30fc\u30c8\u306eA\u5217\u306b\u307e\u3068\u3081\u305f\u3044\u65b0\u898f\u306e\u30d6\u30c3\u30af\u540d\uff08\u62e1\u5f35\u5b50\u4ed8\u304d\uff09\u3092\u767b\u9332\u3001\uff22\u5217\u306b\u305d\u306e\u30d6\u30c3\u30af\u306b\u3082\u3063\u3066\u3044\u304d\u305f\u3044\uff08\u305d\u306e\u30d5\u30a9\u30eb\u30c0\u5185\u306e\uff09\u30d6\u30c3\u30af\u540d\uff08\u62e1\u5f35\u5b50\u4ed8\u304d\uff09\u3092\u767b\u9332\u3057\u3066\u304a\u304f\u2605<\/p>\n\n\n\n

Sub \u6307\u5b9a\u30d6\u30c3\u30af\u3092\u30b7\u30fc\u30c8\u306b\u767b\u9332()
Dim folderPath As String
Dim fileName As String
Dim sheetName As String
Dim targetWorkbook As Workbook
Dim sourceWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim cell As Range
Dim ws As Worksheet
Dim totalFiles As Long
Dim processedFiles As Long<\/p>\n\n\n\n

' \u30d5\u30a9\u30eb\u30c0\u306e\u30d1\u30b9\u3092\u5165\u529b\nfolderPath = InputBox(\"\u64cd\u4f5c\u5bfe\u8c61\u306b\u306a\u308b\u30d5\u30a9\u30eb\u30c0\u306e\u30d1\u30b9\u3092\u5165\u529b\u3057\u3066\u304f\u3060\u3055\u3044\", \"\u30d5\u30a9\u30eb\u30c0\u306e\u9078\u629e\")\nIf Right(folderPath, 1) <> \"\\\" Then folderPath = folderPath & \"\\\"\n\n' \u753b\u9762\u306e\u66f4\u65b0\u3092\u505c\u6b62\nApplication.ScreenUpdating = False\n\n' \u51e6\u7406\u5bfe\u8c61\u306e\u30d5\u30a1\u30a4\u30eb\u6570\u3092\u30ab\u30a6\u30f3\u30c8\ntotalFiles = ActiveSheet.Range(\"A1:A\" & ActiveSheet.Cells(rows.Count, 1).End(xlUp).row).Count\nprocessedFiles = 0\n\n' A\u5217\u306e\u5404\u30bb\u30eb\u3092\u30eb\u30fc\u30d7\nFor Each cell In ActiveSheet.Range(\"A1:A\" & ActiveSheet.Cells(rows.Count, 1).End(xlUp).row)\n    fileName = cell.Value\n    sheetName = Left(cell.Offset(0, 1).Value, InStrRev(cell.Offset(0, 1).Value, \".\") - 1)\n\n    ' \u30bf\u30fc\u30b2\u30c3\u30c8\u30d6\u30c3\u30af\u304c\u5b58\u5728\u3059\u308b\u304b\u78ba\u8a8d\u3057\u3001\u5b58\u5728\u3057\u306a\u3051\u308c\u3070\u4f5c\u6210\n    On Error Resume Next\n    Set targetWorkbook = Workbooks.Open(folderPath & fileName)\n    If Err.Number <> 0 Then\n        Set targetWorkbook = Workbooks.Add\n        targetWorkbook.SaveAs folderPath & fileName\n    End If\n    On Error GoTo 0\n\n    ' \u30bd\u30fc\u30b9\u30d6\u30c3\u30af\u3092\u958b\u304f\n    Set sourceWorkbook = Workbooks.Open(folderPath & cell.Offset(0, 1).Value)\n\n    ' \u30bd\u30fc\u30b9\u30d6\u30c3\u30af\u306b\u8907\u6570\u30b7\u30fc\u30c8\u304c\u3042\u308b\u5834\u5408\u306f\u30a8\u30e9\u30fc\u3092\u8868\u793a\u3057\u3066\u505c\u6b62\n    If sourceWorkbook.Sheets.Count > 1 Then\n        MsgBox \"\u30a8\u30e9\u30fc: \" & sourceWorkbook.Name & \" \u306b\u8907\u6570\u306e\u30b7\u30fc\u30c8\u304c\u3042\u308a\u307e\u3059\u3002\u51e6\u7406\u3092\u4e2d\u6b62\u3057\u307e\u3059\u3002\"\n        sourceWorkbook.Close False\n        targetWorkbook.Close False\n        Exit Sub\n    End If\n\n    Set sourceSheet = sourceWorkbook.Sheets(1)\n\n    ' \u30bd\u30fc\u30b9\u30b7\u30fc\u30c8\u3092\u30bf\u30fc\u30b2\u30c3\u30c8\u30d6\u30c3\u30af\u306b\u30b3\u30d4\u30fc\n    sourceSheet.Copy After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)\n    targetWorkbook.Sheets(targetWorkbook.Sheets.Count).Name = sheetName\n\n    ' \u30bd\u30fc\u30b9\u30d6\u30c3\u30af\u3092\u4fdd\u5b58\u305b\u305a\u306b\u9589\u3058\u308b\n    sourceWorkbook.Close False\n\n    ' \u7a7a\u767d\u306e\u30b7\u30fc\u30c8\u3092\u524a\u9664\n    For Each ws In targetWorkbook.Sheets\n        If WorksheetFunction.CountA(ws.Cells) = 0 Then\n            Application.DisplayAlerts = False\n            ws.Delete\n            Application.DisplayAlerts = True\n        End If\n    Next ws\n\n    ' \u5404\u30b7\u30fc\u30c8\u3092100%\u8868\u793a\u306b\u3057\u3001A1\u30bb\u30eb\u306b\u30d5\u30a9\u30fc\u30ab\u30b9\u3092\u5f53\u3066\u308b\n    For Each ws In targetWorkbook.Sheets\n        ws.Activate\n        ActiveWindow.Zoom = 100\n        ws.Cells(1, 1).Select\n    Next ws\n\n    ' \u4e00\u756a\u5de6\u306e\u30b7\u30fc\u30c8\u3092\u30a2\u30af\u30c6\u30a3\u30d6\u306b\u3059\u308b\n    targetWorkbook.Sheets(1).Activate\n\n    ' \u30bf\u30fc\u30b2\u30c3\u30c8\u30d6\u30c3\u30af\u3092\u4fdd\u5b58\u3057\u3066\u9589\u3058\u308b\n    targetWorkbook.Close True\n\n    ' \u9032\u6357\u72b6\u6cc1\u3092\u66f4\u65b0\n    processedFiles = processedFiles + 1\n    Application.StatusBar = \"\u9032\u6357: \" & Format(processedFiles \/ totalFiles, \"0%\") & \" \u5b8c\u4e86\"\nNext cell\n\n' \u753b\u9762\u306e\u66f4\u65b0\u3092\u518d\u958b\nApplication.ScreenUpdating = True\nApplication.StatusBar = False\n\nMsgBox \"\u30b7\u30fc\u30c8\u306e\u767b\u9332\u304c\u5b8c\u4e86\u3057\u307e\u3057\u305f\uff01\"<\/code><\/pre>\n\n\n\n
End Sub<\/p>\n","protected":false},"excerpt":{"rendered":"
\u2605\u6307\u5b9a\u3057\u305f\u30d5\u30a9\u30eb\u30c0\u5185\u306e\u30d6\u30c3\u30af\u306b\u304a\u3044\u3066\u3001\u73fe\u5728\u958b\u3044\u3066\u3044\u308b\u30a2\u30af\u30c6\u30a3\u30d6\u30b7\u30fc\u30c8\u306eA\u5217\u306b\u307e\u3068\u3081\u305f\u3044\u65b0\u898f\u306e\u30d6\u30c3\u30af\u540d\uff08\u62e1\u5f35\u5b50\u4ed8\u304d\uff09\u3092\u767b\u9332\u3001\uff22\u5217\u306b\u305d\u306e\u30d6\u30c3\u30af\u306b\u3082\u3063\u3066\u3044\u304d\u305f\u3044\uff08\u305d\u306e\u30d5\u30a9\u30eb\u30c0\u5185\u306e\uff09\u30d6\u30c3\u30af\u540d\uff08\u62e1\u5f35\u5b50\u4ed8\u304d\uff09\u3092\u767b\u9332\u3057\u3066\u304a\u304f\u2605 Sub … <\/p>\n
“\u30d5\u30a9\u30eb\u30c0\u5185\u306e\u30d6\u30c3\u30af\u3092\u6307\u5b9a\u3057\u305f\u30d6\u30c3\u30af\u306e\u30b7\u30fc\u30c8\u3068\u3057\u3066\u307e\u3068\u3081\u308b\u3002” \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-217","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/anbtksh.com\/index.php?rest_route=\/wp\/v2\/posts\/217","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anbtksh.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anbtksh.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anbtksh.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anbtksh.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=217"}],"version-history":[{"count":1,"href":"https:\/\/anbtksh.com\/index.php?rest_route=\/wp\/v2\/posts\/217\/revisions"}],"predecessor-version":[{"id":218,"href":"https:\/\/anbtksh.com\/index.php?rest_route=\/wp\/v2\/posts\/217\/revisions\/218"}],"wp:attachment":[{"href":"https:\/\/anbtksh.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=217"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anbtksh.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=217"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anbtksh.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=217"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}