Query not returning all the rows
 

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?

Query not returning all the rows
 

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?

Query not returning all the rows
 

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?

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?

Query not returning all the rows
 

Period is indexing Columns so it really doesn't need to be a long. Each
column on the parameter page contains the information to specify a range of
data. The sheet works perfectly for up to 1 years worth of data whether I
specify it as jan-dec in 1 column or jan-mar, apr-jun,jul-sep and oct-dec but
somebody asked for 3 years worth of data. Even so period is less than 10.

What difference does using & rather than + do? The resulting query printed
and copied to the sql window works fine.

"RB Smissaert" wrote:

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

There were 2 lines that were irrelivant so I didn't include them but I
missed the line:

Cells.Select

so if selection is not right then it is still an excel problem that it isn't
selecting the right range when there are more than 65K rows. I'll look at
that.
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?

Query not returning all the rows
 

As I said, these changes won't fix anything, but still worth doing.
Long is better as you never know what might happen to the code in the
future and it will be (though unmeasurable) faster. Using & instead of +
will make the code clearer.
Is the Selected range fine at the Copy?

I don't 2007 yet, so not sure what could be causing the trouble.

RBS

"barnabel" wrote in message
...
Period is indexing Columns so it really doesn't need to be a long. Each
column on the parameter page contains the information to specify a range
of
data. The sheet works perfectly for up to 1 years worth of data whether I
specify it as jan-dec in 1 column or jan-mar, apr-jun,jul-sep and oct-dec
but
somebody asked for 3 years worth of data. Even so period is less than 10.

What difference does using & rather than + do? The resulting query
printed
and copied to the sql window works fine.

"RB Smissaert" wrote:

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

There were 2 lines that were irrelivant so I didn't include them but I
missed the line:

Cells.Select

so if selection is not right then it is still an excel problem that it
isn't
selecting the right range when there are more than 65K rows. I'll look at
that.
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?

Query not returning all the rows
 

Thanks for making me look at things from a different angle. I think I know
the solution but I can't test it until I get to the office Monday. I'll Post
the results Monday.

"RB Smissaert" wrote:

As I said, these changes won't fix anything, but still worth doing.
Long is better as you never know what might happen to the code in the
future and it will be (though unmeasurable) faster. Using & instead of +
will make the code clearer.
Is the Selected range fine at the Copy?

I don't 2007 yet, so not sure what could be causing the trouble.

RBS

"barnabel" wrote in message
...
Period is indexing Columns so it really doesn't need to be a long. Each
column on the parameter page contains the information to specify a range
of
data. The sheet works perfectly for up to 1 years worth of data whether I
specify it as jan-dec in 1 column or jan-mar, apr-jun,jul-sep and oct-dec
but
somebody asked for 3 years worth of data. Even so period is less than 10.

What difference does using & rather than + do? The resulting query
printed
and copied to the sql window works fine.

"RB Smissaert" wrote:

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

There were 2 lines that were irrelivant so I didn't include them but I
missed the line:

Cells.Select

so if selection is not right then it is still an excel problem that it
isn't
selecting the right range when there are more than 65K rows. I'll look at
that.
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?

Query not returning all the rows
 

Solution found. The sheet was opened in compatability mode and compatability
MUST be maintained. It has to be saved as an XLSM closed and reopened before
compatability mode is lost. Once it is a 2007 file it returned all the rows.

Peter Richardson

"barnabel" wrote:

Thanks for making me look at things from a different angle. I think I know
the solution but I can't test it until I get to the office Monday. I'll Post
the results Monday.

"RB Smissaert" wrote:

As I said, these changes won't fix anything, but still worth doing.
Long is better as you never know what might happen to the code in the
future and it will be (though unmeasurable) faster. Using & instead of +
will make the code clearer.
Is the Selected range fine at the Copy?

I don't 2007 yet, so not sure what could be causing the trouble.

RBS

"barnabel" wrote in message
...
Period is indexing Columns so it really doesn't need to be a long. Each
column on the parameter page contains the information to specify a range
of
data. The sheet works perfectly for up to 1 years worth of data whether I
specify it as jan-dec in 1 column or jan-mar, apr-jun,jul-sep and oct-dec
but
somebody asked for 3 years worth of data. Even so period is less than 10.

What difference does using & rather than + do? The resulting query
printed
and copied to the sql window works fine.

"RB Smissaert" wrote:

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

There were 2 lines that were irrelivant so I didn't include them but I
missed the line:

Cells.Select

so if selection is not right then it is still an excel problem that it
isn't
selecting the right range when there are more than 65K rows. I'll look at
that.
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?

Query not returning all the rows
 

Thanks for letting us know.

RBS

"barnabel" wrote in message
...
Solution found. The sheet was opened in compatability mode and
compatability
MUST be maintained. It has to be saved as an XLSM closed and reopened
before
compatability mode is lost. Once it is a 2007 file it returned all the
rows.

Peter Richardson

"barnabel" wrote:

Thanks for making me look at things from a different angle. I think I
know
the solution but I can't test it until I get to the office Monday. I'll
Post
the results Monday.

"RB Smissaert" wrote:

As I said, these changes won't fix anything, but still worth doing.
Long is better as you never know what might happen to the code in the
future and it will be (though unmeasurable) faster. Using & instead of
+
will make the code clearer.
Is the Selected range fine at the Copy?

I don't 2007 yet, so not sure what could be causing the trouble.

RBS

"barnabel" wrote in message
...
Period is indexing Columns so it really doesn't need to be a long.
Each
column on the parameter page contains the information to specify a
range
of
data. The sheet works perfectly for up to 1 years worth of data
whether I
specify it as jan-dec in 1 column or jan-mar, apr-jun,jul-sep and
oct-dec
but
somebody asked for 3 years worth of data. Even so period is less
than 10.

What difference does using & rather than + do? The resulting query
printed
and copied to the sql window works fine.

"RB Smissaert" wrote:

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

There were 2 lines that were irrelivant so I didn't include them but
I
missed the line:

Cells.Select

so if selection is not right then it is still an excel problem that
it
isn't
selecting the right range when there are more than 65K rows. I'll
look at
that.
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?