Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 476
  • Last Modified:

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

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
Sean
Asked:
Sean
  • 6
  • 2
1 Solution
 
TommySzalapskiCommented:
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
 
TommySzalapskiCommented:
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
 
SeanAuthor Commented:
Tommy,
Please see attached.
Thanks
s
T1.jpg
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
TommySzalapskiCommented:
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
 
TommySzalapskiCommented:
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
 
SeanAuthor Commented:
Sorry Tommy,
Please see attached.
S
T1.jpg
0
 
TommySzalapskiCommented:
What's the error?
0
 
TommySzalapskiCommented:
If you don't have an actual Sheet1 object you can use Sheets("SheetName")
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now