Hi
I have a macro which calls a lot of ms queries each query has a date prompt
which is the same for each at present I have to enter date for each query .
Is there away to get vba to enter prompt value
I have requested date required at beginning of macro but do not know how to
use this for parameter in query via vba
part of macro as follows
sub macro1()
Sheets("TOTAL UNITS").Select
mydate = InputBox("enter date dd/mm/yy")
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;L:\SYMDATA\TINA\FACTORY\TOTAL UNITS.dqy",
Destination:=Range("A1"))
.Name = "TOTAL UNITS"
.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
Sheets("MORN OP1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;L:\SYMDATA\TINA\FACTORY\DAY MORN OP1.dqy", _
Destination:=Range("A1"))
.Name = "DAY MORN OP1"
.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
Sheets("LATE OP1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;L:\SYMDATA\TINA\FACTORY\DAY LATE OP1.dqy", _
Destination:=Range("A1"))
.Name = "DAY LATE OP1"
.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
Thanks
Tina