控制数据库通过VBA格式化Excel [英] Control Database form Excel through VBA

问题描述

我正在尝试创建一个excel addin，它具有一组功能来从数据库中获取值（我使用MS SQL Server）。所以我的查询将只返回一个记录集。我在vba代码中使用下面的东西。

之后，您可以从上表中获取所有数据查找函数如下：

  = INDEX（SQLdata，MATCH（1，（SQLdata [Table] =Users）* SQLData [UserID] = 25），0），4）
  

_{表SQLdata使它更容易查看公式并了解它。此外，像这样，您可以轻松地扫描您的Excel文件，以查看此表格。}

另一种方法可能是使表格更简洁：

请注意，这次我正在混合 Strings with Numbers and also Strings with Date s （这是非常差的设计，对于这里的一些人甚至不可能想到）。此外，列标题现在描述较少。但是，这样做也可以：

  = INDEX（SQLrev，MATCH（1，（SQLrev [Table] =Users）* （SQLrev [Dimension1] = 25），0），5）
  

_{请注意，这次调用表SQLrev。}

这两种解决方案都允许您从表中汇总数据。所以，如果你想要（例如）苹果在2017年的平均价格，那么你可以使用以下公式来总结今年的报价，并将它们除以3：

  = SUM（IF（ 股市= SQLrev [表]; 1; 0）* IF（ AAPL= SQLrev [DIMENSION1]; 1; 0）* SQLDATA [价格]）/ 3 
  

这种方法最显着的优点是您只需更新一个表格整个Excel文件意味着从服务器只有一个一个 SQL拉。

最显着的缺点（除了SQL select which您可能需要知道需要驻留在此表中的所有数据。如果数据没有被拉入这个表格，那么以上公式都不能检索这些值。

虽然这种方法肯定有其缺点，这更容易所有上述公式都是数组公式，必须按 Ctrl + Shift + 输入。有关数组公式的更多信息，请阅读以下内容： https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7D94A64E-3FF3-4686-9372-ECFD5CAA57C7

I am trying to create an excel addin which has set of Functions to pull value from the database (I use MS SQL Server). So my query will return only one record set. I use something like below in my vba code.

Using Excel VBA to run SQL query

But the problem with this is if I have my custom function in 100 cells, the macro makes connection to the DB everytime and retrive data from the DB.

Is there a way where I can make one connection and use that connection to write as many queries as I want?

解决方案

Disclaimer: while this is not a direct solution to the problem described in the post I would like to add this approach as a much faster and easier solution to the problem described.

Step 1: create a (possibly hidden) sheet where you pull all the SQL data that you need in this Excel file. Pull the data into one table with all the necessary columns / dimensions to get the data afterwards from this table.

Here is what I mean by that. Let's assume that you need in this Excel file some data from the table Users on the SQL server as well as some information from a StockMarket table on the server. From the table Users you want the UserID, the first name, last name, and the job title. From the table StockMarket you will need the Stockmarket ID and the price for this particular share. As these prices are by date you also need the the quote date for the price.

Now, since you want all in one table you must think of a smart way to combine all this data into one table. One approach could be the following:

Afterwards you can get all the data from the above table with lookup functions like these:

=INDEX(SQLdata,MATCH(1,(SQLdata[Table]="Users")*(SQLdata[UserID]=25),0),4)

_{Note, that I named the table SQLdata to make it easier when looking at the formula and understanding it. Also, like this you can easily scan your Excel file for any reference to this table.}

Another approach could be the following to make the table more concise:

Note, that this time I am mixing Strings with Numbers and also Strings with Dates (which is very poor design and for some people here even impossible to think of). Also, the column headers are now less descriptive. Yet, this works too:

=INDEX(SQLrev,MATCH(1,(SQLrev[Table]="Users")*(SQLrev[Dimension1]=25),0),5)

_{Note, that I called the table this time SQLrev.}

Both solutions allow you also to aggregate data from the table. So, if you want (for example) the average price for Apple in 2017 then you can use the following formula to sum up the the quotes for this year and divide them by 3:

=SUM(IF("StockMarket"=SQLrev[Table];1;0)*IF("AAPL"=SQLrev[Dimension1];1;0)*SQLdata[Price])/3

The most notable advantage for this approach is that you merely have to update one table in the entire Excel file which means that there is only one SQL pull from the server.

The most notable disadvantage (apart from the SQL select which might get pretty complicated to write) is that you need to know of all the data that needs to reside in this table. If the data is not pulled into this table then none of the above formulas will be able to retrieve these values.

While this approach certainly has its downsides this is much easier to implement than the Excel AddIn you are aiming for.

_{All above formulas are array formulas and must be entered pressing Ctrl + Shift + Enter. For more information on array formulas please read the following: https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7D94A64E-3FF3-4686-9372-ECFD5CAA57C7}

这篇关于控制数据库通过VBA格式化Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！