\u4eca\u56de\u306f\u3001Dir\u95a2\u6570\u3092\u5229\u7528\u3057\u3066\u3001\u30d5\u30a9\u30eb\u30c0\u306e\u4e2d\u306eCSV\u30d5\u30a1\u30a4\u30eb\u7fa4\u3092\u5217\u6319\u3057\u3066\uff11\u3064\u306e\u30b7\u30fc\u30c8\u306b\u30b3\u30d4\u30fc\u3059\u308b\u65b9\u6cd5\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/p>\n
CSV\u30d5\u30a1\u30a4\u30eb\u306b\u30d8\u30c3\u30c0\u30fc\u304c\u5b58\u5728\u3059\u308b\u5834\u5408\u306f\u3001\u6700\u521d\u306e\u30d5\u30a1\u30a4\u30eb\u3092\u8aad\u307f\u8fbc\u3093\u3060\u30c7\u30fc\u30bf\u306f\u30d8\u30c3\u30c0\u30fc\u4ed8\u304d\u3001\u305d\u308c\u4ee5\u5916\u306f2\u884c\u76ee\u304b\u3089\u306e\u30c7\u30fc\u30bf\u3092\u30b7\u30fc\u30c8\u306b\u8cbc\u308a\u4ed8\u3051\u3057\u307e\u3059\u3002<\/p>\n
\u30d8\u30c3\u30c0\u30fc\u304c\u5b58\u5728\u3057\u306a\u3044\u5834\u5408\u306f\u3001\u5168\u3066\u306e\u30c7\u30fc\u30bf\u3092\u30b7\u30fc\u30c8\u306b\u8cbc\u308a\u4ed8\u3051\u3057\u307e\u3059\u3002<\/p>\n
CSV\u30d5\u30a1\u30a4\u30eb\u3092\u5217\u6319\u3057\u3066\u8aad\u307f\u8fbc\u307f1\u3064\u306e\u30b7\u30fc\u30c8\u306b\u66f8\u304d\u8fbc\u3080\u95a2\u6570\u3092\u5229\u7528\u3059\u308b\u306b\u306f\u3001\u5217\u6319\u3057\u305fCSV\u30d5\u30a1\u30a4\u30eb\u30c7\u30fc\u30bf\u3092\u30b3\u30d4\u30fc\u3057\u3066\u8cbc\u308a\u4ed8\u3051\u3059\u308b\u305f\u3081\u306e\u30b7\u30fc\u30c8\u540d\u3092\u8ffd\u52a0\u3057\u3066\u304a\u304f\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n
Function CSV\u30d5\u30a1\u30a4\u30eb\u3092\u5217\u6319\u3057\u3066\u8aad\u307f\u8fbc\u307f1\u3064\u306e\u30b7\u30fc\u30c8\u306b\u66f8\u304d\u8fbc\u3080(strFolder As String, nHeader As Long, strSheetName As String) As Boolean\r\n \r\n Dim strFile As String\r\n \r\n Dim wb As Workbook\r\n Dim ws As Worksheet\r\n \r\n Dim nEndRow As Long\r\n \r\n Dim nEndRow2 As Long\r\n Dim nEndColumn2 As Long\r\n \r\n ' Excel\u306e\u78ba\u8a8d\u30e1\u30c3\u30bb\u30fc\u30b8\u306a\u3057\u30fb\u30a4\u30d9\u30f3\u30c8\u306a\u3057\u30fb\u63cf\u5199\u306a\u3057\u30fb\u81ea\u52d5\u8a08\u7b97\u306a\u3057\u30fb\u30ab\u30fc\u30bd\u30eb\u3092\u7802\u6642\u8a08\r\n Application.DisplayAlerts = False\r\n Application.EnableEvents = False\r\n Application.ScreenUpdating = False\r\n Application.Calculation = xlCalculationManual\r\n Application.Cursor = xlWait\r\n \r\n ' strSheetName\u30b7\u30fc\u30c8\u306e\u30bb\u30eb\u3092\u30af\u30ea\u30a2\u3059\u308b\r\n ThisWorkbook.Worksheets(strSheetName).Cells.Clear\r\n \r\n If Right(strFolder, 1) <> \"\\\" Then\r\n strFolder = strFolder & \"\\\"\r\n End If\r\n \r\n ' \u30d5\u30a9\u30eb\u30c0\u306b\u3042\u308bCSV\u30d5\u30a1\u30a4\u30eb\u3092\u8aad\u307f\u8fbc\u307f\u3001\uff11\u3064\u306e\u30b7\u30fc\u30c8\u306b\u66f8\u304d\u8fbc\u3080\r\n strFile = Dir(strFolder & \"*.csv\")\r\n Do While strFile <> \"\"\r\n \r\n ' CSV\u30d5\u30a1\u30a4\u30eb\u3092\u8aad\u307f\u8fbc\u3080\r\n Set wb = Workbooks.Open(strFolder & strFile)\r\n Set ws = wb.Sheets(1)\r\n \r\n ' \u3053\u306e\u30ef\u30fc\u30af\u30d6\u30c3\u30af\u306edata\u30b7\u30fc\u30c8\u306e\u6700\u5f8c\u306e\u884c\u3092\u53d6\u5f97\r\n nEndRow = ThisWorkbook.Worksheets(strSheetName).Cells(Rows.Count, 1).End(xlUp).Row\r\n \r\n ' CSV\u30d5\u30a1\u30a4\u30eb\u306e\u6700\u5f8c\u306e\u884c\u3068\u5217\u3092\u53d6\u5f97\r\n nEndRow2 = ws.Cells(Rows.Count, 1).End(xlUp).Row\r\n nEndColumn2 = ws.Cells(1, Columns.Count).End(xlToLeft).Column\r\n \r\n ' CSV\u30d5\u30a1\u30a4\u30eb\u306e\u30c7\u30fc\u30bf\u3092\u30b3\u30d4\u30fc\u3057\r\n ' strSheetName\u30b7\u30fc\u30c8\u304c\u7a7a\u767d\u306e\u5834\u5408\u306f1\u884c\u76ee\u306b\u8cbc\u308a\u4ed8\u3051\r\n ' \u7a7a\u767d\u3067\u306a\u3044\u5834\u5408\u306f\u6700\u5f8c\u306e\u884c\u306e\u6b21\u306e\u884c\u306b\u8cbc\u308a\u4ed8\u3051\r\n If nEndRow = 1 Then\r\n ws.Range(ws.Cells(1, 1), ws.Cells(nEndRow2, nEndColumn2)).Copy ThisWorkbook.Sheets(strSheetName).Cells(nEndRow, 1)\r\n Else\r\n ws.Range(ws.Cells(1 + nHeader, 1), ws.Cells(nEndRow2, nEndColumn2)).Copy ThisWorkbook.Sheets(strSheetName).Cells(nEndRow + 1, 1)\r\n End If\r\n CSV\u30d5\u30a1\u30a4\u30eb\u3092\u5217\u6319\u3057\u3066\u8aad\u307f\u8fbc\u307f1\u3064\u306e\u30b7\u30fc\u30c8\u306b\u66f8\u304d\u8fbc\u3080 = True\r\n \r\n ' CSV\u30d5\u30a1\u30a4\u30eb\u3092\u9589\u3058\u3066\u89e3\u653e\r\n wb.Close\r\n Set ws = Nothing\r\n Set wb = Nothing\r\n \r\n ' \u6b21\u306e\u30d5\u30a1\u30a4\u30eb\u3092\u53d6\u5f97\r\n strFile = Dir()\r\n Loop\r\n \r\n ' Excel\u306e\u78ba\u8a8d\u30e1\u30c3\u30bb\u30fc\u30b8\u3042\u308a\u30fb\u30a4\u30d9\u30f3\u30c8\u3042\u308a\u30fb\u63cf\u5199\u3042\u308a\u30fb\u81ea\u52d5\u8a08\u7b97\u3042\u308a\u30fb\u30ab\u30fc\u30bd\u30eb\u3092\u666e\u901a\u306b\u623b\u3059\r\n Application.ScreenUpdating = True\r\n Application.EnableEvents = True\r\n Application.DisplayAlerts = True\r\n Application.Calculation = xlCalculationAutomatic\r\n Application.Cursor = xlDefault\r\n\r\nEnd Function<\/pre>\n\u95a2\u6570\u306e\u8aac\u660e<\/h3>\n
strFolder:CSV\u30d5\u30a1\u30a4\u30eb\u3092\u5217\u6319\u3057\u305f\u3044\u30d5\u30a9\u30eb\u30c0\u3092\u8a2d\u5b9a\u3057\u307e\u3059\u3002<\/p>\n
nHeader:\u30d8\u30c3\u30c0\u30fc\u304c\u3042\u308b\u5834\u5408\u306f1\u3001\u306a\u3044\u5834\u5408\u306f0\u3092\u8a2d\u5b9a\u3057\u307e\u3059\u3002<\/p>\n
strSheetName:\u5217\u6319\u3057\u305fCSV\u30d5\u30a1\u30a4\u30eb\u306e\u30c7\u30fc\u30bf\u3092\u8cbc\u308a\u4ed8\u3051\u3059\u308b\u305f\u3081\u306e\u30b7\u30fc\u30c8\u3092\u8a2d\u5b9a\u3057\u307e\u3059\u3002<\/p>\n
\u4f7f\u7528\u4f8b<\/h3>\n
C2\u30bb\u30eb\u306b\u30d5\u30a9\u30eb\u30c0\u304c\u5165\u3063\u3066\u3044\u307e\u3059\u3002<\/p>\n
C2\u30bb\u30eb\u306e\u30d5\u30a9\u30eb\u30c0\u3092\u521d\u671f\u8a2d\u5b9a\u3068\u3057\u3066\u3001\u300c\u30d5\u30a9\u30eb\u30c0\u3092\u9078\u629e\u3059\u308b\u300d\u95a2\u6570\u306b\u6e21\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n
\u6b21\u306b\u3001\u9078\u629e\u3057\u305f\u30d5\u30a9\u30eb\u30c0\u3068\u30d1\u30e9\u30e1\u30fc\u30bf\u3092\u8a2d\u5b9a\u3057\u3001\u300cCSV\u30d5\u30a1\u30a4\u30eb\u3092\u5217\u6319\u3057\u3066\u8aad\u307f\u8fbc\u307f1\u3064\u306e\u30b7\u30fc\u30c8\u306b\u66f8\u304d\u8fbc\u3080\u300d\u95a2\u6570\u306b\u6e21\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n
\u4eca\u56de\u306e\u4f8b\u306f\u300115\u884c\u76ee\u3067\u30d8\u30c3\u30c0\u30fc\u306a\u3057\u3001\u30b7\u30fc\u30c8\u540d\u3092\u300cdata\u300d\u3092\u8a2d\u5b9a\u3057\u3066\u3001\u5217\u6319\u3057\u305f\u30d5\u30a1\u30a4\u30eb\u306e\u30c7\u30fc\u30bf\u3092\u300cdata\u300d\u30b7\u30fc\u30c8\u306b\u30b3\u30d4\u30fc\u3059\u308b\u3088\u3046\u306b\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n
Sub \u30d5\u30a9\u30eb\u30c0\u3092\u9078\u629e\u3059\u308b_Click()\r\n\r\n Dim strFolder As String\r\n Dim bRet As Boolean\r\n \r\n strFolder = Range(\"c2\")\r\n \r\n strFolder = \u30d5\u30a9\u30eb\u30c0\u3092\u9078\u629e\u3059\u308b(strFolder)\r\n Range(\"c2\") = strFolder\r\n \r\n Dim i As Long\r\n \r\n ' \u30d5\u30a1\u30a4\u30eb\u3092\u5217\u6319\u3059\u308b\r\n If strFolder <> \"\" Then\r\n bRet = CSV\u30d5\u30a1\u30a4\u30eb\u3092\u5217\u6319\u3057\u3066\u8aad\u307f\u8fbc\u307f1\u3064\u306e\u30b7\u30fc\u30c8\u306b\u66f8\u304d\u8fbc\u3080(strFolder, 0, \"data\")\r\n End If\r\n \r\n MsgBox \"\u66f8\u304d\u8fbc\u307f\u3092\u7d42\u4e86\u3057\u307e\u3057\u305f\", vbInformation\r\n \r\nEnd Sub\r\n\r\n' FileDialog\u3092\u5229\u7528\u3057\u3066\u30d5\u30a9\u30eb\u30c0\u3092\u53c2\u7167\u3059\u308b\r\nFunction \u30d5\u30a9\u30eb\u30c0\u3092\u9078\u629e\u3059\u308b(strFolder As String)\r\n\r\n If Right(strFolder, 1) <> \"\\\" Then\r\n strFolder = strFolder & \"\\\"\r\n End If\r\n\r\n With Application.FileDialog(msoFileDialogFolderPicker)\r\n .InitialFileName = strFolder ' \u30d5\u30a9\u30eb\u30c0\u306e\u521d\u671f\u5024\u3092\u8a2d\u5b9a\r\n .Title = \"\u30d5\u30a9\u30eb\u30c0\u3092\u9078\u629e\"\r\n If .Show = True Then\r\n \u30d5\u30a9\u30eb\u30c0\u3092\u9078\u629e\u3059\u308b = .SelectedItems(1)\r\n End If\r\n End With\r\n\r\nEnd Function<\/pre>\n<\/p>\n","protected":false},"excerpt":{"rendered":"
Dir\u95a2\u6570\u3092\u5229\u7528\u3057\u3001\u9078\u629e\u3057\u305f\u30d5\u30a9\u30eb\u30c0\u306e\u4e2d\u306eCSV\u30d5\u30a1\u30a4\u30eb\u7fa4\u3092\u8aad\u307f\u8fbc\u3093\u30671\u3064\u306e\u30b7\u30fc\u30c8\u306b\u30b3\u30d4\u30fc\u3059\u308b \u4eca\u56de\u306f\u3001Dir\u95a2\u6570\u3092\u5229\u7528\u3057\u3066\u3001\u30d5\u30a9\u30eb\u30c0\u306e\u4e2d\u306eCSV\u30d5\u30a1\u30a4\u30eb\u7fa4\u3092\u5217\u6319\u3057\u3066\uff11\u3064\u306e\u30b7\u30fc\u30c8\u306b\u30b3\u30d4\u30fc\u3059\u308b\u65b9\u6cd5\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002 CSV\u30d5\u30a1\u30a4\u30eb\u306b\u30d8\u30c3\u30c0\u30fc\u304c\u5b58\u5728\u3059\u308b\u5834\u5408\u306f\u3001\u6700\u521d\u306e\u30d5\u30a1\u30a4\u30eb\u3092\u8aad\u307f\u8fbc\u3093\u3060\u30c7\u30fc\u30bf\u306f\u30d8\u30c3\u30c0\u30fc\u4ed8\u304d\u3001\u305d\u308c\u4ee5\u5916\u306f2\u884c\u76ee\u304b\u3089\u306e\u30c7\u30fc\u30bf\u3092\u30b7\u30fc\u30c8\u306b\u8cbc\u308a\u4ed8\u3051\u3057\u307e\u3059\u3002 \u30d8\u30c3\u30c0\u30fc\u304c\u5b58\u5728\u3057\u306a\u3044\u5834\u5408\u306f\u3001\u5168\u3066\u306e\u30c7\u30fc\u30bf\u3092\u30b7 […]<\/p>\n","protected":false},"author":1,"featured_media":599,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[230],"tags":[238,113,269,268,235,231],"class_list":["post-971","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-vba","tag-dir","tag-vba","tag-269","tag-268","tag-235","tag-231"],"_links":{"self":[{"href":"https:\/\/sunafukin.jp\/index.php?rest_route=\/wp\/v2\/posts\/971"}],"collection":[{"href":"https:\/\/sunafukin.jp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sunafukin.jp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sunafukin.jp\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sunafukin.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=971"}],"version-history":[{"count":15,"href":"https:\/\/sunafukin.jp\/index.php?rest_route=\/wp\/v2\/posts\/971\/revisions"}],"predecessor-version":[{"id":1069,"href":"https:\/\/sunafukin.jp\/index.php?rest_route=\/wp\/v2\/posts\/971\/revisions\/1069"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sunafukin.jp\/index.php?rest_route=\/wp\/v2\/media\/599"}],"wp:attachment":[{"href":"https:\/\/sunafukin.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=971"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sunafukin.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=971"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sunafukin.jp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=971"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}