基于单元格输入在vba中更改excel查询源

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then '将A1替换为你的输入单元格
        '在这里编写更改查询源的代码
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then '将A1替换为你的输入单元格
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets("Sheet1") '将Sheet1替换为你的工作表名称
        
        '清除现有查询
        ws.ListObjects("Table1").QueryTable.Delete
        
        '设置新的查询源
        Dim querySource As String
        querySource = ws.Range("A1").Value '将A1替换为你的输入单元格
        
        '创建新的查询
        Dim qt As QueryTable
        Set qt = ws.ListObjects("Table1").QueryTable
        qt.Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & querySource & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        qt.CommandType = xlCmdTable
        qt.CommandText = "Sheet1$" '将Sheet1替换为你的工作表名称
        qt.Refresh
        
        '调整表格格式
        ws.ListObjects("Table1").TableStyle = "TableStyleMedium2"
    End If
End Sub