VBA inputbox to excel sql queries

[bnpaco]

Hello,

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.

SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '20070731 10:10:51.450'
SET @EndDate = '20070731 10:11:51.450'
SET ROWCOUNT 100
SET NOCOUNT OFF
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
SET ROWCOUNT 0

This is a generic query i created to use as 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

I'm open to suggestions if i'm doing this the retarded/impossible/or hard way.

Thanks,

Marshall

 

[bnpaco]

Anyone have any ideas?

 

[QuietRiot]

im a little confused by what exactly your looking for..

something that I built for my work would open up a text file and then extract cusips from it. It then populated the sql script in a text box that had a copy to clipboard button. the user just copied and pasted the sql query that it made.

are you looking for something that will ask for input and then create a script so the user can run?

 

[bnpaco]

The overall goal is the following:

1) Ask the user for the Month and Year they wish the report to be run. --I've done that
2) Store the information provided by the user in a variable. -- I've done that = querydate
3) Take the variable and place it in sql definitions. -- This is the spot i cannot figure out.

I'd like to keep the time and the day of the month static, the operator will never change that...however the month and year will always change. The reason i'm using an input box is if for some reason they lose a previous month's report and would like to recreate another one, as well as run this report every month for the previous month. I.E. Run it on july 2nd for the month of June's data.

Because of the fact that the operator never runs this report on a scheduled day, i cant just use something like (mm,-1) so its got to be a defined date. Thats where i figured it would be easy for them to figure out if an input box prompted them for that information.

SET @StartDate = '20070731 10:10:51.450' 
SET @EndDate = '20070731 10:11:51.450'

my goal is to remove the '200707' and replace it with some sort of variable. I.E. @StartDate = 'querytable & 31 10:10:51.450' So that when the report is ran querytable gets replaced with the information that the user was prompted to input.

 

[QuietRiot]

below is some code I whipped up real quick.. its an input box but what it does is sets the default as current day in the proper format this way if the person needs to input current date its already done for them and in the format of 20070731.

still don't know if this is what you want but to achieve that it would just be adding two strings startdate = " 10:10:51.450" - notice space before it and then querytable = "20070731" it would just be a simple querytable + startdate

Sub Macro1()
Dim querytable As String, dat3 As String, startdate As String



dat3 = Date 'this part of the code gets todays date in 6/6/6666 format
startdate = " 10:10:51.450"



'this part of the code seperates day month and year from date
Dayy = Mid(dat3, InStr(1, dat3, "/") + 1, InStr(2, dat3, "/"))
If InStr(1, dat3, "/") = 2 Then dat3 = "0" + dat3
Monthh = Left(dat3, InStr(1, dat3, "/") - 1)
Yearr = Right(dat3, 4)


Dayy = Replace(Dayy, "/", "")
If Len(Dayy) = 1 Then Dayy = "0" + Dayy

'this part puts it in proper format
querytable = Yearr + Monthh + Dayy



'this is the inputbox that already has current date as default

    querytable = InputBox(Prompt:="Enter Date Please: ", _
          Title:="ENTER DATE", Default:=querytable)

'this part shows how to add date to startdate string
startdate = querytable + startdate

MsgBox startdate

End Sub

if you need more help let me know. If you would like a text box that will populate with the entire sql script or something

 

[bnpaco]

Yeah, i've got that stuff, my code is similar to yours already...

Here's what i've got. I've got an excel workbook, its an MOR, monthly operational report. Basically a ton of sql database connections populating my cells. I know how to get the data from the last month. @startdate = (mm,-1, ect ect). That wont work because they may run it on the first or the secnd or the 5th of the next month and the line of code i just listed is last month, not the calendar month. So what i need to do is come up with a way that no matter what day they run the report they will still end up with the same data by being prompted for what date the report is being ran for.

So i wrote VB code using inputbox() and then broke it down till i got the integer i needed. What i need to do is place that integer in each of my sql database connection queries that are planted all over the excel sheet.

So using my example earlier, "querytable". How do i get the results from my visual basic code for querytable so that way my SQL @StartDate = and @EndDate equals:

SET NOCOUNT ON 
DECLARE @StartDate DateTime 
DECLARE @EndDate DateTime 
SET @StartDate = '(RESULTSFROMVBCODE)31 10:10:51.450' 
SET @EndDate = '{RESULTSFROMVBCODE)31 10:11:51.450' 
SET ROWCOUNT 100 
SET NOCOUNT OFF 
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 
SET ROWCOUNT 0

 

[bnpaco]

anyone have an idea as to how i can do this?

 

[Norie]

bnpaco

Where's the Excel/VBA connection?

What you've posted is SQL.

You say you've got something like Quiet Riot, so why not post that.

 

[bnpaco]

Because the VBA part works. Its taking my vba in the excel workbook results and somehow making those results my @startdate and @enddate in the query definitions embedded in the excel workbook.

 

[Norie]

Sorry you've lost me there.

I thought you wanted help with incorporating the dates into the SQL which is presumably being run from VBA.

The only way I can see us helping with that would be if we saw the current VBA.