{"id":149,"date":"2023-07-22T18:52:47","date_gmt":"2023-07-22T09:52:47","guid":{"rendered":"https:\/\/rafa-system.com\/vba\/?page_id=149"},"modified":"2023-08-01T21:29:31","modified_gmt":"2023-08-01T12:29:31","slug":"page-149","status":"publish","type":"page","link":"https:\/\/rafa-system.com\/vba\/page-149\/","title":{"rendered":"SQL\u7528\u30af\u30e9\u30b9\u30e2\u30b8\u30e5\u30fc\u30eb"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">\u4f7f\u7528\u4f8b<\/h2>\n\n\n\n<pre class=\"wp-block-luxe-blocks-syntaxhighlighter line-numbers language-vba\"><code class=\"language-vba\">Sub sqlclass()\n    Dim sql As DataExcelSQL: Set sql = New DataExcelSQL\n    Dim ws As Worksheet: Set ws = Sheet2\n    \n    Dim strSQL As String: strSQL = _\n    \"SELECT SC,\u540d\u79f0,\u5e02\u5834,\u696d\u7a2e,\u682a\u4fa1 FROM [Sheet1$] WHERE \u682a\u4fa1>2000;\"\n\n    sql.mRead (strSQL)\n    If Not sql.pDataExists Then Exit Sub\n    ws.Cells.Clear\n    sql.mOutput ws, \"A1\", True\nEnd Sub<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">\u30e1\u30bd\u30c3\u30c9\u3068\u30d7\u30ed\u30d1\u30c6\u30a3<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">\u30e1\u30bd\u30c3\u30c9<\/h3>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td>\u540d\u79f0<\/td><td>return<\/td><td>\u8aac\u660e<\/td><\/tr><tr><td>Class_Initialize<\/td><td>\u7121\u3057<\/td><td>\u521d\u671f\u5316\u51e6\u7406<\/td><\/tr><tr><td>mRead<\/td><td>\u7121\u3057<\/td><td>SQL\u306eSELECT\u6587\u3092\u5b9f\u884c\u3059\u308b\u3002<br>\u5f15\u65701\uff08\u5fc5\u9808\uff09\uff1aSQL\u6587\u3092String\u578b\u3067\u6e21\u3059<\/td><\/tr><tr><td>mCrud<\/td><td>\u7121\u3057<\/td><td>SQL\u306eINSERT\/UPDATE\u3092\u5b9f\u884c\u3059\u308b\uff08\u3081\u3063\u305f\u306b\u4f7f\u308f\u306a\u3044\uff09<br>\u5f15\u65701\uff08\u5fc5\u9808\uff09\uff1aSQL\u6587\u3092String\u578b\u3067\u6e21\u3059<\/td><\/tr><tr><td>mOutput<\/td><td>\u7121\u3057<\/td><td>SQL\u3067\u53d6\u5f97\u3057\u305f\u30c7\u30fc\u30bf\u3092\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u306b\u51fa\u529b\u3059\u308b\u3000<br>\u5f15\u65701\uff08\u5fc5\u9808\uff09\uff1aWorksheet\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u6e21\u3059<br>\u5f15\u65702\uff08\u5fc5\u9808\uff09\uff1a\u30bb\u30eb\u756a\u5730\u3092String\u578b\u3067\u6e21\u3059\u3000&#8221;A1&#8243; \u306a\u3069<br>\u5f15\u65703\uff08\u4efb\u610f\uff09\uff1a\u30d8\u30c3\u30c0\u30fc\u6709\u7121\u3092True\/False\u3067\u6307\u5b9a\u3000\u7701\u7565\u3057\u305f\u3089True<\/td><\/tr><tr><td>mPrintTableInfo<\/td><td>\u7121\u3057<\/td><td>SQL\u3067\u53d6\u5f97\u3057\u305f\u30c7\u30fc\u30bf\u306e\u5217\u6570\u3068\u884c\u6570\u3092\u30a4\u30df\u30c7\u30a3\u30a8\u30a4\u30c8\u30a6\u30a3\u30f3\u30c9\u30a6\u306bprint\u3059\u308b<\/td><\/tr><tr><td>printError<\/td><td>\u7121\u3057<\/td><td>\u30c7\u30d0\u30c3\u30b0\u7528\u3000\u30a8\u30e9\u30fc\u30e1\u30c3\u30bb\u30fc\u30b8\u3092print\u3059\u308b<br>\u5f15\u65701\uff08\u5fc5\u9808\uff09\uff1aString\u578b\u3067\u30a8\u30e9\u30fc\u304c\u767a\u751f\u3057\u305f\u95a2\u6570\u540d\u306a\u3069\u6e21\u3059<br>\u5f15\u65702\uff08\u5fc5\u9808\uff09\uff1aString\u578b\u306e\u30a8\u30e9\u30fc\u6587\u3092\u81ea\u5206\u3067\u66f8\u304f\u304berr.Description\u3067\u6e21\u3059<br>\u5f15\u65703\uff08\u4efb\u610f\uff09\uff1aLong\u578b\u306e\u30a8\u30e9\u30fc\u756a\u53f7\u3000err.Number \u3067OK<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">\u30d7\u30ed\u30d1\u30c6\u30a3<\/h3>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td>\u540d\u79f0<\/td><td>return<\/td><td>\u8aac\u660e<\/td><\/tr><tr><td>pDataExists<\/td><td>Boolean\u578b<\/td><td>SQL\u3067\u53d6\u5f97\u3057\u305f\u30c7\u30fc\u30bf\u304c\u5b58\u5728\u3059\u308b\u304b\u8fd4\u3059<\/td><\/tr><tr><td>pArrDataBody<\/td><td>Variant\u578b<\/td><td>SQL\u3067\u53d6\u5f97\u3057\u305f\u30c7\u30fc\u30bf\u3092\u4e8c\u6b21\u5143\u306e\u914d\u5217\u5909\u6570\u3067\u8fd4\u3059<br>\u5f15\u65701\uff08\u4efb\u610f\uff09\uff1a\u30d8\u30c3\u30c0\u30fc\u6709\u7121\u3092True\/False\u3067\u6307\u5b9a\u3000\u7701\u7565\u3057\u305f\u3089True<\/td><\/tr><tr><td>pArrColumns<\/td><td>Variant\u578b<\/td><td>SQL\u3067\u53d6\u5f97\u3057\u305f\u30c7\u30fc\u30bf\u306e\u30d8\u30c3\u30c0\u30fc\u3092\u4e00\u6b21\u5143\u914d\u5217\u3067\u8fd4\u3059<\/td><\/tr><tr><td>pStrColumns<\/td><td>String\u578b<\/td><td>SQL\u3067\u53d6\u5f97\u3057\u305f\u30c7\u30fc\u30bf\u306e\u30d8\u30c3\u30c0\u30fc\u3092\u30ab\u30f3\u30de\u533a\u5207\u308a\u306e\u6587\u5b57\u5217\u3067\u8fd4\u3059<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">\u30af\u30e9\u30b9\u30e2\u30b8\u30e5\u30fc\u30eb<\/h2>\n\n\n\n<p>\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u540d\uff1aDataExcelSQL<\/p>\n\n\n\n<pre class=\"wp-block-luxe-blocks-syntaxhighlighter line-numbers language-vba\"><code class=\"language-vba\">Option Explicit\n'=============================================================================\n'\u53c2\u7167\u8a2d\u5b9a\u300cMicrosoft ActiveX Data Objects 2.8 Library\u300d\uff086.1\u3067\u3082OK\u30012.7\u4ee5\u4e0b\u306f\u672a\u691c\u8a3c\uff09\n'\u53c2\u7167\u8a2d\u5b9a\u300cMicrosoft Scripting Runtime\u300d\n'=============================================================================\n\nPrivate cn As ADODB.Connection\nPrivate rs As ADODB.Recordset\nPrivate colIndex As Scripting.Dictionary\nPrivate dataBody() As Variant\nPrivate dataExists As Boolean\n\nPrivate Sub Class_Initialize()\n    Set colIndex = New Scripting.Dictionary\n    Set cn = New ADODB.Connection\n    Set rs = New ADODB.Recordset\n    cn.Provider = \"MSDASQL\"\n    cn.ConnectionString = _\n    \"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};\" &amp; _\n    \"DBQ=\" &amp; ThisWorkbook.FullName &amp; \"; ReadOnly=True;\"\nEnd Sub\n\n'SELECT\u7528\nPublic Sub mRead(strSQL As String)\n    cn.Open 'DB\u63a5\u7d9a\n    \n    'SQL\u5b9f\u884c\n    On Error GoTo omg\n        rs.Open strSQL, cn, adOpenStatic\n    On Error GoTo 0\n    \n    ' \u30ec\u30b3\u30fc\u30c9\u306e\u53d6\u5f97 Transpose\u3067\u5411\u304d\u5909\u66f4\n    Dim tmp As Variant\n    If Not rs.EOF Then tmp = rs.GetRows\n    dataBody = WorksheetFunction.Transpose(tmp)\n    dataExists = True\n    \n    ' \u5217\u306eIndex\u4f5c\u6210\n    ' count\u30d7\u30ed\u30d1\u30c6\u30a3\u306f1\u30d9\u30fc\u30b9\u306a\u306e\u306b\u30b3\u30ec\u30af\u30b7\u30e7\u30f3\u306f0\u30d9\u30fc\u30b9\u3063\u307d\u3044\u306e\u3067-1\u5fc5\u8981\n    Dim i As Long\n    For i = 0 To rs.Fields.Count - 1\n        colIndex.Add rs.Fields(i).Name, i + 1\n    Next i\n    \n    rs.Close: cn.Close 'DB\u5207\u65ad\n    Exit Sub\n'-----------------------------------------------------------------\n'\u30a8\u30e9\u30fc\u51e6\u7406\nomg:\n    If Not rs.State = 0 Then rs.Close\n    Set rs = Nothing\n    cn.Close: Set cn = Nothing\n    Debug.Print Time() &amp; \" SQL\u5931\u6557\"\n    Debug.Print strSQL\n    Call printError(\"mRead\", Err.Description, Err.Number)\nEnd Sub\n\n'\u30c7\u30d0\u30c3\u30b0\u7528\u3000\u30a8\u30e9\u30fc\u30e1\u30c3\u30bb\u30fc\u30b8\u3092print\u3059\u308b\nPrivate Sub printError(errWhere As String, errDescription As String, Optional errNumber As Long)\n    Debug.Print String(\"50\", \"-\")\n    \n    If errNumber = 0 Then\n        Debug.Print errWhere &amp; \"\uff1a \" &amp; errDescription\n    Else\n        Dim str As String: str = Replace(errDescription, \"\u3002\", \"\u3002\\n\")\n        Dim arr: arr = Split(str, \"\\n\")\n        \n        Debug.Print errWhere &amp; \"\uff1aERROR NUMBER \" &amp; Err.Number\n        \n        Dim i As Long\n        For i = 0 To UBound(arr) - 1\n            Debug.Print arr(i)\n        Next i\n    End If\n    \n    Debug.Print String(\"50\", \"-\")\nEnd Sub\n\n'INSERT\/UPDATE\u7528\nPublic Sub mCrud(ByVal strSQL As String)\n    cn.Open 'DB\u63a5\u7d9a\n    \n    'SQL\u5b9f\u884c\n    On Error GoTo omg\n        rs.Open strSQL, cn, adOpenStatic\n    On Error GoTo 0\n    \n    'DB\u5207\u65ad\n    If Not rs.State = 0 Then rs.Close\n    cn.Close\n    Exit Sub\n'-----------------------------------------------------------------\n'\u30a8\u30e9\u30fc\u51e6\u7406\nomg:\n    If Not rs.State = 0 Then rs.Close\n    Set rs = Nothing\n    cn.Close: Set cn = Nothing\n    Call printError(\"mCrud\", Err.Description, Err.Number)\nEnd Sub\n\n'\u30c6\u30fc\u30d6\u30eb\u3092\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u306b\u51fa\u529b\u3059\u308b\u3000header\u304cTRUE\u306a\u3089\u30d8\u30c3\u30c0\u30fc\u3092\u542b\u3080\nPublic Sub mOutput(ws As Worksheet, rng As String, Optional header As Boolean = True)\n    If Not dataExists Then\n        Call printError(\"mOutput\u30a8\u30e9\u30fc\", \"\u51fa\u529b\u3067\u304d\u308b\u30c7\u30fc\u30bf\u304c\u3042\u308a\u307e\u305b\u3093\u3002\")\n        Exit Sub\n    End If\n    \n    Dim arr: arr = Me.pArrDataBody(header)\n    ws.Range(rng).Resize(UBound(arr), UBound(arr, 2)) = arr\nEnd Sub\n\n'\u30c6\u30fc\u30d6\u30eb\u306e\u884c\u6570\u3068\u5217\u6570\u3092\u30a4\u30df\u7a93\u306bprint\u3059\u308b\nPublic Sub mPrintTableInfo()\n    Debug.Print String(\"50\", \"-\")\n    Debug.Print \"mPrintTableInfo\"\n    \n    If dataExists Then\n        Debug.Print \"\u884c\u6570\uff1a\" &amp; UBound(dataBody) &amp; \" (\u203b\u30d8\u30c3\u30c0\u30fc\u306f\u542b\u307e\u306a\u3044\u6570)\"\n        Debug.Print \"\u5217\u6570\uff1a\" &amp; UBound(colIndex.Keys) + 1 &amp; Replace(\" (?)\", \"?\", Me.pStrColumns)\n    Else\n        Debug.Print \"\u307e\u3060\u53d6\u5f97\u3057\u305f\u30c7\u30fc\u30bf\u306f\u3042\u308a\u307e\u305b\u3093\"\n    End If\n    Debug.Print String(\"50\", \"-\")\nEnd Sub\n\n'\u8fd4\u3059\u30c7\u30fc\u30bf\u578b\uff1aBoolean\uff5c\u5185\u5bb9\uff1aSQL\u3067\u53d6\u5f97\u3057\u305f\u30c7\u30fc\u30bf\u304c\u3042\u3063\u305f\u3089True\u3001\u7121\u3044\u306a\u3089False\nPublic Property Get pDataExists() As Boolean\n    pDataExists = dataExists\nEnd Property\n\n'\u8fd4\u3059\u30c7\u30fc\u30bf\u578b\uff1a\u4e00\u6b21\u5143\u914d\u5217\uff08\u8d77\u70b90\uff09\uff5c\u5185\u5bb9\uff1a\u5217\u540d\u306e\u6587\u5b57\u5217\nPublic Property Get pArrColumns() As Variant\n    If Not dataExists Then\n        Call printError(\"pArrColumns\", \"\u51fa\u529b\u3067\u304d\u308b\u30c7\u30fc\u30bf\u304c\u3042\u308a\u307e\u305b\u3093\u3002\")\n        Exit Property\n    End If\n    pArrColumns = colIndex.Keys\nEnd Property\n\n'\u8fd4\u3059\u30c7\u30fc\u30bf\u578b\uff1aString\uff5c\u5185\u5bb9\uff1a\u5217\u540d\u3092\u30ab\u30f3\u30de\u533a\u5207\u308a\u3057\u305f\u6587\u5b57\u5217\nPublic Property Get pStrColumns() As String\n    If Not dataExists Then\n        Call printError(\"pStrColumns\", \"\u51fa\u529b\u3067\u304d\u308b\u30c7\u30fc\u30bf\u304c\u3042\u308a\u307e\u305b\u3093\u3002\")\n        Exit Property\n    End If\n    \n    Dim arr: arr = colIndex.Keys\n    Dim i As Long, tmp As String\n    For i = 0 To UBound(arr)\n        tmp = tmp &amp; CStr(arr(i)) &amp; \",\"\n    Next i\n    pStrColumns = Left(tmp, Len(tmp) - 1)\nEnd Property\n\n'\u8fd4\u3059\u30c7\u30fc\u30bf\u578b\uff1a\u4e8c\u6b21\u5143\u914d\u5217\uff08\u8d77\u70b91\uff09\uff5c\u5185\u5bb9\uff1a\u4fdd\u6709\u4e2d\u306e\u30c6\u30fc\u30d6\u30eb\uff5cHeader\u304cTrue\u306a\u3089\u30d8\u30c3\u30c0\u30fc\u8fbc\u307f\u3001False\u306a\u3089\u30c7\u30fc\u30bf\u90e8\u5206\u306e\u307f\nPublic Property Get pArrDataBody(Optional header As Boolean = True) As Variant\n    If Not dataExists Then\n        Call printError(\"pArrDataBody\", \"\u51fa\u529b\u3067\u304d\u308b\u30c7\u30fc\u30bf\u304c\u3042\u308a\u307e\u305b\u3093\u3002\")\n        Exit Property\n    End If\n    \n    If Not header Then\n        pArrDataBody = dataBody\n    Else\n        Dim i As Long, j As Long, hd: hd = Me.pArrColumns\n        Dim arr() As Variant\n        ReDim arr(1 To UBound(dataBody) + 1, 1 To UBound(dataBody, 2))\n        '\u30d8\u30c3\u30c0\u30fc\u90e8\u5206\n        For i = 1 To UBound(arr, 2)\n            arr(1, i) = hd(i - 1)\n        Next i\n        '\u30c7\u30fc\u30bf\u90e8\u5206\n        For i = 1 To UBound(dataBody)\n            For j = 1 To UBound(arr, 2)\n                arr(i + 1, j) = dataBody(i, j)\n            Next j\n        Next i\n        pArrDataBody = arr\n    End If\nEnd Property<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u4f7f\u7528\u4f8b \u30e1\u30bd\u30c3\u30c9\u3068\u30d7\u30ed\u30d1\u30c6\u30a3 \u30e1\u30bd\u30c3\u30c9 \u540d\u79f0 return \u8aac\u660e Class_Initialize \u7121\u3057 \u521d\u671f\u5316\u51e6\u7406 mRead \u7121\u3057 SQL\u306eSELECT\u6587\u3092\u5b9f\u884c\u3059\u308b\u3002\u5f15\u65701\uff08\u5fc5\u9808\uff09\uff1aSQL\u6587\u3092String\u578b\u3067\u6e21\u3059 m [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-149","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/rafa-system.com\/vba\/wp-json\/wp\/v2\/pages\/149","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rafa-system.com\/vba\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/rafa-system.com\/vba\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/rafa-system.com\/vba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rafa-system.com\/vba\/wp-json\/wp\/v2\/comments?post=149"}],"version-history":[{"count":11,"href":"https:\/\/rafa-system.com\/vba\/wp-json\/wp\/v2\/pages\/149\/revisions"}],"predecessor-version":[{"id":269,"href":"https:\/\/rafa-system.com\/vba\/wp-json\/wp\/v2\/pages\/149\/revisions\/269"}],"wp:attachment":[{"href":"https:\/\/rafa-system.com\/vba\/wp-json\/wp\/v2\/media?parent=149"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}