J
jimmygoogle
i have a spreadsheet where there is a field call it 'name' and when
the user types in the name it returns the result matching the where
clause - is it possible that i do not have to hardcode the cell number
so the user can enter and return as many result sets as they want
i dont have VB experience but i have programming experience so i was
hoping to follow this model i found but I would like to not have to
hardcode C2, C3, etc - i dont know if this is even possible - any
insight is appreciated
Sub Sales_Query()
Dim salesrep As Variant
Dim salesdate As Date
salesrep = Range("C2").Text
salesdate = Range("C3").Value
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"ODBC;DSN=Northwind;Description=Northwind;APP=Microsoft Office
XP;DATABASE=Northwind;Trusted_Connection=YES"),
Destination:=Range("B5"))
.CommandText = Array("SELECT * FROM Orders WHERE EmployeeID="
& salesrep & " AND OrderDate > '" & salesdate & "' ORDER BY
OrderDate")
.Name = "Sales Query from Northwind"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
the user types in the name it returns the result matching the where
clause - is it possible that i do not have to hardcode the cell number
so the user can enter and return as many result sets as they want
i dont have VB experience but i have programming experience so i was
hoping to follow this model i found but I would like to not have to
hardcode C2, C3, etc - i dont know if this is even possible - any
insight is appreciated
Sub Sales_Query()
Dim salesrep As Variant
Dim salesdate As Date
salesrep = Range("C2").Text
salesdate = Range("C3").Value
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"ODBC;DSN=Northwind;Description=Northwind;APP=Microsoft Office
XP;DATABASE=Northwind;Trusted_Connection=YES"),
Destination:=Range("B5"))
.CommandText = Array("SELECT * FROM Orders WHERE EmployeeID="
& salesrep & " AND OrderDate > '" & salesdate & "' ORDER BY
OrderDate")
.Name = "Sales Query from Northwind"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub