英文:
Using an Excel userform to launch queries with specific data as parameters
问题
如何将用户窗体数据发送到查询而不将其打印在工作表中？
我的当前用户窗体使用列表框和文本框询问一些[日期/名称/文件路径]。
根据用户的选择，可以启动或不启动某些查询。
显然，这些查询使用在用户窗体中设置的数据。
我不想使用用户窗体将数据打印到Excel中，然后启动一些从这些最后数据获取其参数的查询。
这是否可能？
英文:
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 ?
答案1
得分: 1
你可以编写一个子程序，它接受你的参数并为你重写查询。以下是我所指的示例。
Sub ModifyPowerQuery(filePath As String)
Dim wb As Workbook
Set wb = ThisWorkbook
Dim qry As QueryTable
Set qry = wb.Queries("My Query") ' 修改现有的 "My 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") ' 修改工作表上现有的查询表
qt.Refresh ' 刷新查询以加载新文件中的数据
End Sub
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论