| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Returning a value based on a query | Excel Worksheet Functions | |||
| Alternatives to Excel VBA (Query Tables returning more than a million rows) | Excel Programming | |||
| MS Query Not Returning Data to Excel | Excel Programming | |||
| SQL Query not returning correct results | Excel Programming | |||
| Query returning more data than will fit on worksheet with VBA DB query... | Excel Programming | |||
Linear Mode
