SQL\u306e\u6319\u52d5\u3092\u8abf\u3079\u308b\u306e\u306b\u30a8\u30af\u30bb\u30eb\u304b\u3089\u624b\u8efd\u306bSQL\u3092\u767a\u884c<\/p>\n
Sub test()\r\n\r\nApplication.CommandBars(\"cell\").Reset\r\n\r\nEnd Sub\r\n\r\nSub auto_open()\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 = \"tt\"\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\nWith ActiveSheet\r\n\r\ntbl = InputBox(\"\u30c6\u30fc\u30d6\u30eb\u540d\")\r\n\r\nSet cn = CreateObject(\"adodb.connection\")\r\ncn.Open \"provider=microsoft.jet.oledb.4.0;data source=\" & ThisWorkbook.Path & \"\\SQL\u5b9f\u9a13.mdb\"\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\nfld = \"\"\r\n\r\nFor c = Selection(1).Column To Selection(Selection.Count).Column\r\n\r\n Select Case Right(.Cells(Selection(1).Row, c).Value, 1)\r\n Case \"s\"\r\n tmp = \"varchar\" 'char\u306f\u77ed\u3044\u30c6\u30ad\u30b9\u30c8(varchar\u306f\u53ef\u5909\u9577), text\u306f\u9577\u3044\u30c6\u30ad\u30b9\u30c8\r\n Case \"i\"\r\n tmp = \"integer\"\r\n End Select\r\n \r\n fld = fld & .Cells(Selection(1).Row, c).Value & \" \" & tmp & \",\"\r\n\r\nNext c\r\n\r\nfld = Left(fld, Len(fld) - 1)\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\r\n fld = fld & .Cells(Selection(1).Row, c).Value & \",\"\r\n\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\r\n For cc = Selection(1).Column To Selection(Selection.Count).Column\r\n \r\n rec = rec & \"'\" & .Cells(r, cc).Value & \"'\" & \",\"\r\n \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\n \r\nNext r\r\n\r\ncn.Close\r\nSet cn = Nothing\r\n\r\nMsgBox \"done\"\r\n\r\nEnd With\r\n\r\nEnd Sub\r\n\r\nSub tt()\r\n\r\nWith ActiveSheet\r\n\r\nSet cn = CreateObject(\"adodb.connection\")\r\nSet rn = CreateObject(\"adodb.recordset\")\r\n\r\ncn.Open \"provider=microsoft.jet.oledb.4.0;data source=\" & ThisWorkbook.Path & \"\\SQL\u5b9f\u9a13.mdb\"\r\nrn.Open ActiveCell.Value, cn\r\n\r\n'\u30d5\u30a3\u30fc\u30eb\u30c9\u4f5c\u6210\r\n\r\ni = 0\r\nFor c = Selection(1).Column To (Selection(1).Column + rn.Fields.Count) - 1\r\n ActiveCell.Offset(1, i).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\nActiveCell.Offset(2, 0).CopyFromRecordset rn\r\n\r\nrn.Close\r\nSet rn = Nothing\r\n\r\ncn.Close\r\nSet cn = Nothing\r\n\r\nEnd With\r\n\r\nEnd Sub\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"SQL\u306e\u6319\u52d5\u3092\u8abf\u3079\u308b\u306e\u306b\u30a8\u30af\u30bb\u30eb\u304b\u3089\u624b\u8efd\u306bSQL\u3092\u767a\u884c Sub test() Application.CommandBars(“cell”).Reset End Sub Sub auto_open() With Appl … <\/p>\n