1

I want to import a live data server from Microsoft SQL Server with time filter but in Excel and a refresh button.

To be more exact, I want to create a user friendly program in Excel where you can enter 2 parameter values of time (StartDate and EndDate).

I saw some video on YouTube with what I want, but every time I finish, I get this error

Error 1004 – Application-Defined or Object-Defined Error

I see this site: https://www.automateexcel.com/vba/error-1004-application-defined-object-defined/

But I don t know what I need to change.

This is SQL command for time:

select top 10000 * 
from [IAG_Machine1].[dbo].[PD_Report]
where tist between '1/31/2023 4:02:06 PM' and '2/20/2023 4:02:06 PM'
AND [PrNr] = '2'
order by tist

In Excel, I have this code:

Sub RunQuery()
ActiveWorkbook.Queries("Query1").Formula = "let" & Chr(13) & "" & Chr(10) & "    Source = Sql.Database(""***IP****"", ""IAG_Machine1"", [Query=""#(lf)select top 10000 * #(lf)from [IAG_Machine1].[dbo].[PD_Report]#(lf)#(lf)where tist between '" & ActiveSheet.Range("$E$2") & "' and '" & ActiveSheet.Range("$E$3") & "'#(lf)AND [PrNr] = '" & ActiveSheet.Range("$E$1") & "'#(lf)order by tist ""])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
ActiveWorkbook.Queries("Query1").Refresh

End Sub

Andrei22
  • 11
  • 2
  • [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) – Thom A Jul 07 '23 at 12:14
  • SQ don't like this date format. try to use between TRY_CAST('1/31/2023 4:02:06 PM' AS DATETIME) and TRY_CAST('2/20/2023 4:02:06 PM' AS DATETIME) – Power Mouse Jul 07 '23 at 13:56
  • @Power-Mouse, Thanks for the answer but it still doesn't work. – Andrei22 Jul 13 '23 at 08:40
  • did you try to run query in sql management studio? does it return data you expecting? – Power Mouse Jul 13 '23 at 12:34
  • how you linked to sql? try new query, from database, sql, ... and where condition would be filter. let Source = Sql.Database("(local)", "mishka"), dbo_Person = Source{[Schema="dbo",Item="Person"]}[Data], #"Filtered Rows" = Table.SelectRows(dbo_Person, each [Birthday] >= #date(2000, 9, 21) and [Birthday] <= #date(2015, 11, 11)) in #"Filtered Rows" – Power Mouse Jul 13 '23 at 12:46

0 Answers0