How do I avoid a 1004 Runtime Error when trying to run this script?

[helpneeded2]

I have created a query similar to that of one described in this video:

The summary of this is:

• I have a field that contains a list of numbers that will be passed to a SQL query: (1), (23), (3003)
• I have a macro that runs a query I have saved as Power BI query.
• The result is output onto the DATA tab of my worksheet.
• The macro is executed by pressing a button.

Everything works correctly, however the VBA code that should be refreshing the result on my table on my DATA sheet, instead returns a "Run-time error '1004': Application-defined or object-defined error".

ActiveWorkbook.Queries("GetDataQuery").Refresh

If I remove this line of code above, then I can relaunch the query with the Macro button, and then I right click on the returned table on my DATA sheet and select "refresh" which will open up a "Native Database Query" window, in which I select "Run" , and it will refresh with the new data.

In the video, the host just clicks the Macro button, which will then bring up the "Native Database Query" window, which will then allow the user to select "Run" and complete the operation.

Can anyone think of a reason why my process will not work as intended with the VBA Refresh code? (Or more importantly, can anyone advise what I can do to make it work correctly.)

 

[Skyybot]

Need to see your code.

 

[helpneeded2]

Need to see your code.

If you think it would help, sure I can paste the code here, however the code works fine -- it is only the refresh line that generates the error, which is why I didn't paste the entire code.

Sub AddData()

Dim DataString As String

DormantPatrons = Sheets("DATA").Range("C2").Value

ActiveWorkbook.Queries("GetDataQuery").Formula = "let Source = Sql.Database(""MySQLServer"", ""MyDatabase"", [Query=""declare @searchDays int = 45#(lf)#(lf)drop table if exists #dormantpatrons#(lf)Create Table #dormantpatrons (Masterpatronid int)#(lf)insert into #dormantpatrons values " & DormantPatrons & _
        "#(lf)#(lf);#(lf)With CTE_Name as(#(lf)select distinct t1.masterpatronid,concat(Upper(Surname), ', ',GivenName) as PatronName , S_Date as 'Date'  from patron_RV t1#(lf)inner join (Select distinct masterpatronid, Max(S_Date) as 'Date' from patron_RV group by MasterPatronId) t2#(lf)on t1.MasterPatronId =t2.MasterPatronId a" & _
        "nd t1.S_Date=t2.Date#(lf)group by t1.MasterPatronId,surname,GivenName,t1.S_Date)#(lf)#(lf)select distinct p.MasterPatronId#(lf),PatronName#(lf),max(r.C_Date) as Report_CreateDate#(lf),(case when MAX(r.c_date) between DATEADD(day,(@searchDays * -1),GETDATE()) and GETDATE() then 'Yes' else 'No' end) as 'Active?'#(lf),GETDATE() as runDate#(lf)from Report_RV as r#(lf)in" & _
        "ner join Patron_RV as p on r.PatronId = p.PatronId#(lf)left join CTE_Name as t1 on p.MasterPatronId = t1.MasterPatronId#(lf)where p.MasterPatronId in (Select * from #dormantpatrons)#(lf)group by p.MasterPatronId,t1.PatronName#(lf)order by p.MasterPatronId""])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source" & _
        ""

ActiveWorkbook.Queries("GetDataQuery").Refresh

ActiveWorkbook.Save

End Sub

 

[helpneeded2]

I have spent a few hours searching Google to find an answer for this, however this seems to be a frequently experienced issue that doesn't have many answers. One solution I read, advised to replace the refresh line with:

ActiveWorkbook.RefreshAll

When I do this, then the following happens:
- After clicking on the Macro button to launch the code, a Microsoft Excel pop-up appears displaying: This will cancel a pending data resfresh. Continue?
- I click OK and the code runs, but the refresh does not occur.