Excel VBA - SQL查询和字段映射 [英] Excel VBA - SQL Query and Field Mappings
问题描述
我已经创建了一个excel电子表格，用于使用= GetGoogleDistance的加载项计算航运费率，以计算。由于您每天仅限于2500次查询，因此我选择将搜索结果保存到SQL数据库中。除了从SQL中提取数据并正确映射字段之外，我有一切按照计划进行工作。我之后说的是我的SQL数据库中有Distance1字段，如何将它映射到ActiveWorkbook.Sheets（Southwest Miles Shipper）。Range（D8）并填充该字段。我获得数据出来的唯一方法只是一种格式，我可以从那里获取数据，并做VBA工作，以获得适当的地方，但我想检查，看看有没有人知道如何做它。以下是链接当前文件：
I have created an excel spreadsheet used for calculating shipping rates based on miles using an add-in for =GetGoogleDistance to do the calculations. Since you are limited to just 2500 querys a day without paying for it, I elected to save the results from searches that have been done into my SQL database. I have everything working as planned so far except for pulling the data back from SQL and mapping the fields correctly. What I am after is say I have Distance1 field within my SQL database, how do I map it to ActiveWorkbook.Sheets("Southwest Miles Shipper").Range("D8") and it populate the field. The only way I have gotten the data to come out is just in a row format, I can take that data from there and do VBA work to get it to the proper places, but I wanted to check and see if anyone knows how to do it. Here is a link to the current file:
推荐答案
我想你所需要做的只是修改你的 getGoogleDistance 方法：
I think all you need to do is modify your getGoogleDistance method to:
- 检查SQL Server以查看该值是否已经存在
- 如果如果没有，请运行您的普通功能，然后将该值插入到SQL Server中。
至于如何做步骤＃1和＃3，如果你知道在哪里看，就不缺少这些例子。如果你搜索ADO，SQL Server，VBA我会看到更多的例子，你可以摇一摇。作为第1步的替代方案，您还可以使用内置于MS Excel中的MS Query，这样可以消除一些VBA，但并不排除您不必使用ADO，因为没有内置的内容知道）管理插入。
As far as how to do steps #1 and #3, there is no shortage of examples if you know where to look. If you search ADO, SQL Server, VBA I think will see more examples than you can shake a stick at. As an alternative to Step 1, you can also use MS Query, which is built into MS Excel, and that would eliminate some VBA, but it doesn't preclude you from having to use ADO since there is nothing built-in (that I know of) to manage the inserts.
这是一个非常裸的（未经测试）的例子，说明如何插入：
This is a really bare-bones (and untested) example of how you could do the insert:
Dim conn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim cs As String
Set conn = New ADODB.Connection
cs = "Provider=SQLOLEDB;Data Source=<whatever>\<whatever>;" & _
"Initial Catalog=<whatever>;" & _
"Integrated Security=SSPI;"
conn.ConnectionString = cs
cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "insert into [Distances] values (@FROM, @TO, @DIST)"
cmd.NamedParameters = True
cmd.Parameters.Append cmd.CreateParameter("@FROM", adVarChar, adParamInput, 256, fromValue)
cmd.Parameters.Append cmd.CreateParameter("@TO", adVarChar, adParamInput, 256, toValue)
cmd.Parameters.Append cmd.CreateParameter("@DIST", adNumeric, adParamInput, 256, distance)
cmd.Execute
这篇关于Excel VBA - SQL查询和字段映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
