2

I have data like this in 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

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

Community
  • 1
  • 1
Johan
  • 74,508
  • 24
  • 191
  • 319
  • 1
    You can query an Excel workbook using the Access database engine (Jet, ACE, whatever) which doesn't support `CASE` e.g. instead use `IIF(cp.period between CDATE('2012/01/01') and CDATE('2012/01/31'), sum(cpf.amount)*cp.percentage, NULL) as jan2012`. – onedaywhen May 26 '11 at 14:50
  • @onedaywhen, great that's what I'm talking 'bout. – Johan May 26 '11 at 14:54

3 Answers3

3

Here's the simplest of queries against Excel which may get you started:

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
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • And I would have to use named ranges to tell Excel which cells are in a table? – Johan May 26 '11 at 15:08
  • 1
    You can use worksheet names (e.g. `[Sheet1$]`) and Range addresses (e.g. whole columns `[Sheet1$A:B]` or specific row/column `[Sheet1$A2:B10]`). More info can be found in [this answer](http://stackoverflow.com/questions/4733155/any-references-manuals-on-sql-in-excel-with-microsoft-ole-db-provider-for-jet-4-0/4734707#4734707). – onedaywhen May 26 '11 at 18:58
1

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.

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$1")).QueryTable
    .CommandType = xlCmdTable
    .CommandText = Array("SELECT * FROM [Sheet1$A1:B30]")

End With
user1147015
  • 1,719
  • 1
  • 10
  • 2
-1

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).

However, you can write an equivalent function in VBA and run that in 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.

Vicky
  • 12,934
  • 4
  • 46
  • 54
  • thanks for replying, I can use Excel as a ODBC source, so why can't I select from Excel? – Johan May 26 '11 at 14:53
  • You can query an Excel workbook using the Access database engine (Jet, ACE, whatever) e.g. using the OLE DB provider for Jet or ACE with Excel in the `Extended Properties` (see my answer for a brief example). – onedaywhen May 26 '11 at 15:03