How to have ms query reference the SQL on an excel worksheet

How can I have MS Query reference SQL on an excel worksheet?  I would like to have the parameter for my union query where I have to enter a range for each statement ( between '20100408' and '20100414').  If I enter my SQL in the MS Query SQL window it cannot be represented graphically.  Then when I try to add the paremeter through data connections...  I can only enter the parameter for my first select statement.

I am using Excel 2007 and am open to a VBA alternative, but I will need handholding on the execution(s)

Thanks a million
kgittingerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kgittingerAuthor Commented:
oops  Here is my sql
SELECT
dbo.Demo.EmployerID, 
dbo.Demo.Division, 
dbo.Demo.FName, 
dbo.Demo.LName, 
dbo.Demo.LName +', '+ dbo.Demo.LName As Participant_Name,
dbo.Trans_POS.PlanID, 
dbo.Trans_POS.AccountType, 
dbo.Trans_POS.EDate, 
dbo.Trans_POS.SettleDate, 
dbo.Trans_POS.ReImbDate, 
dbo.Trans_POS.TransAmt, 
dbo.Trans_POS.ReImbMeth, 
dbo.Trans_POS.SettleSqNum as ChkTrcNum, 
dbo.Trans_POS.ChkReIssue
FROM dbo.Demo RIGHT OUTER JOIN dbo.Trans_POS ON dbo.Demo.EmployeeID = dbo.Trans_POS.EmployeeID 
AND dbo.Demo.EmployerID = dbo.Trans_POS.EmployerID
WHERE (dbo.Trans_POS.SettleDate BETWEEN '20100301' AND '20100331')


UNION ALL
SELECT dbo.Demo.EmployerID, 
dbo.Demo.Division, 
dbo.Demo.FName, 
dbo.Demo.LName, 
dbo.Demo.LName +', '+ dbo.Demo.LName As Participant_Name,
dbo.Trans_Man.PlanID, 
dbo.Trans_Man.AccountType,
dbo.Trans_Man.EDate, 
dbo.Trans_Man.SettleDate, 
dbo.Trans_Man.ReImbDate, 
dbo.Trans_Man.TransAmt, 
dbo.Trans_Man.ReImbMeth, 
dbo.Trans_Man.ChkTrcNum, 
dbo.Trans_Man.ChkReIssue
FROM dbo.Demo RIGHT OUTER JOIN dbo.Trans_Man ON dbo.Demo.EmployeeID = dbo.Trans_Man.EmployeeID 
AND dbo.Demo.EmployerID = dbo.Trans_Man.EmployerID
WHERE(dbo.Trans_Man.ReImbDate BETWEEN '20100301' AND '20100331')

Open in new window

0
RichardSchollarCommented:
Hi

On a fresh worksheet, can you record a macro of you creating a new data table from MSQuery using the SQL you showed above?  Please post the resulting VBA code into a reply - this will provide all the connection string info for your database (presumed to be SQL Server) and allow us to craft a solution for you.  You do need to use VBA for this.

Richard
0
cyberkiwiCommented:
Hi,

These links should help you, straight from the horses mouth, so to speak

http://office.microsoft.com/en-us/excel/HP051995481033.aspx

expand  "Use data from a cell as a parameter value"
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Rory ArchibaldCommented:
Try this function - you just need to pass the two ranges to it and it will return the completed SQL string:



Function BuildSQL(rngParm1 As Range, rngParm2 As Range) As String
Dim strSQL As String
strSQL = "SELECT dbo.Demo.EmployerID, dbo.Demo.Division, dbo.Demo.FName, dbo.Demo.LName, " & _
        "dbo.Demo.LName +', '+ dbo.Demo.LName As Participant_Name, dbo.Trans_POS.PlanID, " & _
        "dbo.Trans_POS.AccountType, dbo.Trans_POS.EDate, dbo.Trans_POS.SettleDate, dbo.Trans_POS.ReImbDate, " & _
        "dbo.Trans_POS.TransAmt, dbo.Trans_POS.ReImbMeth, dbo.Trans_POS.SettleSqNum as ChkTrcNum, dbo.Trans_POS.ChkReIssue " & _
        "FROM dbo.Demo RIGHT OUTER JOIN dbo.Trans_POS ON dbo.Demo.EmployeeID = dbo.Trans_POS.EmployeeID " & _
        "AND dbo.Demo.EmployerID = dbo.Trans_POS.EmployerID " & _
        "WHERE (dbo.Trans_POS.SettleDate BETWEEN '" & rngParm1.Text & "' AND '" & rngParm2.Text & "') "
strSQL = strSQL & "Union ALL SELECT dbo.Demo.EmployerID, dbo.Demo.Division, " & _
        "dbo.Demo.FName, dbo.Demo.LName, dbo.Demo.LName +', '+ dbo.Demo.LName As Participant_Name, dbo.Trans_Man.PlanID, " & _
        "dbo.Trans_Man.AccountType, dbo.Trans_Man.EDate, dbo.Trans_Man.SettleDate, dbo.Trans_Man.ReImbDate, dbo.Trans_Man.TransAmt, " & _
        "dbo.Trans_Man.ReImbMeth, dbo.Trans_Man.ChkTrcNum, dbo.Trans_Man.ChkReIssue FROM dbo.Demo " & _
        "RIGHT OUTER JOIN dbo.Trans_Man ON dbo.Demo.EmployeeID = dbo.Trans_Man.EmployeeID AND dbo.Demo.EmployerID = dbo.Trans_Man.EmployerID " & _
        "WHERE(dbo.Trans_Man.ReImbDate BETWEEN '" & rngParm1.Text & "' AND '" & rngParm2.Text & "')"
BuildSQL = strSQL

End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kgittingerAuthor Commented:
Thank you for the comments and I am sorry to be so dense...
1) I do not know how to create a macro to create a table...  I do not have administrator rites (The server is on an outside site) on the server, so I cannot create a table...
2) since my data is not represented graphically, I cannot edit the paremeter as customary (this is the root of my problem)
it looks like rorya has a good jump on things for me.  I just need some more help...  Do I add this VBA in a Module?  Then how do I pass the range to the function?  I can see rngParm1 (as my 1st Paramater) and rngParm2 (as my 2nd Parameter) but I am confused as to why they are both called Range - Although I am sure you are right, I just don't understand

0
Rory ArchibaldCommented:
I am assuming that you have a querytable already set up (per your other posts) that points at the database. You would need to copy that function into a new module in your workbook, then add a macro that uses something like the code below. Without knowing anything about your workbook it's impossible to provide more specific code! :)
Rory


PS The code assumes you are using XL2007.





Sub UpdateQT()
   Dim qt as Querytable
   Dim wks as worksheet
   set wks = sheets("Sheet name")
   Set QT = wks.Listobjects(1).Querytable
   QT.Commandtext = BuildSQL(wks.range("A1"), wks.range("A2"))
   QT.Refresh
End Sub

Open in new window

0
kgittingerAuthor Commented:
Thanks I will try it, I must drive to the office.  I have written my SQL in studio and am tring to get it into excel...

More later
0
stevericeCommented:
0
kgittingerAuthor Commented:
I am unable to create a view.  I was able to swap around my statements and create the paremters as you instructed in my previous question, but it does not work for htis example (different SQL server/Site)??
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.