I need to fetch data from My sql in excel of current date

[Prashant24]

hi there,

I am having a problem while fetching the data of the current date from my sql database but when i specify a date in the format "2013-08-20" then it works perfectly and returns the data to excel sheet, can anybody please tell me how to do this.



Regards:-
Prashant

[Kyle123]

Without knowing how you are actually fetching the data, I'm afraid no-one will be able to help you

[Prashant24]

hi,

here i am sharing my cod with u, please check

CommandText = Array( _
"SELECT **********.Entrydate, *******.ORDERNO, *****.DISPOSITION_NAME, ****.CC_NAME" & Chr(13) & "" & Chr(10) & "FROM ***** customiseview" & Chr(13) & "" & Chr(10) & "WHERE (****.Entrydate='2013-08-20') AND" _
, _
" (****.DISPOSITION_NAME='Order') AND (****.CC_NAME='Indore')" _
)

here at "WHERE (****.Entrydate='2013-08-20') i need a function which can take current date as entry date.

Prashant

[Kyle123]

Please Login or Register  to view this content.

[Prashant24]

Well i tried to use this code but still it not working

[Kyle123]

What "isn't working"? You're going to need to be a bit more descriptive than that

[Prashant24]

All right when i entered the above code and refreshed the query then it rerurned nothing

[Kyle123]

Both my code and your hardcoded text produce identical outcomes, I suspect therefore that your problem lies elsewhere, consider:

Please Login or Register  to view this content.

[Prashant24]

SELECT customiseview.Entrydate, customiseview.email, customiseview.DISPOSITION_NAME, customiseview.AGENTID
FROM GTM.dbo.customiseview customiseview
WHERE (customiseview.Entrydate='2013-08-20') AND (customiseview.DISPOSITION_NAME='ORDER')

here is the orignal code and i just replaced
SELECT customiseview.Entrydate, customiseview.email, customiseview.DISPOSITION_NAME, customiseview.AGENTID
FROM GTM.dbo.customiseview customiseview
WHERE (customiseview.Entrydate='" & Format(Now(), "yyyy-mm-dd") AND (customiseview.DISPOSITION_NAME='ORDER')

Well i am not able to find the exact problem as there is no error poping.

[Kyle123]

That isn't the code I gave you. Where have you put that? It isn't VBA code it's SQL and you're trying to put VBA in it

It should be here:

Please Login or Register  to view this content.

[Prashant24]

Actually i just made two different macro's but i need the same criteria in both
the code which i just posted was from sql which i got from the edit query option
so please give me the code for this statement
SELECT customiseview.Entrydate, customiseview.email, customiseview.DISPOSITION_NAME, customiseview.AGENTID
FROM GTM.dbo.customiseview customiseview
WHERE (customiseview.Entrydate='" & Format(Now(), "yyyy-mm-dd") AND (customiseview.DISPOSITION_NAME='ORDER')

I just want the current date at the place of entry date so that i don't have to edit the query on daily basis

[Kyle123]

The code does that, you need to put it in the macro - post the code for your full macro and remember to use code tags

[Prashant24]

i just refreshed the query in the macro so it uses only these codes

Range("B2").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

but when i edit the query manualy
then i get the previously posted statements in the SQL query table

[Prashant24]

Here's a newly recorded macro please check it




Sub Chc_c()
'
' Chc_c Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DRIVER=SQL Server;SERVER=*.*.*.*;UID=***;;APP=2007 Microsoft Office system;WSID=E6" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT customiseview.Entrydate, customiseview.DISPOSITION_NAME" & Chr(13) & "" & Chr(10) & "FROM GTM.dbo.customiseview customiseview" & Chr(13) & "" & Chr(10) & "WHERE (customiseview.Entrydate='" & Format(Now(), "yyyy-mm-dd") AND (customiseview.DISPOSITION_NAME='Order')" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_Prashant"
.Refresh BackgroundQuery:=False
End With
End Sub
when it put the cod given by you it gives syntax error i m a beginner to debug this

[Kyle123]

Please Login or Register  to view this content.

[Prashant24]

Thanks bro it worked for me

Thanks a lot!