djburnheim
Technical User
I have written and tested the below procedure in Excel 2000 and it works fine but when a excel 97 users tries to run it I get error "Object doesn't support this propert or method...it doesn't like the line:
.commandtext=Array(StartQuery)
Any suggestions would be great!
Dave
***Code***
Dim OpenQuery As String
Dim ClosedQuery As String
Dim StartDate
Dim EndDate
Dim Row
StartDate = Format(Range("D4"
.Value - 1, "yyyy-mm-dd HH:MM:SS"![Wink ;) ;)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
EndDate = Format(Range("D4"
.Value + 5, "yyyy-mm-dd HH:MM:SS"![Wink ;) ;)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
Row = 6
For i = 1 To 18
OpenQuery = "SELECT Count(*) AS 'Cases Created' FROM Vantive.dbo.SW_HD_CASE SW_HD_CASE WHERE (SW_HD_CASE.swDateCreated Between {ts '" & StartDate & "'} And {ts '" & EndDate & "'}) AND (SW_HD_CASE.swCreatedBy='" & Cells(Row, 3) & "')"
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=SQLServer;UID=user;PWD=password;APP=Microsoft® Query;DATABASE=database" _
, Destination:=Cells(Row, 16))
.CommandText = Array(OpenQuery)
.Name = "Open"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Cells(Row, 16).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Font.ColorIndex = 54
With Selection
.HorizontalAlignment = xlCenter
End With
Next i
.commandtext=Array(StartQuery)
Any suggestions would be great!
Dave
***Code***
Dim OpenQuery As String
Dim ClosedQuery As String
Dim StartDate
Dim EndDate
Dim Row
StartDate = Format(Range("D4"
EndDate = Format(Range("D4"
Row = 6
For i = 1 To 18
OpenQuery = "SELECT Count(*) AS 'Cases Created' FROM Vantive.dbo.SW_HD_CASE SW_HD_CASE WHERE (SW_HD_CASE.swDateCreated Between {ts '" & StartDate & "'} And {ts '" & EndDate & "'}) AND (SW_HD_CASE.swCreatedBy='" & Cells(Row, 3) & "')"
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=SQLServer;UID=user;PWD=password;APP=Microsoft® Query;DATABASE=database" _
, Destination:=Cells(Row, 16))
.CommandText = Array(OpenQuery)
.Name = "Open"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Cells(Row, 16).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Font.ColorIndex = 54
With Selection
.HorizontalAlignment = xlCenter
End With
Next i