vba 包含 SQL 表的 Excel vlookup
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20977348/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel vlookup incorporating SQL table
提问by RumDemon
I have an Excel spreadsheet which I use to calculate the cost of a product we sell, (happens to be car insurance). This product is calculated based on various pieces of information; the customers age, postcode, vehicle, occupation, etc etc.
我有一个 Excel 电子表格，用于计算我们销售的产品的成本（恰好是汽车保险）。本产品是根据各种信息计算得出的；客户年龄、邮政编码、车辆、职业等。
I enter information onto the main tab of my spreadsheet and then perform lots of vlookup formulas to return the relevant figures from other tabs of the spreadsheet. An example of one of my vlookups is:
我在电子表格的主选项卡上输入信息，然后执行大量查找公式以从电子表格的其他选项卡返回相关数字。我的一个 vlookups 的一个例子是：
=VLOOKUP(G1,RatesInsurerX!A36:B986786,2,FALSE)
=VLOOKUP(G1,RatesInsurerX!A36:B986786,2,FALSE)
So this looks up the value I enter into cell G1 an matches it to the data found on the workbook tab called RatesInsurerX.
因此，这会查找我在单元格 G1 中输入的值，并将其与在名为 RatesInsurerX 的工作簿选项卡上找到的数据进行匹配。
As you can see this particular table contains nearly 1 million rows and excel is starting to struggle so I was wondering if there is a way of doing exactly the same thing only performing the lookup against a SQL table rather than the RatesInsurerX workbook?
正如您所看到的，这个特定的表包含近 100 万行，并且 excel 开始挣扎，所以我想知道是否有一种方法可以做完全相同的事情，只对 SQL 表而不是 RatesInsurerX 工作簿执行查找？
回答by bendataclear
Good news is you can do this without VBA, quite a few steps though as follows:
好消息是，您可以在没有 的情况下执行此操作VBA，尽管如下所示：
1 . First add a new sheet, call this something meaningful like PoscodeLookup.
1 . 首先添加一个新工作表，将其命名为有意义的，例如PoscodeLookup.
2 . Next go to Dataand select Other Sourcesand Microsoft Query:
2 . 接下来转到Data并选择Other Sources和Microsoft Query：
3 . Next select (or create) an ODBC data source that can connect you to your database asd select this (you may need to enter user/pass).
3 . 接下来选择（或创建）一个可以将您连接到数据库的 ODBC 数据源，并选择此选项（您可能需要输入用户/密码）。
4 . The query designer will ask you to select a table, just click close.
4 . 查询设计器会要求您选择一个表，只需单击关闭。
5 . Next select the SQL button:
5 . 接下来选择 SQL 按钮：
6 . Now enter the SQL query to get the single value you need, using an example postcode eg:
6 . 现在输入 SQL 查询以获取您需要的单个值，使用示例邮政编码，例如：
SELECT TOP 1 [Value] FROM [MyTable] WHERE [Postcode] = 'AB12 1AA';
7 . Now hit OK and OK which should return a single value in the window.
7 . 现在点击 OK 和 OK 这应该在窗口中返回一个值。
8 . Click on Return datain the toolbar:
8 . 单击Return data工具栏中的：
9 . Now back in Excel hit properties in the prompt:
9 . 现在回到 Excel 中的提示中的属性：
10 . Now change the post code you entered into a ?in the definition tab:
10 . 现在更改您?在定义选项卡中输入的邮政编码：
11 . Hit OK and OK again and it will prompt for a value, enter a valid postcode and hit enter, this should now give you the value you want in cell A1.
11 . 再次点击确定和确定，它会提示输入一个值，输入一个有效的邮政编码并按回车键，现在应该会在单元格 A1 中为您提供所需的值。
12 . Now click in the cell A1 and go to Data> Propertiesthen Connection properties:
12 . 现在单击单元格 A1 并转到Data>Properties然后Connection properties：
13 . Now in the definition tab you have a Parametersbutton at the bottom, in here you can fill out as below:
13 . 现在在定义选项卡Parameters底部有一个按钮，在这里您可以填写如下：
Note, the target cell is the cell where you enter the postcode, tick the refresh box if you want this to re-run the query when the post code changes.
请注意，目标单元格是您输入邮政编码的单元格，如果您希望在邮政编码更改时重新运行查询，请勾选刷新框。
That should be it, let me know if it doesn't work.
应该是这样，如果它不起作用，请告诉我。
回答by simon at rcl
You can. In the VBA of the sheet, create a function like so:
你可以。在工作表的 VBA 中，创建一个函数，如下所示：
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
and in it's body
在它的身体里
- Check the Target is the cell you want to base your lookup on (G1)
- If it is, get the data from the DB and put it where it needs to go
- 检查目标是您要基于查找的单元格 (G1)
- 如果是，从数据库中获取数据并将其放在需要去的地方
Cheers -
干杯——
