[RESOLVED] Excel Range lossing query to load data
I have the below code, but whenever I try to run the report by changing the start date and end date the Range("EmailReason").Select does not find or losses the query, ie. stored procedure. Also does keeps the old query when the data range changes from a higher range value to lower range value.
I have set the external date range properties to "Insert Cells for New Date,Delete Unused Cells". I have got other sheets set the same but they working fine.
Please need help.
Code:
Sheets("Mazda ").Activate
Range("A9").Select
With Selection.QueryTable
.CommandText = Array("exec rpt_mazdaEmailstats @startdate='" & startdate & "', @enddate='" & enddate & "'")
.Refresh BackgroundQuery:=False
End With
Range("EmailReason").Select
With Selection.QueryTable
.CommandText = Array("exec rpt_mazdaEmailReason @startdate='" & startdate & "', @enddate='" & enddate & "'")
.Refresh BackgroundQuery:=False
End With
Re: Excel Range lossing query to load data
Don't you need to set the .Connection each time ??
In the past when i have done similar stuff (although not with stored procedure)
i have done something like the following, the only thing you seem to be doing different not including the connection object each time.
All i did was record a macro, and then go to Edit Query, the Macro normally will record you connection aswell as SQL.
Code:
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=Arista;UID=User;;DATABASE=db;HOST=host;SRVR=online;SERV=1526;PRO=olsoctcp;CLOC=en_US.CP1252"),Array( _";DLOC=en_US.CP1252;VMB=0;CURB=0;OPT=;SCUR=0;ICUR=0;OAC=1;OPTOFC=0;RKC=0;ODTYP=0;DDFP=0;DNL=0;RCWC=0"))
.CommandText = Array( "SELECT stuff from database" )
.Refresh BackgroundQuery:=False
End With
