Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
VBA inputbox to excel sql queries. (Read 8994 times)
YaBB Newbies


Posts: 1
VBA inputbox to excel sql queries.
31.07.07 at 17:10:00
I'm stuck at a roadblock with a report i'm writing.  The report is a "template" that will be ran once a month every month and will look back at the previous month's data.  What i've invisioned is writing a vb script to load on workbook open asking various questions via "inputbox()" method.  
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '20070731 10:10:51.450'
SET @EndDate = '20070731 10:11:51.450'
SELECT EventTagName = EventHistory.TagName, Value
 FROM EventHistory
INNER JOIN AnalogSnapshot ON EventHistory.EventLogKey = AnalogSnapshot.EventLogKey
INNER JOIN SnapshotTag ON SnapshotTag.SnapshotTagKey = AnalogSnapshot.SnapshotTagKey
 WHERE SnapshotTag.TagName IN ('SysSpaceMain')
 AND DateTime >= @StartDate
 AND DateTime <= @EndDate
 AND SnapshotTag.TagType = 1
 AND Quality = 0

This is a generic query i created to get an example of how i can use inputbox() and datepart() to create the startdate and enddate in my sql query.  The day of the month must be static, however the year and month will not be.  
dim querydate as Integer 

Where querydate is my result of datepart() and inputbox.
how do i code querydate into each of my sql query definitions so when the workbook loads, it gets the information after the user has given the date to the inputbox()?  The end result would be taking the 20070731 and breaking it to "querydate + 31".
I hope i've explained this clearly enough, running on nothing but coffee atm  undecided
I'm open to suggestions if i'm doing this the retarded/impossible/or hard way.
Back to top
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Re: VBA inputbox to excel sql queries.
Reply #1 - 06.08.07 at 07:41:47
You can use the SQL*XL accept command in the SQL window to display a prompt and to receive input from the user:
ACCEPT variable_name PROMPT text;  
For example:
ACCEPT my_month PROMPT 'Enter a month:';
select * from mytable where month = &my_month;
With the above the challenge remains how to build the month and year information into a query. You can get the year and the month using:
ACCEPT ym PROMPT 'Enter the year and month (e.g. 200707)';
Then you need to concatenate that to a static part of your date using string concatenation in SQL Server:
select * from mytable where date = &ym + 31;
It is a challenge now to make SQL Server interpret &ym + 31 or 200707 + 31 as a date. Perhaps this will do it:
convert (datetime,'20070731',120)
This will make the SQL string:
ACCEPT ym PROMPT 'Enter the year and month (e.g. 200707)';
select * from mytable where date = convert (datetime, '&ym' + '31' ,120)
I have not tried this out in SQL Server yet. Please try the above for me and let us know whether it worked or not. If you cannot get it going try first to substitute the &ym for a hard coded value like 200707. If that starts working ok put in the Accept and the variable.
See also:
Back to top
« Last Edit: 06.08.07 at 21:02:35 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1