Hello folks,

I have the folowing vb script which I have managed to cobble together and it
works very well. Except that is if the user cancels instead of entering a
date at which point it errors. I would really like it if the user cancels
then that would cancel the macro.


Help.

Thanks,

Mike

Dim queryDate As Date
Application.ScreenUpdating = False
Sheets("NSKData").Select
Range("A1").Select
Range("A1:E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.QueryTable.Delete
'Range("G1").Select
'queryDate = Range("G1")
queryDate = InputBox("Enter a Date")
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=trop;UID=MAYNEM
;PWD=trader;DBQ=TROP.WORLD;DBA=W;APA=T;EXC=F;FEN=T ;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=I fAllSuccessful;MTS=" _
), Array("F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")),
Destination:=Range("A1"))
.CommandText = Array( _
"select trading_date, period, sum(band1_6sec_max)
,sum(band1_60sec_max)from tr_owner.tr_pcmp_offer_summ where status = 'c' and
asset_owner_code = 'SKOG'and trading_date = '" & queryDate & "' group by
trading_date, period order " _
, "by 1, 2")
.Name = "Query from trop"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("ChartNSK").Select
Application.ScreenUpdating = True
End Sub

Dim queryDate As Date
Application.ScreenUpdating = False
Sheets("NSKData").Select
With Range(Range("A1:E1"), Range("A1:E1").End(xlDown))
.ClearContents
.QueryTable.Delete
End With
On Error Resume Next
queryDate = InputBox("Enter a Date")
If Err.Number = 0 Then
With ActiveSheet.QueryTables.Add( _
Connection:=Array(Array( _
"ODBC;DSN=trop;UID=MAYNEM;PWD=trader;DBQ=TROP.WORL D;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB= T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS="
_
), _
Array("F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")), _
Destination:=Range("A1")).CommandText = Array( _
"select trading_date, period,
sum(band1_6sec_max),sum(band1_60sec_max)from tr_owner.tr_pcmp_offer_summ
where status = 'c' and asset_owner_code = 'SKOG'and trading_date = '" &
queryDate & "' group by trading_date, period order " _
, "by 1, 2")
.Name = "Query from trop"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End If
Sheets("ChartNSK").Select
Application.ScreenUpdating = True


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"MikeM" wrote in message
...
Hello folks,

I have the folowing vb script which I have managed to cobble together and
it
works very well. Except that is if the user cancels instead of entering a
date at which point it errors. I would really like it if the user cancels
then that would cancel the macro.


Help.

Thanks,

Mike

Dim queryDate As Date
Application.ScreenUpdating = False
Sheets("NSKData").Select
Range("A1").Select
Range("A1:E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.QueryTable.Delete
'Range("G1").Select
'queryDate = Range("G1")
queryDate = InputBox("Enter a Date")
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=trop;UID=MAYNEM
;PWD=trader;DBQ=TROP.WORLD;DBA=W;APA=T;EXC=F;FEN=T ;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=I fAllSuccessful;MTS="
_
), Array("F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")),
Destination:=Range("A1"))
.CommandText = Array( _
"select trading_date, period, sum(band1_6sec_max)
,sum(band1_60sec_max)from tr_owner.tr_pcmp_offer_summ where status = 'c'
and
asset_owner_code = 'SKOG'and trading_date = '" & queryDate & "' group by
trading_date, period order " _
, "by 1, 2")
.Name = "Query from trop"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("ChartNSK").Select
Application.ScreenUpdating = True
End Sub