Solved

Hardcoding SQL query into an excel sheet to run as a macro

Posted on 2010-11-19
8
468 Views
Last Modified: 2012-05-10
Hi,

Is it possible to hard code this sql query in a master excel sheet to run as a macro.

Thanks


select d.Recordings,r.Extension, r.LastName, r.firstName  
from users r
join (
      select u.userid, count(*) as Recordings
      from users u join calllog c on u.userid=c.userid
      where starttime between dateadd(d, 0, datediff(d, 0, getdate())) and dateadd(d, 1, datediff(d, 0, getdate()))
      group by u.userid
) d on r.userId = d.userId

select count(*) as TotalRecordings
from calllog

where starttime between dateadd(d, 0, datediff(d, 0, getdate())) and dateadd(d, 1, datediff(d, 0, getdate()))
0
Comment
Question by:Sean
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
8 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34177339
Sheet1.QueryTables.Add(connectionStringGoesHere, Range("A1"), "select d.Recordings,r.Extension, r.LastName, r.firstName  
from users r
join (
      select u.userid, count(*) as Recordings
      from users u join calllog c on u.userid=c.userid
      where starttime between dateadd(d, 0, datediff(d, 0, getdate())) and dateadd(d, 1, datediff(d, 0, getdate()))
      group by u.userid
) d on r.userId = d.userId")
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34177346
Of course those line breaks will mess it up
Dim con as String
Dim sql As String

con = "Provider=blahblah..."
sql = "select d.Recordings,r.Extension, r.LastName, r.firstName  _
from users r _
join ( _
      select u.userid, count(*) as Recordings _
      from users u join calllog c on u.userid=c.userid _
      where starttime between dateadd(d, 0, datediff(d, 0, getdate())) and dateadd(d, 1, datediff(d, 0,  getdate())) _
      group by u.userid _
) d on r.userId = d.userId"

Sheet1.QueryTables.Add(con, Range("A1"), sql)
This puts the resulting table starting in A1

Open in new window

0
 
LVL 1

Author Comment

by:Sean
ID: 34177879
Tommy,
Please see attached.
Thanks
s
T1.jpg
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 34178076
Right, sorry. You either need to do
Set someVar = Sheet1.QueryTables.Add(con, Range("A1"), sql)
or just do
Sheet1.QueryTables.Add con, Range("A1"), sql

VBA is weird about the parentheses. If you use them, it wants you to do something with the return value.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34178094
Right, sorry. You either need to do
Set someVar = Sheet1.QueryTables.Add(con, Range("A1"), sql)
or just do
Sheet1.QueryTables.Add con, Range("A1"), sql

VBA is weird about the parentheses. If you use them, it wants you to do something with the return value.
0
 
LVL 1

Author Comment

by:Sean
ID: 34178780
Sorry Tommy,
Please see attached.
S
T1.jpg
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34181405
What's the error?
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34181408
If you don't have an actual Sheet1 object you can use Sheets("SheetName")
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question