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])
    )
  ),

     

Lorenzo's avatar
Lorenzo
Silver Contributor
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
    Apr 26, 2023

    Thank you both @NikolinoDE and Lorenzo 

     

    Once I changed the format to text, in the Power Query Editor I'm getting the following:

     

    DataSource.Error: ODBC: ERROR [22007] Date '4/26/2023 12:00:00 AM' is not recognized
    Details:
    DataSourceKind=Odbc
    DataSourcePath=dsn=Snowflake_Source
    OdbcErrors=[Table]

     

    So I tried the DateTime option since in Snowflake that is a date data type and I changed the B3 cell format to Date (MM/DD/YYYY), and it worked when I refreshed it in the Editor, but when I closed the Excel file and reopen it again, the inputbox popped out, inserted the date, and the file freezes completely and do not run.

     

    Could it be something in the vb code about the refresh that is causing some trouble?

     

    Here is what I have in the vb code for the inputbox and the refresh.

     

    Private Sub Workbook_Open()
'PURPOSE: Refresh All function when file is opened

'InputBox
Range("A2:AL500").ClearContents
inputvalue = InputBox("Enter date(MM/DD/YYYY):")
Range("B3").Value = inputvalue

'Refresh
ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
MsgBox "Data has been refreshed!"

End Sub

     

    Thanks again!

    • dlc04's avatar
      dlc04
      Copper Contributor
      Apr 27, 2023

      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