Macro to filter a query based on user input

[suttorpa]

Hello,

I never excel scripted before.. so I am looking for some help with a macro, I created a workbook with a company inventory of all IT items.. each sheet has a listing of a certain type of device (workstations/switchs/keyboards/monitors/printers ect). I have a query that is activated by a macro that pulls all devices into one sheet with just a few related columns such as "location, serial number, model number, ect". The query works, but I am now trying to create a macro that accepts a users input of location and filters the query based on that so I can easily see what devices belong in which location. Here is the macro VBA as it is now (everything in black works, color Items I have added, the orange script works to query input, but the red script is creating an error.. I think it may be incorrect sequence, can anyone help?:

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+e
'
Dim myValue As Variant
myValue = InputBox("Give me some input")
Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Query Entire Inventory""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Query Entire Inventory]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = "Query_Entire_Inventory"
.Refresh BackgroundQuery:=False
.ActiveSheet.Range("$A$1:$Z$9359").AutoFilter Field:=Location.Column, Criteria1:="myValue"
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

 

[mumps]

It would be easier to help if I could see your actual file. Perhaps you could upload a copy to a free site such as Dropbox or box.com. Mark the file for sharing and you will be given a link to the file that you can post here. Include a detailed explanation of what you want to do referring to specific cells and sheets.

 

[suttorpa]

Thank you mumps, your questions helped me figure it out.. your right activesheet filters a sheet not a query.. I made the adjustment to the query itself in .CommandText = Array("SELECT * FROM [Query Entire Inventory] WHERE Location=myValue")

That gave me an error that this command is not supported in excel..

So instead I have a macro that first uses the query to create a sheet with all items on it, then I have a seperate macro that takes input from the user and edits that sheet like so:

Sub Macro2()
'
' Macro2 Macro
'
'
Dim myValue As String
myValue = InputBox("Give me some input")
Worksheets("Sheet15").Range("A1").AutoFilter _
field:=1, _
Criteria1:=myValue, _
VisibleDropDown:=False
End Sub

Thanks a lot for your help, i am new at this..

 

[mumps]

My pleasure.