Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel 2010 -- adding date parameters to the SLQ query via the Connections properties

Status
Not open for further replies.

olrac

MIS
Nov 23, 2010
16
CA
Hi,

I'm very new to VBA for EXCEL 2010.

I wrote a query with a where clause :

WHERE

TC.TimeCardDateTime = '2015-07-08'
and then a VBA piece:

Private Sub RefreshQuery()

With ActiveWorkbook.Connections("MSP_RECON").OLEDBConnection
.CommandText = "EXECUTE TC.TimeCardDateTime '" & Range("D2").Value & "'"
End With
ActiveWorkbook.Connections("MSP_RECON").Refresh


End Sub

Question 1 : The VBA codes gives error Subscript out or Range
Question 2 : How to I modify the VBA go look at a range of rates ( Where TC.TimeCardDateTime between '2015-01-01' and '2015-10-12'

Thank you very much!
 
Hi,

I have never used OLEDB. For the past 20 or so years, I have used ODBC regularly, since I have the entire SQL string to manipulate.

What kind of database are your querying via OLEDB?

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Hi,

Thanks for the quick response. I added a file to my original post (and again in this one). I database is a SQL database (on a MS 2013 server). In EXCEL I just choose Command Type as SQL and add my query.

From what I understand of the VBA..


StartDate = Sheets("Sheet1").Range("D2").Value 'Pass value from cell D2 to StartDate variable
EndDate = Sheets("Sheet1").Range("D3").Value 'Pass value from cell D3 to EndDate variable

'Pass the Parameters values to the Query used in the Data Connection
With ActiveWorkbook.Connections("MSP_RECON").OLEDBConnection
.CommandText = "EXEC TC.TimeCardDateTime between '" & StartDate & "' AND '" & EndDate & "'"
ActiveWorkbook.Connections("MSP_RECON").Refresh



it looks at the Query and replaces the String "TC.TimeCardDateTime between '2010-01-01' and '2010-01-' with = "EXEC TC.TimeCardDateTime between '" & StartDate & "' AND '" & EndDate & "'"
where start and end dates are cells D2 and D3

When I run the VBA is get a runtime error '9' .. Subscript out of range.

I'm sure I'll get more errors once I get past this one.

Thanks any help on this!
 
 http://files.engineering.com/getfile.aspx?folder=f83a68a8-973a-4241-817b-db4b8f2b1758&file=Excel_VBA.docx
First realize that your dates are really NUMBERS. faq68-5827

I don't have much experience with SQL Server databases, but I'm guessing that there's a function that converts a date STRING to a date NUMBER in SQL Server. Maybe ToDate()? Or maybe DateFromParts(), using Year, Month, Day.

At any rate a string is not what you gave in Excel and a string is not what you need in your criteria in your Where clause.


Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Try:
[tt].CommandText = "EXEC TC.TimeCardDateTime between CAST('" & StartDate & "' AS DATETIME) AND CAST('" & EndDate & "' AS DATETIME)"[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
There ya go: I knew we'd flush out a real expert!

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Hi , great I'll try that too. The problem with the "Script out of range" was that the ActiveWorkbook.Connections("MSP_RECON") was incorrectly named. I kept looking at problems with my datat type declarations Once this was changed the VBA worked perfectly.

Thanks to all for youe help!

Carlo
 
real expert" :) not really, I was just doing this

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top