vba 我可以使用 SQL 语句从工作表 A + 工作表 B 中提取数据并仅使用 Excel 转储到工作表 C 中吗
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6140200/
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
Can I use an SQL statement to extract data from sheet A + sheet B and dump in in sheet C using only Excel
提问by Johan
I have data like this in Excel
我在 Excel 中有这样的数据
table Cost_per_period
---------------------
ProjectId
FaseID
Period
Percentage
table cost_per_partner_per_fase
-------------------------------
ProjectID
FaseID
PartnerID
Amount
table partners
--------------
PartnerID
name
Here's the output I want.
这是我想要的输出。
2012 2013 2014 2015
Project fase jan feb ... dec Q1 Q2 Q3 Q4 wholeY wholeY
------------------------------------------------------------------------
A310 1 100k 20k 10k 100k - 10k 10k 1000k 2000k
A310 2 110k - 20k 99k - 40k 50k 5000k 3000k
......
To combine this data, I'm thinking of doing a SQL statement like
为了结合这些数据，我正在考虑做一个像 SQL 语句
SELECT cp.projectID, cp.faseID
, case when cp.period between '2012/01/01' and '2012/01/31'
THEN sum(cpf.amount)*cp.percentage as jan2012 end
, case when ..... as feb2012 end
, case .......
FROM cost_per_period as cp
INNER JOIN cost_per_partner_per_fase as cpf
on (cp.postjectid = cpf.projectid) and (cp.faseid = cpf.faseid)
GROUP BY cp.Projectid, cp.faseid
ORDER BY cp.ProjectID, cp.FaseID
Can I do this using only Excel?, I'm using excel 2007
我可以只使用 Excel 吗？，我使用的是 excel 2007
回答by onedaywhen
Here's the simplest of queries against Excel which may get you started:
以下是针对 Excel 的最简单查询，可以帮助您入门：
Sub test()
Dim rs As Object
Set rs = CreateObject("ADOR.Recordset")
rs.Open _
"SELECT CompanyName FROM Customers;", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\db.xls;" & _
"Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
Sheet3.Range("A1").CopyFromRecordset rs
End Sub
回答by user1147015
This is taken from a macro recorded in Excel 2007 that works for me. You can modify the data source and the SQL query to suit your need.
这是从 Excel 2007 中记录的一个对我有用的宏中获取的。您可以修改数据源和 SQL 查询以满足您的需要。
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;" _
, "Data Source=C:\RM.xlsm;" _
, "Jet OLEDB:Engine Type=37"), Destination:=Range("$A")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("SELECT * FROM [Sheet1$A1:B30]")
End With
回答by Vicky
If the data is already in Excel, you can't run an SQL query against it. You'd only be able to run that kind of code against an SQL database (eg if you had the data in Access to start with).
如果数据已在 Excel 中，则无法对其运行 SQL 查询。您只能对 SQL 数据库运行那种代码（例如，如果您在 Access 中有数据的话）。
However, you can write an equivalent function in VBA and run that in Excel.
但是，您可以在 VBA 中编写等效函数并在 Excel 中运行该函数。
I also found this plug in that would let you execute SQL against Excel data: http://www.querycell.com/but it's not free and I have no idea how good it is.
我还发现这个插件可以让你对 Excel 数据执行 SQL：http: //www.querycell.com/但它不是免费的，我不知道它有多好。
