Solved

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

Posted on 2010-11-19
8
449 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

777 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