Query not returning all the rows
Can't see yet where it might go wrong.
It won't fix it but I would declare Period as Long and use the & to
concatenate
strings rather than the + you are using.
What is happening he
End With
Selection.Copy
Workbooks.Add
Is Selection the right range here?
RBS
"barnabel" wrote in message
...
Here is the code. The actual query should be irrelivent. If I print the
sqlstring in the immediate window and paste it into an SQL command window
it
returns all the rows.
Dim sqlString As String
Dim period As Integer
Dim client As String
Dim sourceBook As String
Dim targetBook As String
sqlString = ""
period = 1
sourceBook = ActiveWorkbook.Name
While Not IsEmpty(Sheets("Parameters").Cells(2, period + 1))
If (sqlString < "") Then
sqlString = sqlString + "union all" + Chr(13)
End If
sqlString = sqlString + buildQuery(Sheets("Parameters").Cells(2,
period + 1), "pba", Sheets("Parameters").Cells(3, period + 1),
Sheets("Parameters").Cells(4, period + 1), _
Sheets("Parameters").Cells(5, period + 1),
Sheets("Parameters").Cells(6, period + 1), Sheets("Parameters").Cells(7,
2))
sqlString = sqlString + "union all" + Chr(13)
sqlString = sqlString + buildQuery(Sheets("Parameters").Cells(2,
period + 1), "buy", Sheets("Parameters").Cells(3, period + 1),
Sheets("Parameters").Cells(4, period + 1), _
Sheets("Parameters").Cells(5, period + 1),
Sheets("Parameters").Cells(6, period + 1), Sheets("Parameters").Cells(7,
2))
period = period + 1
Wend
Sheets.Add
ActiveSheet.Name = "rawData"
Sheets("rawData").Cells(1, 1).Select
With Sheets("rawData").QueryTables.Add(Connection:= _
"ODBC;DSN=Sybase;SRVR=server;DB=database;UID=user; PWD=password", _
Destination:=Range(Sheets("rawData").Cells(1, 1),
Sheets("rawData").Cells(1, 1)))
.CommandText = sqlString
.Name = "RawCompetativeData"
.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
Selection.Copy
Workbooks.Add
targetBook = ActiveWorkbook.Name
Workbooks(targetBook).Activate
ActiveSheet.Name = "rawPerformance"
Sheets("rawPerformance").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Workbooks(sourceBook).Sheets("Parameters").Copy
After:=Workbooks(targetBook).Sheets("rawStationPer formance")
"RB Smissaert" wrote:
Could you post the relevant code?
Did you use an Integer variable somewhere?
RBS
"barnabel" wrote in message
...
I have Excel 2007 which is supposed to support sheets longer than 65K
rows.
I have a query that should return more than 135K rows. But there are
only
65K rows in the sheet. Anybody know how to set the maximum number of
rows
returns?
|