{"id":2475,"date":"2018-10-09T18:04:36","date_gmt":"2018-10-09T09:04:36","guid":{"rendered":"http:\/\/okamurax.com\/?p=2475"},"modified":"2020-05-10T17:40:07","modified_gmt":"2020-05-10T08:40:07","slug":"vba-%e3%82%a8%e3%82%af%e3%82%bb%e3%83%ab%e3%81%8b%e3%82%89sql%e5%ae%9f%e8%a1%8c","status":"publish","type":"post","link":"https:\/\/appbay.org\/?p=2475","title":{"rendered":"VBA SQL\u5b9f\u9a13\u7528\u2462(SQL Server)"},"content":{"rendered":"

\u4ee5\u524d\u4f5c\u3063\u305f\u3082\u306e\u306bSELECT\u4ee5\u5916\u3082\u52d5\u304f\u3088\u3046\u306b\u5c11\u3057\u4fee\u6b63\u3002SQL\u306e\u52d5\u304d\u3092\u8abf\u3079\u308b\u306e\u306b\u30a8\u30af\u30bb\u30eb\u304b\u3089\u30c6\u30fc\u30d6\u30eb\u306e\u4f5c\u6210\u3084SQL\u306e\u5b9f\u884c\u304c\u3067\u304d\u308b\u3068\u697d\u3002<\/p>\n

Sub auto_open()\r\n\r\nApplication.CommandBars(\"cell\").Reset\r\n\r\nWith Application.CommandBars(\"cell\").Controls.Add\r\n.OnAction = \"t\"\r\n.Caption = \"\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\u30fb\u767b\u9332\"\r\nEnd With\r\n\r\nWith Application.CommandBars(\"cell\").Controls.Add\r\n.OnAction = \"e\"\r\n.Caption = \"SQL\u5b9f\u884c\"\r\nEnd With\r\n\r\nEnd Sub\r\n\r\nSub auto_close()\r\n\r\nOn Error Resume Next\r\n\r\nFor i = 1 To 2\r\n  Application.CommandBars(\"cell\").Controls(\"\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\u30fb\u767b\u9332\").Delete\r\n  Application.CommandBars(\"cell\").Controls(\"SQL\u5b9f\u884c\").Delete\r\nNext i\r\n\r\nEnd Sub\r\n\r\nSub t()\r\n\r\nSet cn = CreateObject(\"adodb.connection\")\r\ncn.Open \"Provider=SQLOLEDB;Data Source=PC\\SQLEXPRESS;Initial Catalog=my_database;Integrated Security=SSPI;\"\r\n\r\ntbl = InputBox(\"\u30c6\u30fc\u30d6\u30eb\u540d\")\r\nIf tbl = \"\" Then Exit Sub\r\n\r\n'\u30c6\u30fc\u30d6\u30eb\u524a\u9664\r\n\r\nOn Error Resume Next\r\ncn.Execute \"drop table \" & tbl\r\n\r\n'\u30c6\u30fc\u30d6\u30eb\u4f5c\u6210\r\n\r\nOn Error GoTo 0\r\n\r\nfld = \"\"\r\n\r\nFor c = Selection(1).Column To Selection(Selection.Count).Column\r\n\r\n  Select Case Right(ActiveSheet.Cells(Selection(1).Row, c).Value, 1)\r\n    Case \"s\"\r\n    tmp = \"varchar(255)\"\r\n    Case \"i\"\r\n    tmp = \"bigint\"\r\n    Case \"m\"\r\n    tmp = \"money\"\r\n    Case \"d\"\r\n    tmp = \"datetime\"\r\n  End Select\r\n  \r\n  fld = fld & ActiveSheet.Cells(Selection(1).Row, c).Value & \" \" & tmp & \",\"\r\n\r\nNext c\r\n\r\nfld = Left(fld, Len(fld) - 1)\r\n\r\nq = \"create table \" & tbl & \" (id integer identity(1,1) primary key,\" & fld & \")\": Debug.Print q\r\ncn.Execute q\r\n\r\n'\u30c7\u30fc\u30bf\u767b\u9332\r\n\r\nfld = \"\"\r\n\r\nFor c = Selection(1).Column To Selection(Selection.Count).Column\r\n  fld = fld & ActiveSheet.Cells(Selection(1).Row, c).Value & \",\"\r\nNext c\r\n\r\nfld = Left(fld, Len(fld) - 1)\r\n\r\nFor r = (Selection(1).Row) + 1 To Selection(Selection.Count).Row\r\n  For cc = Selection(1).Column To Selection(Selection.Count).Column\r\n    rec = rec & \"'\" & ActiveSheet.Cells(r, cc).Value & \"'\" & \",\"\r\n  Next cc\r\n\r\n  rec = Left(rec, Len(rec) - 1)\r\n  \r\n  q = \"insert into \" & tbl & \" (\" & fld & \") values (\" & rec & \")\": Debug.Print q\r\n  cn.Execute q\r\n\r\n  rec = \"\"\r\nNext r\r\n\r\nIf cn.State = 1 Then cn.Close\r\nSet cn = Nothing\r\n\r\nMsgBox \"done\"\r\n\r\nEnd Sub\r\n\r\nSub s(q)\r\n\r\nSet cn = CreateObject(\"adodb.connection\")\r\nSet rn = CreateObject(\"adodb.recordset\")\r\n\r\ncn.Open \"Provider=SQLOLEDB;Data Source=PC\\SQLEXPRESS;Initial Catalog=my_database;Integrated Security=SSPI;\"\r\nrn.Open q, cn\r\n\r\n'\u30d5\u30a3\u30fc\u30eb\u30c9\u4f5c\u6210\r\n\r\ni = 0\r\n\r\nFor c = Selection(1).Column To (Selection(1).Column + rn.Fields.Count) - 1\r\n  Cells(Selection(Selection.Count).Row + 1, c).Value = rn.Fields(i).Name\r\n  i = i + 1\r\nNext c\r\n\r\n'\u30c7\u30fc\u30bf\u8aad\u8fbc\r\n\r\nCells(Selection(Selection.Count).Row + 2, Selection(1).Column).CopyFromRecordset rn\r\n\r\nIf rn.State = 1 Then rn.Close\r\nSet rn = Nothing\r\n\r\nIf cn.State = 1 Then cn.Close\r\nSet cn = Nothing\r\n\r\nEnd Sub\r\n\r\nSub o(q)\r\n\r\nSet cn = CreateObject(\"adodb.connection\")\r\n\r\ncn.Open \"Provider=SQLOLEDB;Data Source=PC\\SQLEXPRESS;Initial Catalog=my_database;Integrated Security=SSPI;\"\r\ncn.Execute q\r\n\r\nIf cn.State = 1 Then cn.Close\r\nSet cn = Nothing\r\n\r\nEnd Sub\r\n\r\nSub e()\r\n\r\nIf Selection.Rows.Count = 1 Then\r\n  q = ActiveCell.Value\r\nElse\r\n  For r = 1 To Selection.Rows.Count\r\n    q = q & \" \" & Selection(r)\r\n  Next r\r\nEnd If\r\n\r\nIf Left(q, 1) = \"s\" Then\r\n  s (q)\r\nElse\r\n  o (q)\r\nEnd If\r\n\r\nEnd Sub\r\n<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
\u4ee5\u524d\u4f5c\u3063\u305f\u3082\u306e\u306bSELECT\u4ee5\u5916\u3082\u52d5\u304f\u3088\u3046\u306b\u5c11\u3057\u4fee\u6b63\u3002SQL\u306e\u52d5\u304d\u3092\u8abf\u3079\u308b\u306e\u306b\u30a8\u30af\u30bb\u30eb\u304b\u3089\u30c6\u30fc\u30d6\u30eb\u306e\u4f5c\u6210\u3084SQL\u306e\u5b9f\u884c\u304c\u3067\u304d\u308b\u3068\u697d\u3002 Sub auto_open() Application.CommandBars(“ce … <\/p>\n
“VBA SQL\u5b9f\u9a13\u7528\u2462(SQL Server)” \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-2475","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\/2475","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=2475"}],"version-history":[{"count":3,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/2475\/revisions"}],"predecessor-version":[{"id":3471,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/2475\/revisions\/3471"}],"wp:attachment":[{"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2475"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2475"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2475"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}