Forum Discussion

dlc04's avatar
dlc04
Copper Contributor
Apr 21, 2023

Add inputbox value into a where clause in odbc.query

Hello,   I have an Excel with macros connected to Snowflake via ODBC and I'm trying to create an InputBox to select a date and update the where clause in the odbc.query.   What I'm trying right n...
BI & Data Analysis
Excel
Macros and VBA
  • Lorenzo's avatar
    Lorenzo
    Apr 23, 2023

    Hi dlc04 

     

    Little add. to NikolinoDE 

     

    Assuming the value in B2 is like dd/mm/yyyy your selectedDate step returns a DateTime value (i.e. 01/01/2023 00:00:00). If SnowFlake expects a Date value (i.e. 01/01/2023) only you'll have to convert the DateTime value to a Date value before converting it to a Text value. Can be done as follow:

     

     

      selectedDate = Text.From(
    DateTime.Date(
      Table.FirstValue(Excel.CurrentWorkbook(){[Name="mydate"]}[Content])
    )
  ),

     

dlc04's avatar
dlc04
Copper Contributor

Found a way to do it. I was using the refreshAll along with the AsyncQueriesDone

ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone

which I'm guessing it was running the query while it was trying to update such query at the same time.

So I used 

ThisWorkbook.Connections("Query - Query1").Refresh

And in the query properties I unchecked the option "Enable background refresh".

Resources