Using an Excel userform to launch queries with specific data as parameters-CodePudding

How can I send some userform datas to a query without printing them in a sheet ?

My current userform ask some [ Date / Name / File path ] using listBox and TextBox. Depending of the choice of the user, some queries can be launch or not. Obviously these queries are using the datas set in the userform.

I don't want to use the userform to print data in Excel and then start some queries that get their parameters from these last datas.

Is that possible ?

CodePudding user response：

You can write a sub that takes your parameters and rewrites a query for you. Here's an example of what I mean.

Sub ModifyPowerQuery(filePath As String)
  Dim wb As Workbook
  Set wb = ThisWorkbook
  
  Dim qry As QueryTable
  Set qry = wb.Queries("My Query") ' Modify the existing "My Query" power query
  
  ' Set the new command text for the query
  qry.CommandText = "SELECT * FROM """ & filePath & """"
  
  Dim ws As Worksheet
  Set ws = wb.Worksheets("Sheet1")
  
  Dim qt As QueryTable
  Set qt = ws.QueryTables("My Query") ' Modify the existing query table on the worksheet
  
  qt.Refresh ' Refresh the query to load the data from the new file
End Sub