{"id":2674,"date":"2012-06-01T09:00:00","date_gmt":"2012-06-01T01:00:00","guid":{"rendered":"http:\/\/www.neolee.net\/?p=2674"},"modified":"2025-02-12T13:23:39","modified_gmt":"2025-02-12T05:23:39","slug":"data-excel%e5%a4%96%e8%aa%bf%e6%95%b8%e6%93%9a%e5%ba%ab-%e7%94%9f%e6%88%90%e9%80%8f%e8%a6%96%e8%a1%a8","status":"publish","type":"post","link":"https:\/\/www.neolee.net\/?p=2674","title":{"rendered":"Excel DataBase \u5916\u8abf\u6578\u64da\u5eab \u751f\u6210\u900f\u8996\u8868"},"content":{"rendered":"\r\n<p>\u9762\u5c0d\u9f90\u5927\u7684\u6578\u64da\uff0c\u60f3\u9032\u4e00\u6b65\u8981\u6c42\u5de5\u4f5c\u4eba\u54e1\u4e0d\u8981\u904e\u65bc\u4f9d\u8cf4excel\u5b58\u5132\u6578\u64da\uff0c\u61c9\u8a72\u81f3\u5c11\u4f7f\u7528\u684c\u9762\u6578\u64da\u5eab\uff0c\u518d\u5229\u7528Excel\u8abf\u7528\u6578\u64da\uff0c\u9019\u6a23\u5373\u5b89\u5168\u53c8\u65b9\u4fbf\uff0c\u65bc\u662f\u505a\u4e86\u793a\u8303\u7d66\u4ed6\u5011\u5b78\u7fd2\uff0c\u53ef\u662f\u767c\u73fe\u4f3c\u4e4e\u5c0d\u65bc\u5927\u591a\u6578\u8fa6\u516c\u4eba\u54e1\u4f86\u8aaa\u5f88\u8907\u96dc\uff0c\u6559\u7d2f\u5b78\u7d2f\uff0c\u68c4\u4e4b\uff0c\u5f80\u5f8c\u4e5f\u6c92\u6709\u6559\u4ed6\u5011\u5b78\u7fd2\u3002\u56e0\u70ba\u539f\u59cb\u6578\u64da\u76f8\u5c0d\u771f\u5be6\uff0c\u9084\u5728\u4f7f\u7528\u4e2d\uff0c\u70ba\u514d\u9ebb\u7169\uff0c\u6240\u4ee5\u5c31\u653e\u4e0a\u5716\u7247\u4e86\uff0c\u611f\u8208\u8da3\u7684\u53ef\u4ee5\u770b\u6e90\u4ee3\u78bc\u81ea\u5df1\u5beb\u4e86\u6e2c\u8a66\u3002<\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"790\" height=\"483\" class=\"wp-image-2678\" src=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest04.jpg\" alt=\"\" srcset=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest04.jpg 790w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest04-300x183.jpg 300w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest04-768x470.jpg 768w\" sizes=\"auto, (max-width: 790px) 100vw, 790px\" \/><\/figure>\r\n\r\n\r\n\r\n<div class=\"wp-block-image\">\r\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"391\" height=\"159\" class=\"wp-image-2681\" src=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest07-e1595296803572.jpg\" alt=\"\" srcset=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest07-e1595296803572.jpg 391w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest07-e1595296803572-300x122.jpg 300w\" sizes=\"auto, (max-width: 391px) 100vw, 391px\" \/><\/figure>\r\n<\/div>\r\n\r\n\r\n\r\n<p><!--more--><\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"603\" height=\"1024\" class=\"wp-image-2675\" src=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest01-603x1024.jpg\" alt=\"\" srcset=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest01-603x1024.jpg 603w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest01-177x300.jpg 177w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest01.jpg 764w\" sizes=\"auto, (max-width: 603px) 100vw, 603px\" \/><\/figure>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"450\" height=\"1024\" class=\"wp-image-2676\" src=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest02-450x1024.jpg\" alt=\"\" srcset=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest02-450x1024.jpg 450w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest02-132x300.jpg 132w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest02.jpg 709w\" sizes=\"auto, (max-width: 450px) 100vw, 450px\" \/><\/figure>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"666\" height=\"473\" class=\"wp-image-2677\" src=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest03.jpg\" alt=\"\" srcset=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest03.jpg 666w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest03-300x213.jpg 300w\" sizes=\"auto, (max-width: 666px) 100vw, 666px\" \/><\/figure>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"678\" class=\"wp-image-2679\" src=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest05-1024x678.jpg\" alt=\"\" srcset=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest05-1024x678.jpg 1024w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest05-300x199.jpg 300w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest05-768x508.jpg 768w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest05.jpg 1213w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"404\" class=\"wp-image-2680\" src=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest06-1024x404.jpg\" alt=\"\" srcset=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest06-1024x404.jpg 1024w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest06-300x119.jpg 300w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest06-768x303.jpg 768w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/excelrefytest06.jpg 1324w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\r\n\r\n\r\n\r\n<p><textarea style=\"margin: 2px; padding: 2px; background-color: #ffffe1; border: 1px solid;\" cols=\"60\" name=\"\" rows=\"30\">Private Sub Workbook_Open()\r\n\r\n\r\n    Dim strCon As String, iPath As String, i As Integer, iStr As String, iFile As String    &#8216;\u5b9a\u4e49\u53d8\u91cf\r\n   &#8216; On Error Resume Next\r\n  \r\n    iPath = ThisWorkbook.Path            &#8216;\u83b7\u53d6\u8def\u5f84\r\n    iFile = iPath + &#8220;\\VIMTHData.dat&#8221;\r\n  \r\n  With ActiveWorkbook.Connections(&#8220;VIMTHDatabase ODBC&#8221;).ODBCConnection\r\n        .BackgroundQuery = True\r\n        .CommandText = Array( _\r\n        &#8220;SELECT * FROM `&#8221; + iFile + &#8220;`.`\u623f\u6e90\u603b\u8868`&#8221; _\r\n        )\r\n        .CommandType = xlCmdSql\r\n        .Connection = Array(Array( _\r\n        &#8220;ODBC;DSN=MS Access Database;DBQ=&#8221; + iFile + &#8220;;DefaultDir=&#8221; + iPath + &#8220;;&#8221; _\r\n        ), Array( _\r\n        &#8220;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;&#8221; _\r\n        ))\r\n        .RefreshOnFileOpen = False\r\n        .SavePassword = False\r\n        .SourceConnectionFile = &#8220;&#8221;\r\n        .SourceDataFile = &#8220;&#8221;\r\n        .ServerCredentialsMethod = xlCredentialsMethodIntegrated\r\n        .AlwaysUseConnectionFile = False\r\n    End With\r\n    &#8216;ActiveWorkbook.Connections(&#8220;VIMTHDatabase ODBC&#8221;).Refresh\r\n   tb = MsgBox(&#8220;Welcome to VIM ODBC DataBase!&#8221; + vbNewLine + vbNewLine + &#8221;  Ver 0.1 2012 June ELooNG &#8220;, 0 + 64, &#8220;VIM&#8221;)\r\n&#8216;Application.Visible = True\r\nEnd Sub\r\n\r\nSub StartData()\r\n    Dim strCon As String, iPath As String, i As Integer, iStr As String, iFile As String    &#8216;\u5b9a\u4e49\u53d8\u91cf\r\n   &#8216; On Error Resume Next\r\n  \r\n    iPath = ThisWorkbook.Path            &#8216;\u83b7\u53d6\u8def\u5f84\r\n    iFile = iPath + &#8220;\\VIMTHData.dat&#8221;\r\nWith ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _\r\n        &#8220;ODBC;DSN=MS Access Database;DBQ=&#8221; + iFile + &#8220;;DefaultDir=&#8221; + iPath + &#8220;;&#8221; _\r\n        ), Array( _\r\n        &#8220;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;&#8221; _\r\n        )), Destination:=Range(&#8220;$A$1&#8221;)).QueryTable\r\n        .CommandText = Array( _\r\n        &#8220;SELECT * FROM `&#8221; + iFile + &#8220;`.`\u623f\u6e90\u603b\u8868`&#8221; _\r\n        )\r\n        .RowNumbers = False\r\n        .FillAdjacentFormulas = False\r\n        .PreserveFormatting = True\r\n        .RefreshOnFileOpen = False\r\n        .BackgroundQuery = True\r\n        .RefreshStyle = xlInsertDeleteCells\r\n        .SavePassword = False\r\n        .SaveData = True\r\n        .AdjustColumnWidth = True\r\n        .RefreshPeriod = 0\r\n        .PreserveColumnInfo = True\r\n        .SourceConnectionFile = _\r\n        .ListObject.DisplayName = &#8220;\u8868_THDatabase_ODBC&#8221;\r\n        .Refresh BackgroundQuery:=False\r\n   End With\r\nEnd Sub\r\n<\/textarea> <br \/><br \/><textarea style=\"margin: 2px; padding: 2px; background-color: #ffffe1; border: 1px solid;\" cols=\"60\" name=\"\" rows=\"30\">Sub \u6309\u94ae1_Click()\r\n\r\n\r\nDim PWD\r\nPWD = CPSWD(&#8220;ViMeGa&#8221;)\r\nSheets(&#8220;Sheet1&#8221;).Visible = 2\r\nSheets(&#8220;Sheet1&#8221;).Unprotect Password:=PWD\r\n\r\n\r\n   &#8216; With Selection.ListObject.QueryTable\r\n\r\n&#8216;    End With\r\n\r\n\r\nWith ActiveWorkbook.Connections(&#8220;VIMTHDatabase ODBC&#8221;)\r\n.ODBCConnection.BackgroundQuery = False\r\n.Refresh\r\nEnd With\r\n\r\n\r\nmb = MsgBox(&#8220;VIM\u6570\u636e\u66f4\u65b0\u5b8c\u6210\uff01&#8221;, 64 + 0)\r\n\r\n\r\n\r\n&#8216;ActiveWorkbook.Connections(&#8220;VIMTHDatabase ODBC&#8221;).Refresh\r\n&#8216;ActiveWorkbook.RefreshAll\r\n\r\n\r\nSheets(&#8220;Sheet1&#8221;).Protect AllowUsingPivotTables:=True, Password:=PWD\r\n\r\nEnd Sub\r\n\r\n\r\nSub NewPivoTable()\r\n&#8216;\r\nDim SH, SN, PN\r\n&#8216;\r\n\r\nSheets.Add After:=Sheets(Sheets.Count)\r\n\r\n\r\nSN = Sheets(Sheets.Count).Name\r\nPN = &#8220;\u6570\u636e\u900f\u89c6\u8868&#8221; &amp; SN\r\nSN = SN + &#8220;!R3C2&#8221;\r\n\r\n\r\n    MsgBox (SN + PN)\r\n        \r\n    \r\n    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _\r\n        &#8220;\u8868_VIMTHData.mdb_\u623f\u6e90\u603b\u8868&#8221;, Version:=xlPivotTableVersion12).CreatePivotTable _\r\n        TableDestination:=SN, TableName:=PN, DefaultVersion:= _\r\n        xlPivotTableVersion12\r\n   &#8216; Sheets(&#8220;Sheet&#8221; + &#8220;SheetsCount&#8221;).Select\r\n\r\n\r\n    Cells(3, 1).Select\r\n    \r\nEnd Sub\r\nFunction CPSWD(PSWD)\r\nDim regEx, Match, Matches, NPWD\r\n\r\n   Set regEx = CreateObject(&#8220;VBSCRIPT.REGEXP&#8221;)\r\n   regEx.Pattern = &#8220;([A-Za-z-0-9\\W])&#8221;\r\n   regEx.IgnoreCase = True\r\n   regEx.Global = True\r\n   PSWD = PSWD + Chr(255)\r\n   PSWD = PSWD + Chr(16)\r\n   Set Matches = regEx.Execute(PSWD)\r\n   For Each Match In Matches\r\n      NPWD = NPWD + Asc(Match)\r\n   Next\r\n   CPSWD = NPWD\r\n\r\nEnd Function\r\n\r\n\r\n<\/textarea><\/p>\r\n","protected":false},"excerpt":{"rendered":"<p>\u9762\u5c0d\u9f90\u5927\u7684\u6578\u64da\uff0c\u60f3\u9032\u4e00\u6b65\u8981\u6c42\u5de5\u4f5c\u4eba\u54e1\u4e0d\u8981\u904e\u65bc\u4f9d\u8cf4excel\u5b58\u5132\u6578\u64da\uff0c\u61c9\u8a72\u81f3\u5c11\u4f7f\u7528\u684c\u9762\u6578\u64da\u5eab\uff0c\u518d\u5229\u7528Excel\u8abf\u7528\u6578\u64da\uff0c\u9019\u6a23\u5373\u5b89\u5168\u53c8\u65b9\u4fbf\uff0c\u65bc\u662f\u505a\u4e86\u793a\u8303\u7d66\u4ed6\u5011\u5b78\u7fd2\uff0c\u53ef\u662f\u767c\u73fe\u4f3c\u4e4e\u5c0d\u65bc\u5927\u591a\u6578\u8fa6\u516c\u4eba\u54e1\u4f86\u8aaa\u5f88\u8907\u96dc\uff0c\u6559\u7d2f\u5b78\u7d2f\uff0c\u68c4\u4e4b\uff0c\u5f80\u5f8c\u4e5f\u6c92\u6709\u6559\u4ed6\u5011\u5b78\u7fd2\u3002\u56e0\u70ba\u539f\u59cb\u6578\u64da\u76f8\u5c0d\u771f\u5be6\uff0c\u9084\u5728\u4f7f\u7528\u4e2d\uff0c\u70ba\u514d\u9ebb\u7169\uff0c\u6240\u4ee5\u5c31\u653e\u4e0a\u5716\u7247\u4e86\uff0c\u611f\u8208\u8da3\u7684\u53ef\u4ee5\u770b\u6e90\u4ee3&#8230;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"class_list":["post-2674","post","type-post","status-publish","format-standard","hentry","category-cyberworld"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.neolee.net\/index.php?rest_route=\/wp\/v2\/posts\/2674","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.neolee.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.neolee.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.neolee.net\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.neolee.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2674"}],"version-history":[{"count":0,"href":"https:\/\/www.neolee.net\/index.php?rest_route=\/wp\/v2\/posts\/2674\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.neolee.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2674"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.neolee.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2674"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.neolee.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2674"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}