{"id":3576,"date":"2020-06-01T00:30:12","date_gmt":"2020-05-31T15:30:12","guid":{"rendered":"https:\/\/okamurax.com\/?p=3576"},"modified":"2020-06-01T00:31:09","modified_gmt":"2020-05-31T15:31:09","slug":"vba-%e4%bb%95%e5%85%a5%e5%8f%b0%e5%b8%b3%e3%81%8b%e3%82%89%e6%b3%a8%e6%96%87%e6%9b%b8","status":"publish","type":"post","link":"https:\/\/appbay.org\/?p=3576","title":{"rendered":"VBA \u4ed5\u5165\u53f0\u5e33\u304b\u3089\u6ce8\u6587\u66f8"},"content":{"rendered":"

\u53f0\u5e33\u306b\u5165\u529b\u3055\u308c\u305f\u5185\u5bb9\u3092\u6ce8\u6587\u66f8\u306e\u96db\u5f62\u306b\u66f8\u304d\u8fbc\u307f\u3001\u6307\u5b9a\u3057\u305f\u30d6\u30c3\u30af\u306b\u65b0\u3057\u3044\u30b7\u30fc\u30c8\u3068\u3057\u3066\u8907\u5199\u3059\u308b\u3002\u30ec\u30b8\u30b9\u30c8\u30ea\u306b\u30d1\u30b9\u3092\u4fdd\u5b58\u3059\u308b\u3053\u3068\u3067\u30d6\u30c3\u30af\u3092\u8a18\u61b6\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u308b\u3002<\/p>\n

Sub OrderSheet()\r\n\r\n'HKEY_CURRENT_USER\\Software\\VB and VBA Program Setting\r\n\r\nOn Error GoTo exception\r\n\r\nIf (Not ActiveWorkbook Is ThisWorkbook) Then\r\n  MsgBox msg(\"\u4ed5\u5165\u53f0\u5e33\u3067\u8d77\u52d5\u3057\u3066\u304f\u3060\u3055\u3044\")\r\n  Exit Sub\r\nEnd If\r\n\r\nIf ThisWorkbook.ReadOnly = True Then\r\n  If MsgBox(\"\u4ed5\u5165\u53f0\u5e33\u304c\u8aad\u307f\u53d6\u308a\u5c02\u7528\u3067\u3059\u304c\u7d9a\u3051\u307e\u3059\u304b\uff1f\", vbYesNo) = vbNo Then Exit Sub\r\nEnd If\r\n\r\ntRow = Selection(1).Row\r\nbRow = Selection(Selection.Count).Row\r\n\r\nIf (bRow - tRow) + 1 > 12 Then\r\n  If MsgBox(\"\u9078\u629e\u7bc4\u56f2\u304c12\u884c\u3092\u8d85\u3048\u3066\u3044\u307e\u3059\u304c\u7d9a\u3051\u307e\u3059\u304b\uff1f\", vbYesNo) = vbNo Then Exit Sub\r\nEnd If\r\n\r\norderBookPath = GetSetting(\"\u6ce8\u6587\u66f8\", \"\u30d1\u30b9\", Application.UserName)\r\n\r\nIf orderBookPath <> \"\" Then\r\n  isUseRegPath = MsgBox(\"\u8a18\u61b6\u3057\u305f\u4fdd\u5b58\u5148\u3092\u4f7f\u7528\u3057\u307e\u3059\u304b\uff1f\", vbYesNo)\r\nEnd If\r\n\r\nIf isUseRegPath = vbYes Then\r\n\r\n  Workbooks.Open Filename:=orderBookPath\r\n  orderBookName = ActiveWorkbook.Name\r\n  \r\nElse ' vbNo, False\u306e\u5834\u5408\r\n\r\n  orderBookPath = Application.GetOpenFilename(\"Microsoft Excel\u30d6\u30c3\u30af,*.xlsx;*.xlsm\")\r\n  If orderBookPath = False Then Exit Sub\r\n  \r\n  SaveSetting \"\u6ce8\u6587\u66f8\", \"\u30d1\u30b9\", Application.UserName, orderBookPath\r\n  \r\n  Workbooks.Open Filename:=orderBookPath\r\n  orderBookName = ActiveWorkbook.Name\r\n  \r\nEnd If\r\n\r\nIf Workbooks(orderBookName).ReadOnly = True Then\r\n  If MsgBox(\"\u6ce8\u6587\u66f8\u306e\u4fdd\u5b58\u5148\u304c\u8aad\u307f\u53d6\u308a\u5c02\u7528\u3067\u3059\u304c\u7d9a\u3051\u307e\u3059\u304b\uff1f\", vbYesNo) = vbNo Then Exit Sub\r\nEnd If\r\n\r\nSet thissheet = ThisWorkbook.ActiveSheet\r\n\r\nIf StrConv(thissheet.Range(\"E\" & tRow & \"\").Text, vbNarrow) Like \"*\uff97\uff77\uff9e\uff9d\uff78\uff9e*\" Then\r\n  If MsgBox(\"\u30e9\u30ae\u30f3\u30b0\u6ce8\u6587\u66f8\u3092\u4f7f\u7528\u3057\u307e\u3059\u304b\uff1f\", vbYesNo) = vbYes Then\r\n    ThisWorkbook.Sheets(\"\u30e9\u30ae\u30f3\u30b0\u6ce8\u6587\u66f8\").Copy After:=Workbooks(orderBookName).Sheets(Workbooks(orderBookName).Sheets.Count)\r\n  Else\r\n    ThisWorkbook.Sheets(\"\u6ce8\u6587\u66f8\").Copy After:=Workbooks(orderBookName).Sheets(Workbooks(orderBookName).Sheets.Count)\r\n  End If\r\nElse\r\n  ThisWorkbook.Sheets(\"\u6ce8\u6587\u66f8\").Copy After:=Workbooks(orderBookName).Sheets(Workbooks(orderBookName).Sheets.Count)\r\nEnd If\r\n\r\nSet targetSheet = Workbooks(orderBookName).Sheets(Workbooks(orderBookName).Sheets.Count) '\u6ce8\u6587\u66f8\u306e\u8907\u5199\u5f8c\u306b\u4ee3\u5165\r\n\r\ntargetSheet.Range(\"G9\").Value = Application.UserName\r\ntargetSheet.Range(\"A4\").Value = thissheet.Range(\"C\" & tRow & \"\").Text & \"\u3000\u5fa1\u4e2d\"\r\ntargetSheet.Range(\"G15\").Value = Format(thissheet.Range(\"D\" & tRow & \"\").Text, \"yymmdd\")\r\n\r\nIf thissheet.Range(\"D\" & tRow & \"\").Text = \"\" Then\r\n  targetSheet.Range(\"G15\").Value = Format(Now, \"yymmdd\")\r\nEnd If\r\n\r\ntargetRow = 18\r\nFor r = tRow To bRow\r\n\r\n  targetSheet.Range(\"A\" & targetRow & \"\").Value = thissheet.Range(\"E\" & r & \"\").Value & \" \" & thissheet.Range(\"F\" & r & \"\").Value & \" \" & thissheet.Range(\"G\" & r & \"\").Value\r\n  targetSheet.Range(\"B\" & targetRow & \"\").Value = thissheet.Range(\"M\" & r & \"\").Value\r\n  targetSheet.Range(\"C\" & targetRow & \"\").Value = thissheet.Range(\"K\" & r & \"\").Value\r\n  targetSheet.Range(\"D\" & targetRow & \"\").Value = thissheet.Range(\"L\" & r & \"\").Value\r\n  \r\n  If thissheet.Range(\"H\" & r & \"\").Value <> \"\" Then\r\n    targetSheet.Range(\"F\" & targetRow & \"\").Value = thissheet.Range(\"H\" & r & \"\").Value & \"\u3078\u7d0d\u54c1\"\r\n  End If\r\n  \r\n  targetSheet.Range(\"G\" & targetRow & \"\").Value = thissheet.Range(\"I\" & r & \"\").Value\r\n  targetSheet.Range(\"H\" & targetRow & \"\").Value = thissheet.Range(\"J\" & r & \"\").Value\r\n  \r\ntargetRow = targetRow + 1\r\n\r\nNext r\r\n\r\nIf MsgBox(\"\u4ed5\u5165\u53f0\u5e33\u3092\u4fdd\u5b58\u3057\u307e\u3059\u304b\uff1f\", vbYesNo) = vbYes Then\r\n  ThisWorkbook.Save\r\nEnd If\r\n\r\nIf MsgBox(\"\u6ce8\u6587\u66f8\u3092\u4fdd\u5b58\u3057\u307e\u3059\u304b\uff1f\", vbYesNo) = vbYes Then\r\n  Workbooks(orderBookName).Save\r\nEnd If\r\n\r\nExit Sub\r\n\r\nexception:\r\n\r\nMsgBox Err.Description\r\n\r\nEnd Sub<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
\u53f0\u5e33\u306b\u5165\u529b\u3055\u308c\u305f\u5185\u5bb9\u3092\u6ce8\u6587\u66f8\u306e\u96db\u5f62\u306b\u66f8\u304d\u8fbc\u307f\u3001\u6307\u5b9a\u3057\u305f\u30d6\u30c3\u30af\u306b\u65b0\u3057\u3044\u30b7\u30fc\u30c8\u3068\u3057\u3066\u8907\u5199\u3059\u308b\u3002\u30ec\u30b8\u30b9\u30c8\u30ea\u306b\u30d1\u30b9\u3092\u4fdd\u5b58\u3059\u308b\u3053\u3068\u3067\u30d6\u30c3\u30af\u3092\u8a18\u61b6\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u308b\u3002 Sub OrderSheet() ‘HKEY_CURRENT_US … <\/p>\n
“VBA \u4ed5\u5165\u53f0\u5e33\u304b\u3089\u6ce8\u6587\u66f8” \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[5],"class_list":["post-3576","post","type-post","status-publish","format-standard","hentry","category-1","tag-vba"],"_links":{"self":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/3576","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3576"}],"version-history":[{"count":2,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/3576\/revisions"}],"predecessor-version":[{"id":3578,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/3576\/revisions\/3578"}],"wp:attachment":[{"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3576"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3576"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3576"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}