{"id":12512,"date":"2020-07-30T06:00:00","date_gmt":"2020-07-29T21:00:00","guid":{"rendered":"https:\/\/www.muscle-hypertrophy.com\/?p=12512"},"modified":"2021-04-24T10:31:51","modified_gmt":"2021-04-24T01:31:51","slug":"excel-vba-%e3%81%a7%e6%97%a2%e5%ad%98%e3%81%ae%e3%83%86%e3%83%bc%e3%83%96%e3%83%ab%e3%81%ab-workbook-%e3%81%8b%e3%82%89%e3%83%87%e3%83%bc%e3%82%bf%e3%82%92%e8%bf%bd%e8%a8%98%e3%81%99%e3%82%8b","status":"publish","type":"post","link":"https:\/\/www.muscle-hypertrophy.com\/?p=12512","title":{"rendered":"EXCEL VBA \u3067\u65e2\u5b58\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u30cd\u30c3\u30c8\u30ef\u30fc\u30af\u30c9\u30e9\u30a4\u30d6\u4e0a\u306e Workbook \u304b\u3089\u30c7\u30fc\u30bf\u3092\u8ffd\u8a18\u3059\u308b"},"content":{"rendered":"\n

\u3000\u524d\u56de\u306e\u8a18\u4e8b<\/a>\u3067\u306f\u30d5\u30a9\u30eb\u30c0\u30fc\u304b\u3089\u4e00\u62ec\u3057\u3066\u30c7\u30fc\u30bf\u3092\u8aad\u307f\u8fbc\u3080\u65b9\u6cd5\u3092\u7d39\u4ecb\u3057\u305f\uff0e\u4eca\u56de\u306f\u6708\u6b21\u306e\u66f4\u65b0\u30d5\u30a1\u30a4\u30eb\u3092\u8aad\u307f\u8fbc\u3093\u3067\u65e2\u5b58\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u30c7\u30fc\u30bf\u3092\u8ffd\u8a18\u3059\u308b\u65b9\u6cd5\u3092\u7d39\u4ecb\u3059\u308b\uff0e<\/p>\n

\u3000\u65e2\u306b\u8aad\u307f\u8fbc\u3093\u3060\u30d5\u30a1\u30a4\u30eb\u306f\u62d2\u5426\u3057\u305f\u3044\uff0e\u76e3\u67fb\u3068\u3057\u3066\u306e\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u304c\u5fc5\u8981\u3060\uff0e\u30ea\u30ec\u30fc\u30b7\u30e7\u30ca\u30eb\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306a\u3089\u30c7\u30fc\u30bf\u306e\u4e00\u610f\u5236\u7d04\u304b\u3089\u53ef\u80fd\u3060\u304c\uff0cEXCEL \u3067\u306f\u81ea\u524d\u3067\u4f5c\u308b\u5fc5\u8981\u304c\u3042\u308b\uff0e<\/p>\n

\u3000\u8272\u3005\u3068\u6311\u6226\u3057\u304c\u3044\u306e\u3042\u308b\u8ab2\u984c\u3067\u3042\u3063\u305f\uff0e<\/p>\n

<\/p>\n

\u30cd\u30c3\u30c8\u30ef\u30fc\u30af\u30c9\u30e9\u30a4\u30d6\u4e0a\u306e\u30d5\u30a1\u30a4\u30eb\u9078\u629e<\/h2>\n

\u30e6\u30fc\u30b6\u30fc\u306b\u30d5\u30a1\u30a4\u30eb\u3092\u9078\u3070\u305b\u308b\u306a\u3089 GetOpenFilename \u95a2\u6570\uff0c\u306a\u306e\u3060\u304c…<\/h3>\n

\u3000Application.GetOpenFilename \u95a2\u6570\u3092\u4f7f\u3046\u3068\uff0c\u30c0\u30a4\u30a2\u30ed\u30b0\u3092\u958b\u3044\u3066\u30e6\u30fc\u30b6\u30fc\u306b\u30d5\u30a1\u30a4\u30eb\u3092\u9078\u3070\u305b\u308b\u3053\u3068\u304c\u3067\u304d\u308b\uff0e\u623b\u308a\u5024\u306f Variant \u578b\u3067\u5ba3\u8a00\u3057\u3066\u304a\u304d\uff0c\u30e6\u30fc\u30b6\u30fc\u306e\u884c\u52d5\u3092\u5b8c\u5168\u306b\u6355\u6349\u3059\u308b\uff0e<\/p>\n

\u3000\u30ed\u30fc\u30ab\u30eb\u30c9\u30e9\u30a4\u30d6\u306b\u3042\u308b\u30d5\u30a1\u30a4\u30eb\u306a\u3089\uff0c\u4e0b\u8a18\u306e\u30b3\u30fc\u30c9\u3067\u5bfe\u5fdc\u3067\u304d\u308b\uff0e<\/p>\n

Dim myPath    As Variant\nmyPath = Application.GetOpenFilename(\"Excel, *.xls?, , , , False\")\nIf myPath = False Then Exit Sub<\/pre>\n
\u30cd\u30c3\u30c8\u30ef\u30fc\u30af\u30c9\u30e9\u30a4\u30d6\u4e0a\u306e\u30d5\u30a1\u30a4\u30eb\u30d1\u30b9\u3092\u53d6\u5f97\u3059\u308b\u306b\u306f Windows Scripting Host \u304c\u5fc5\u8981<\/h3>\n
\u3000\u6700\u521d\u304b\u3089\u3064\u307e\u3065\u304d\u305d\u3046\u306b\u306a\u308b\uff0e\u5b9f\u969b\u306b\u306f\u30e6\u30fc\u30b6\u30fc\u30a4\u30f3\u30bf\u30fc\u30d5\u30a7\u30fc\u30b9\u3067\u306f\u30cd\u30c3\u30c8\u30ef\u30fc\u30af\u3092\u9078\u3076\u3053\u3068\u304c\u3067\u304d\u306a\u3044\uff0e\u30ed\u30fc\u30ab\u30eb\u30c9\u30e9\u30a4\u30d6\u4e0a\u3067\u306f\u7c21\u5358\u306b\u53d6\u5f97\u3067\u304d\u308b\u30d5\u30a1\u30a4\u30eb\u30d1\u30b9\u304c\uff0c\u30cd\u30c3\u30c8\u30ef\u30fc\u30af\u4e0a\u306b\u5909\u308f\u308b\u3060\u3051\u3067\u96e3\u6613\u5ea6\u304c\u4e0a\u304c\u308b\uff0e<\/p>\n
\u3000\u30cd\u30c3\u30c8\u30ef\u30fc\u30af\u4e0a\u306e\u30d5\u30a1\u30a4\u30eb\u30d1\u30b9\u3092\u53d6\u5f97\u3059\u308b<\/a>\u306b\u306f Windows Scripting Host \u304c\u5fc5\u8981\u306b\u306a\u308b\uff0eObject \u578b\u306e\u5909\u6570\u3092\u5ba3\u8a00\u3057\u3066\u30a4\u30f3\u30b9\u30bf\u30f3\u30b9\u3092\u751f\u6210\u3059\u308b\uff0e<\/p>\n
\u3000\u7b46\u8005\u306e\u74b0\u5883\u3060\u3068\u30cd\u30c3\u30c8\u30ef\u30fc\u30af\u4e0a\u306e\u30d5\u30a1\u30a4\u30eb\u30d1\u30b9\u306f \\\\192.168.xxx.yy\\ \u3067\u59cb\u307e\u308b\u6587\u5b57\u5217\u3067\u3042\u308b\uff0e\u53d6\u5f97\u5143\u306b\u306a\u308b\u30d5\u30a1\u30a4\u30eb\u7fa4\u3068\u53d6\u5f97\u5148\u3067\u3042\u308b\u81ea\u5206\u81ea\u8eab\u3068\u304c\u540c\u3058\u30c7\u30a3\u30ec\u30af\u30c8\u30ea\u306b\u3042\u308b\u3068\uff0c\u8a71\u304c\u65e9\u3044\uff0e<\/p>\n
Dim myObj   As Object\nSet myObj = CreateObject(\"WScript.Shell\")\nWith myObj\n    .Currentdirectory = ThisWorkbook.Path\nEnd With\nDim myPath  As Variant\nmyPath = Application.GetOpenFilename(\"Excel, *.xls?, , , , False\")\nIf myPath = False Then Exit Sub<\/pre>\n
Workbooks.Open \u3067 Workbook \u3092\u53d6\u5f97\u3059\u308b<\/h3>\n
\u3000GetOpenFilename \u95a2\u6570\u3067\u5f97\u305f\u30d5\u30a1\u30a4\u30eb\u30d1\u30b9\u3092\u5f15\u6570\u3068\u3057\u3066\u6e21\u3057\uff0cWorkbook \u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u53d6\u5f97\u3059\u308b\uff0e3 \u756a\u76ee\u306e\u5f15\u6570\u306f\u8aad\u307f\u53d6\u308a\u5c02\u7528\u3092\u8868\u3057\u3066\u3044\u308b\uff0e<\/p>\n
Dim myWB    As Workbook\nSet myWB = Workbooks.Open(myPath, , True)<\/pre>\n
\u76e3\u67fb\u30c6\u30fc\u30d6\u30eb<\/h2>\n
\u76e3\u67fb\u30c6\u30fc\u30d6\u30eb\u306e\u3042\u308b\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u3092\u53d6\u5f97\u3059\u308b<\/h3>\n
\u3000\u76e3\u67fb\u30c6\u30fc\u30d6\u30eb\uff0c\u307e\u305f\u306f\u30ed\u30b0\u3068\u3082\u3044\u3046\u304c\uff0c\u3053\u308c\u306f\u30e6\u30fc\u30b6\u30fc\u306e\u6307\u5b9a\u3057\u305f\u30d5\u30a1\u30a4\u30eb\u304c\u65e2\u306b\u53d6\u5f97\u6e08\u307f\u3067\u3042\u308b\u304b\u5426\u304b\u3092\u76e3\u67fb\u3059\u308b\u5f79\u5272\u3092\u679c\u305f\u3059\uff0e\u5177\u4f53\u7684\u306b\u306f\uff0c\u30e6\u30fc\u30b6\u30fc\u306e\u6307\u5b9a\u3057\u305f\u30d5\u30a1\u30a4\u30eb\u540d\u304c\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u306b\u5b58\u5728\u3059\u308b\u304b\u691c\u7d22\u3057\uff0c\u5b58\u5728\u3059\u308c\u3070\u51e6\u7406\u3092\u4e2d\u65ad\u3059\u308b\uff0e\u5b58\u5728\u3057\u306a\u3051\u308c\u3070\u5165\u529b\u51e6\u7406\u3078\u3068\u9032\u3080\uff0e<\/p>\n
\u3000\u307e\u305a\u306f\u76e3\u67fb\u30c6\u30fc\u30d6\u30eb\u306e\u3042\u308b\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u3092\u53d6\u5f97\u3059\u308b\uff0eSheet3 \u306b\u3042\u308b\u3082\u306e\u3068\u3059\u308b\uff0e<\/p>\n
Dim mySh    As Worksheet\nDim myRng   As Range\nSet mySh = ThisWorkbook.Worksheets(\"Sheet3\")\nSet myRng = mySh.Range(\"A1\").CurrentRegion<\/pre>\n
\u76e3\u67fb\u30c6\u30fc\u30d6\u30eb\u3092\u691c\u7d22\u3059\u308b<\/h3>\n
\u3000\u76e3\u67fb\u30c6\u30fc\u30d6\u30eb\u3068\u8a00\u3063\u3066\u3082\uff0c\u30d5\u30a1\u30a4\u30eb\u540d\u3068\u30d5\u30a1\u30a4\u30eb\u4f5c\u6210\u65e5\u304b\u3089\u306a\u308b\u30c6\u30fc\u30d6\u30eb\u306b\u904e\u304e\u306a\u3044\uff0e\u3053\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u691c\u7d22\u3057\uff0c\u30e6\u30fc\u30b6\u30fc\u306e\u6307\u5b9a\u3057\u305f\u30d5\u30a1\u30a4\u30eb\u540d\u304c\u5b58\u5728\u3059\u308b\u304b\u5426\u304b\u3067\u51e6\u7406\u7d99\u7d9a\u306e\u53ef\u5426\u3092\u6c7a\u3081\u308b\uff0e\u305d\u308c\u3060\u3051\u3067\u3042\u308b\uff0e\u7b46\u8005\u306e\u30b9\u30ad\u30eb\u3067\u306f\u3053\u308c\u4ee5\u4e0a\u306e\u6a5f\u80fd\u306f\u5b9f\u88c5\u3067\u304d\u306a\u3044\uff0e<\/p>\n
\u3000\u4e0b\u8a18\u30b3\u30fc\u30c9\u306e Else \u7bc0\u4ee5\u964d\u306f\u76e3\u67fb\u30c6\u30fc\u30d6\u30eb\u306b\u30e6\u30fc\u30b6\u30fc\u306e\u6307\u5b9a\u3057\u305f\u30d5\u30a1\u30a4\u30eb\u540d\u304c\u898b\u3064\u304b\u3063\u305f\u5834\u5408\uff0c\u3064\u307e\u308a\u65e2\u306b\u30d5\u30a1\u30a4\u30eb\u304c\u53d6\u308a\u8fbc\u307f\u6e08\u307f\u3067\u3042\u308b\u5834\u5408\u306e\u51e6\u7406\u3067\u3042\u308b\uff0e\u3064\u307e\u308a\uff0c\u30e6\u30fc\u30b6\u30fc\u306e\u6307\u5b9a\u3057\u305f\u30d5\u30a1\u30a4\u30eb\u3092\u9589\u3058\u3066\u30d7\u30ed\u30b7\u30fc\u30b8\u30e3\u3092\u7d42\u4e86\u3057\u3066\u3044\u308b\uff0e\u672c\u6765\u306a\u3089\u30d5\u30a1\u30a4\u30eb\u4f5c\u6210\u65e5\u306e\u4e00\u81f4\u306e\u662f\u975e\u3082\u5224\u5b9a\u57fa\u6e96\u306b\u52a0\u3048\u308b\u3079\u304d\u3060\u304c\uff0c\u5272\u611b\u3059\u308b\uff0e<\/p>\n
Dim mySh    As Worksheet
Dim myRng   As Range
Set mySh = ThisWorkbook.Worksheets(\"Sheet3\")\nSet myRng = mySh.Range(\"A1\").CurrentRegion\nSet myRng = myRng.Find(what:=myWB.Name)\nIf myRng Is Nothing Then\n    \nElse\n    myWB.Close\n    Exit Sub\nEnd If<\/pre>\n
\u3000\u3053\u3053\u304b\u3089\u5148\uff0c\u81ea\u5206\u3067\u3082\u306a\u305c\u3053\u3093\u306a\u30b3\u30fc\u30c9\u3092\u66f8\u3044\u305f\u306e\u304b\u601d\u3044\u51fa\u305b\u306a\u3044\uff0e\u304c\uff0c\u610f\u56f3\u901a\u308a\u306b\u52d5\u3044\u3066\u3044\u308b\uff0e\u524d\u534a\u306f\u76e3\u67fb\u30c6\u30fc\u30d6\u30eb\u306b\u30d5\u30a1\u30a4\u30eb\u540d\u3068\u4f5c\u6210\u65e5\u3092\u8ffd\u8a18\u3057\u3066\u304a\u308a\uff0c\u5f8c\u534a\u306f\u914d\u5217\u306b\u53d6\u5f97\u3057\u3066\u304b\u3089\u30c6\u30fc\u30d6\u30eb\u306b\u30c7\u30fc\u30bf\u3092\u8ffd\u8a18\u3057\u3066\u3044\u308b\uff0e<\/p>\n
FileSystemObject \u3092\u4f7f\u3046\u306e\u306f\u30d5\u30a1\u30a4\u30eb\u4f5c\u6210\u65e5\u3092\u53c2\u7167\u3059\u308b\u305f\u3081<\/h3>\n
\u3000\u30d5\u30a9\u30eb\u30c0\u5185\u306e Files \u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u3092\u53d6\u5f97\u3059\u308b\u306b\u306f\uff0c FileSystemObect \u306b GetFolder() \u95a2\u6570\u3092\u9069\u7528\u3057\u3066 Folder \u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u53d6\u5f97\u3057\uff0cFiles \u30d7\u30ed\u30d1\u30c6\u30a3\u3092\u9069\u7528\u3059\u308b\uff0e\u3053\u3053\u3067 FileSystemObject \u3092\u4f7f\u3046\u306e\u306f\u30d5\u30a1\u30a4\u30eb\u4f5c\u6210\u65e5\u3092\u53c2\u7167\u3059\u308b\u305f\u3081\u3067\u3042\u308a\uff0cWorkbook \u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u306b\u306f\u305d\u306e\u3088\u3046\u306a\u30e1\u30bf\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\u3059\u308b\u30d7\u30ed\u30d1\u30c6\u30a3\u306f\u5b58\u5728\u3057\u306a\u3044\uff0e<\/p>\n
Dim myFSO   As Scripting.FileSystemObject\nSet myFSO = New Scripting.FileSystemObject\nDim myFiles As Scripting.Files\nDim myFile  As Scripting.File\nSet myFiles = myFSO.GetFolder(ThisWorkbook.Path).Files<\/pre>\n
\u76e3\u67fb\u30c6\u30fc\u30d6\u30eb\u3078\u306e\u66f8\u304d\u8fbc\u307f<\/h3>\n
\u3000Files \u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u3092\u30eb\u30fc\u30d7\u3057\u3066 File \u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u53d6\u5f97\u3059\u308b\uff0eFile.Name \u3068 Workbook.Name \u304c\u4e00\u81f4\u3057\u305f\u5834\u5408\u306b\u30d5\u30a1\u30a4\u30eb\u540d\u3068\u4f5c\u6210\u65e5\u3092\u66f8\u304d\u8fbc\u3080\uff0e\u7e70\u308a\u8fd4\u3059\u304c\uff0c\u30d5\u30a1\u30a4\u30eb\u4f5c\u6210\u65e5\u306f FileSystemObject<\/a> \u304b\u3089\u3067\u306a\u3044\u3068\u53c2\u7167\u3067\u304d\u306a\u3044\uff0e<\/p>\n
Dim mySh    As Worksheet\nDim myRng   As Range\nSet mySh = ThisWorkbook.Worksheets(\"Sheet3\")\nSet myRng = mySh.Range(\"A1\").CurrentRegion\nSet myRng = myRng.Find(what:=myWB.Name)\nIf myRng Is Nothing Then\n    \n    For Each myFile In myFiles\n        If myFile.Name = myWB.Name Then\n            Set myRng = mySh.Range(\"A1\").CurrentRegion\n            Set myRng = myRng.Offset(myRng.Rows.Count)Resize(1, 2)\n            myRng.Cells(1, 1) = myWB.Name\n            myRng.Cells(1, 2) = myFile.DateCreated\n            \n        End If\n    Next myFile\nElse\n    myWB.Close\n    Exit Sub\nEnd If<\/pre>\n
\u30c7\u30fc\u30bf\u306e\u53d6\u5f97\u304b\u3089\u30c6\u30fc\u30d6\u30eb\u3078\u306e\u66f8\u304d\u8fbc\u307f\u307e\u3067<\/h2>\n
Worksheets \u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u3092\u30eb\u30fc\u30d7\u3057\u3066 Worksheet \u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u53d6\u5f97<\/h3>\n
\u30009 \u884c\u76ee\u304b\u3089 11 \u884c\u76ee\u3067 Worksheets \u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u3092\u30eb\u30fc\u30d7\u3057\u3066 Worksheet \u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u53d6\u5f97\u3059\u308b\uff0e\u3053\u306e\u3042\u305f\u308a\u306e\u51e6\u7406\u306f\u3088\u304f\u898b\u304b\u3051\u308b\u3082\u306e\u3067\u3042\u308b\uff0e<\/p>\n
If myRng Is Nothing Then\n    \n    For Each myFile In myFiles\n        If myFile.Name = myWB.Name Then\n            Set myRng = mySh.Range(\"A1\").CurrentRegion\n            Set myRng = myRng.Offset(myRng.Rows.Count)Resize(1, 2)\n            myRng.Cells(1, 1) = myWB.Name\n            myRng.Cells(1, 2) = myFile.DateCreated\n            For Each mySh In myWB.Worksheets\n                \n            Next mySh\n        End If\n    Next myFile\nElse\n    myWB.Close\n    Exit Sub\nEnd If<\/pre>\n
Range \u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u53d6\u5f97\u3057\u3066\u52d5\u7684\u914d\u5217\u306b\u4ee3\u5165<\/h3>\n
\u3000\u5fc5\u8981\u306a\u5909\u6570\u3092\u5ba3\u8a00\u3057\u3066 Range \u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u53d6\u5f97\u3057\uff0c\u52d5\u7684\u914d\u5217\u306b\u4ee3\u5165\u3057\u3066\u3044\u304f\uff0e\u3053\u306e\u3042\u305f\u308a\u306e\u51e6\u7406\u306f\u524d\u56de<\/a>\u3068\u540c\u69d8\u3067\u3042\u308b\uff0e<\/p>\n
Dim myVar    As Variant\nDim i        As Long\nDim j        As Long\nDim myID()   As String\nDim myName() As String\nDim myDate() As Date\nDim myTest() As Single\nDim myYear() As Long\nDim myMonth()    As Long\nDim myDay()  As Long\n\nIf myRng Is Nothing Then\n    j = 0\n    For Each myFile In myFiles\n        If myFile.Name = myWB.Name Then\n            Set myRng = mySh.Range(\"A1\").CurrentRegion\n            Set myRng = myRng.Offset(myRng.Rows.Count)Resize(1, 2)\n            myRng.Cells(1, 1) = myWB.Name\n            myRng.Cells(1, 2) = myFile.DateCreated\n            For Each mySh In myWB.Worksheets\n                Set myRng = Intersect(mySh.Range(\"A1\").CurrentRegion, mySh.Range(\"A:D\"))\n                Set myRng = myRng.Resize(myRng.Rows.Count - 1).Offset(1)\n                myVar = myRng\n                For i = LBound(myVar) to UBound(myVar)\n                    Redim Preserve myID(j)\n                    Redim Preserve myName(j)\n                    Redim Preserve myDate(j)\n                    Redim Preserve myTest(j)\n                    Redim Preserve myYear(j)\n                    Redim Preserve myMonth(j)\n                    Redim Preserve myDay(j)\n                    myID(j) = myVar(i, 1)\n                    myName(j) = myVar(i, 2)\n                    myDate(j) = myVar(i, 3)\n                    myTest(j) = myVar(i, 4)\n                    myYear(j) = myVar(i, 3)\n                    myMonth(j) = myVar(i, 3)\n                    myDay(j) = myVar(i, 3)\n                    j = j + 1\n                Next i\n            Next mySh\n        End If\n    Next myFile\n    \nElse\n    myWB.Close\n    Exit Sub\nEnd If<\/pre>\n
\u30c6\u30fc\u30d6\u30eb\u3078\u306e\u30c7\u30fc\u30bf\u8ffd\u8a18\u306f ListRows.Add<\/h3>\n
\u3000\u30c6\u30fc\u30d6\u30eb\u3078\u306e\u30c7\u30fc\u30bf\u8ffd\u8a18\u306f\u884c\u5358\u4f4d\u3067\u884c\u3046\uff0e\u5177\u4f53\u7684\u306b\u306f 50 \u884c\u76ee\u306e ListRows.Add \u3067 ListRow \u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u53d6\u5f97\u3059\u308b\uff0e\u6700\u7d42\u7684\u306b Range \u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3067\u53d6\u5f97\u3057\u3066\u3044\u308b\u304c\uff0c\u3053\u306e\u65b9\u6cd5\u3092\u898b\u3064\u3051\u305f\u6642\u306f\uff0c\u5c11\u3057\u611f\u52d5\u3057\u305f\uff0e<\/p>\n
\u300051 \u884c\u76ee\u3067 Array \u95a2\u6570\u3067\u914d\u5217\u5316\u3057\u3066\u3044\u308b\u306e\u306f\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u3078\u306e\u30a2\u30af\u30bb\u30b9\u56de\u6570\u3092\u6e1b\u3089\u3057\u305f\u3044\u305f\u3081\u3067\u3042\u308b\uff0e\u30eb\u30fc\u30d7 1 \u56de\u3042\u305f\u308a 1 \u56de\u306e\u30a2\u30af\u30bb\u30b9\u3068\u306a\u3063\u3066\u3044\u308b\uff0e<\/p>\n
Dim myVar    As Variant\nDim i        As Long\nDim j        As Long\nDim myID()   As String\nDim myName() As String\nDim myDate() As Date\nDim myTest() As Single\nDim myYear() As Long\nDim myMonth()    As Long\nDim myDay()  As Long\n\nDim myLstObj As ListObject\nDim myArray  As Variant\n\nIf myRng Is Nothing Then\n    j = 0\n    For Each myFile In myFiles\n        If myFile.Name = myWB.Name Then\n            Set myRng = mySh.Range(\"A1\").CurrentRegion\n            Set myRng = myRng.Offset(myRng.Rows.Count)Resize(1, 2)\n            myRng.Cells(1, 1) = myWB.Name\n            myRng.Cells(1, 2) = myFile.DateCreated\n            For Each mySh In myWB.Worksheets\n                Set myRng = Intersect(mySh.Range(\"A1\").CurrentRegion, mySh.Range(\"A:D\"))\n                Set myRng = myRng.Resize(myRng.Rows.Count - 1).Offset(1)\n                myVar = myRng\n                For i = LBound(myVar) to UBound(myVar)\n                    Redim Preserve myID(j)\n                    Redim Preserve myName(j)\n                    Redim Preserve myDate(j)\n                    Redim Preserve myTest(j)\n                    Redim Preserve myYear(j)\n                    Redim Preserve myMonth(j)\n                    Redim Preserve myDay(j)\n                    myID(j) = myVar(i, 1)\n                    myName(j) = myVar(i, 2)\n                    myDate(j) = myVar(i, 3)\n                    myTest(j) = myVar(i, 4)\n                    myYear(j) = myVar(i, 3)\n                    myMonth(j) = myVar(i, 3)\n                    myDay(j) = myVar(i, 3)\n                    j = j + 1\n                Next i\n            Next mySh\n        End If\n    Next myFile\n    Set mySh = Worksheets(\"Sheet2\")\n    Set myLstObj = mySh.ListObjects(1)\n    For i = LBound(myID) to UBound(myID)\n        Set myRng = myLstObj.ListRows.Add.Range\n        myArray = Array(myID(i), myName(i), myDate(i), myTest(i), myYear(i), myMonth(i), myDay(i))\n        myRng = myArray\n    Next i\nElse\n    myWB.Close\n    Exit Sub\nEnd If<\/pre>\n
\u3000\u4eca\u56de\u306e\u5b66\u3073\u306f\u3053\u306e\u90e8\u5206\u306e\u8a18\u8ff0\u3067\u3042\u308b\uff0e\u30c6\u30fc\u30d6\u30eb\u3067\u306f\u306a\u3044\u901a\u5e38\u306e Range \u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u306b\u5bfe\u3057\u3066\u306f\u4e8c\u6b21\u5143\u914d\u5217\u3092\u4ee3\u5165\u3057\u3066\u4e00\u56de\u306e\u66f8\u304d\u8fbc\u307f\u3067\u51e6\u7406\u3092\u7d42\u4e86\u3067\u304d\u308b\u304c\uff0c\u30c6\u30fc\u30d6\u30eb\u306e\u5834\u5408\u3060\u3068\u305d\u306e\u3088\u3046\u306a\u65b9\u6cd5\u304c\u601d\u3044\u3064\u304b\u306a\u3044\uff0eListRow \u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u306f ListRows.Add \u3068\u3057\u3066\u53d6\u5f97\u3059\u308b\uff0eSQL \u3067\u8a00\u3046\u3068\u3053\u308d\u306e\u4e0b\u8a18\u306e\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u306b\u8a72\u5f53\u3059\u308b\uff0e<\/p>\n
INSERT INTO Table VALUES(value1, value2, ...)<\/pre>\n
\u3000\u3053\u306e\u8a18\u8ff0\u3067\u306f 1 \u884c\u305a\u3064\u3057\u304b\u30c6\u30fc\u30d6\u30eb\u3092\u62e1\u5f35\u3067\u304d\u306a\u3044\uff0eBULK INSERT \u306b\u8a72\u5f53\u3059\u308b\uff0c\u4f55\u304b\u4e00\u62ec\u3057\u3066\u4ee3\u5165\u3059\u308b\u65b9\u6cd5\u304c\u3042\u308c\u3070\u826f\u3044\u306e\u3060\u304c\uff0e<\/p>\n
\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u30d6\u30e9\u30a6\u30b6\u30fc\u3068 SQL \u304b\u3089\u898b\u308b ListRow \u30aa\u30d6\u30b8\u30a7\u30af\u30c8<\/h2>\n
\u3000SQL \u3067\u983b\u7528\u3059\u308b\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u306f SELECT, INSERT, UPDATE, DELETE \u3067\u3042\u308b\uff0eINSERT \u4ee5\u5916\u306f WHERE \u53e5\u3068\u30bb\u30c3\u30c8\u3067\u30ec\u30b3\u30fc\u30c9\u3092\u7279\u5b9a\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\uff0cVBA \u3067\u306f ListRows.Item(Index) \u3042\u305f\u308a\u306b\u306a\u308b\u3060\u308d\u3046\u304b\uff0eUPDATE \u306a\u3089 Range \u30d7\u30ed\u30d1\u30c6\u30a3\u3092\u6307\u5b9a\u3057\u3066\u5b9f\u969b\u306b\u66f8\u304d\u63db\u3048\u3092\u884c\u3044\uff0cDELETE \u306a\u3089 LisRow.Delete \u3068\u306a\u308b\u3060\u308d\u3046\uff0e<\/p>\n
ListRows \u30b3\u30ec\u30af\u30b7\u30e7\u30f3<\/h3>\n\n\n\n\n\n\n\n\n\n
MEMBER<\/th>\n\u00a0<\/th>\nType of Return<\/th>\n<\/tr>\n
Add([Position], [AlwaysInsert])<\/td>\nFunction<\/td>\nListRow<\/td>\n<\/tr>\n
Application<\/td>\nProperty<\/td>\nApplication<\/td>\n<\/tr>\n
Count<\/td>\nProperty<\/td>\nLong<\/td>\n<\/tr>\n
Creator<\/td>\nProperty<\/td>\nXlCreator<\/td>\n<\/tr>\n
Item(Index)<\/td>\nProperty<\/td>\nListRow<\/td>\n<\/tr>\n
Parent<\/td>\nProperty<\/td>\nObject<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
ListRow \u30aa\u30d6\u30b8\u30a7\u30af\u30c8<\/h3>\n\n\n\n\n\n\n\n\n\n
MEMBER<\/th>\n\u00a0<\/th>\nType of Return<\/th>\n<\/tr>\n
Application<\/td>\nProperty<\/td>\nApplication<\/td>\n<\/tr>\n
Creator<\/td>\nProperty<\/td>\nXlCreator<\/td>\n<\/tr>\n
Delete<\/td>\nSub<\/td>\n\u00a0<\/td>\n<\/tr>\n
Index<\/td>\nProperty<\/td>\nLong<\/td>\n<\/tr>\n
Parent<\/td>\nProperty<\/td>\nObject<\/td>\n<\/tr>\n
Range<\/td>\nProperty<\/td>\nRange<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/a>\"\"<\/p>\n","protected":false},"excerpt":{"rendered":"
\u3000\u524d\u56de\u306e\u8a18\u4e8b\u3067\u306f\u30d5\u30a9\u30eb\u30c0\u30fc\u304b\u3089\u4e00\u62ec\u3057\u3066\u30c7\u30fc\u30bf\u3092\u8aad\u307f\u8fbc\u3080\u65b9\u6cd5\u3092\u7d39\u4ecb\u3057\u305f\uff0e\u4eca\u56de\u306f\u6708\u6b21\u306e\u66f4\u65b0\u30d5\u30a1\u30a4\u30eb\u3092\u8aad\u307f\u8fbc\u3093\u3067\u65e2\u5b58\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u30c7\u30fc\u30bf\u3092\u8ffd\u8a18\u3059\u308b\u65b9\u6cd5\u3092\u7d39\u4ecb\u3059\u308b\uff0e \u3000\u65e2\u306b\u8aad\u307f\u8fbc\u3093\u3060\u30d5\u30a1\u30a4\u30eb\u306f\u62d2\u5426\u3057\u305f\u3044\uff0e\u76e3\u67fb\u3068\u3057\u3066\u306e\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u304c … <\/p>\n
“EXCEL VBA \u3067\u65e2\u5b58\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u30cd\u30c3\u30c8\u30ef\u30fc\u30af\u30c9\u30e9\u30a4\u30d6\u4e0a\u306e Workbook \u304b\u3089\u30c7\u30fc\u30bf\u3092\u8ffd\u8a18\u3059\u308b” \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":12507,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[8392,4527,4693,8412,7550,5475,8404,5471,8391,4694,8393,8400,8322,4698,265,8388,4651,8071,8409,8315,8305,8405,8408,8402,8407,5815,8406,8384,8385,6839,6745,8413,5120,8418,5818,8417,4586,6738,8415,8411,4528,5816,5468,4773,5007,5465,4530,4616,8416,5484,5466,5470,8311,4672,259,8394,5819,6392,5479,8333,459,8403,5123,8414,8389,4727,8410,8395,4523,4599,8421,4554,4411,462,6349,646,8383,4825,8401,7053,8377,3575,7667,4845,464,4593,5202,8397,8390,8382,8379,8399,8332,5455,4503,3549,8386,8380,8381,8398,8396,8378],"class_list":{"0":"post-12512","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","6":"hentry","7":"category-technology","8":"tag-wscript-shell","9":"tag-add","10":"tag-application","11":"tag-array","12":"tag-bulk-insert","13":"tag-cells","14":"tag-close","15":"tag-count-2","16":"tag-createobject","17":"tag-creator","18":"tag-currentdirectory","19":"tag-currentregion","20":"tag-datecreated","21":"tag-delete","22":"tag-excel","23":"tag-exit-sub","24":"tag-false","25":"tag-file","26":"tag-file-name","27":"tag-files","28":"tag-filesystemobject","31":"tag-find","32":"tag-folder","33":"tag-for-each","34":"tag-getfolder","35":"tag-getopenfilename","37":"tag-index","38":"tag-insert","39":"tag-insert-into","40":"tag-intersect","41":"tag-itemindex","42":"tag-lbound","43":"tag-lisrow-delete","44":"tag-listobjects","45":"tag-listrows-add","46":"tag-listrows-itemindex","47":"tag-listrow","48":"tag-name","49":"tag-next","50":"tag-nothing","51":"tag-object","53":"tag-offset","54":"tag-range","57":"tag-redim-preserve","58":"tag-resize","59":"tag-rows","60":"tag-scripting","61":"tag-select","62":"tag-sql","63":"tag-thisworkbook-path","64":"tag-ubound","65":"tag-update","66":"tag-values-2","67":"tag-variant-","68":"tag-vba","69":"tag-what","70":"tag-where","72":"tag-windows-scripting-host","73":"tag-workbook","74":"tag-workbook-name","75":"tag-workbooks-open","76":"tag-worksheet","77":"tag-worksheets","78":"tag-xlcreator","79":"tag-4554","80":"tag-4411","81":"tag-462","82":"tag-6349","83":"tag-646","84":"tag-8383","85":"tag-4825","86":"tag-8401","87":"tag-7053","88":"tag-8377","89":"tag-3575","90":"tag-7667","91":"tag-4845","92":"tag-464","93":"tag-4593","94":"tag-5202","95":"tag-8397","96":"tag-8390","97":"tag-8382","98":"tag-8379","99":"tag-8399","100":"tag-8332","101":"tag-5455","102":"tag-4503","103":"tag-3549","104":"tag-8386","105":"tag-8380","106":"tag-8381","107":"tag-8398","108":"tag-8396","109":"tag-8378"},"_links":{"self":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts\/12512","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=12512"}],"version-history":[{"count":92,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts\/12512\/revisions"}],"predecessor-version":[{"id":14288,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts\/12512\/revisions\/14288"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/media\/12507"}],"wp:attachment":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=12512"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=12512"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=12512"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}