Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Procedure will not run in Excel 97

Status
Not open for further replies.

djburnheim

Technical User
Jan 22, 2003
71
AU
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")
EndDate = Format(Range("D4").Value + 5, "yyyy-mm-dd HH:MM:SS")
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
 
It looks like you are having an isuue with the different microslop platforms.
Unfortunatley I have found that office 2000 is not backwards compatible with 97. I rewrote the code for the 2 different platforms. That is the only fix I know. To go where no programmer has gone before.
 
Yeah so much for Office 2000 being backward compatable! On face value I can't see any other way of doing this but I'm still picking VBA up so any help would be great.

Dave
 
Excel 97 help says about the array function; Returns a Variant containing an array. I believe that is where things go sour for you. Shouldn't the Commandtext property be a string?I am assuming you want the value of CommandText to be: 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) & "')". If that's the case all you need is .CommandText = OpenQuery. I am not intimately familiar with Excel 2k but I believe that might be the difference.
hope this helps,
sdraper
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top