Batch extraction in 1 Excel file

[Pikachoo]

Hello,
I hangs on a problem which is as follows:
I have 1 application that extracts from a SQL server to Excel file 1.
As the quantities extracted are important, there is an extraction "by lot" (as in vb.net, no DTS) ...
It works well except that it does not if it exceeds 65,536 lines allowed in 1 tab excel ...
Result missing records. in the generated files.

Here is the code used to extract:

 AppExcel. DisplayAlerts = False 
  xlBook = appExcel. Workbooks. Open (Extraction_DI. sNomRepSource & "temp.xls") 
  = xlsheet xlBook. Sheets. Item (1) 
  xlsheet. Name = "Results" 
  xlsheet. Activate () 

  With xlsheet. QueryTable. Add (sChaineExcel, xlsheet. Range ( "A1")) 
              . CommandText = sSQL 
              . Name = "Results" 
              . FieldName = True 
              . RowNumbers = False 
              . FillAdjacentFormulas = False 
              . PreserveFormatting = True 
              . RefreshOnFileOpen = False 
              . BackgroundQuery = True 
              . RefreshStyle = Excel. XlCellInsertionMode. XlOverwriteCells 
              . SavePassword = False 
              . SaveData = False 
              . AdjustColumnWidth = True 
              . RefreshPeriod = 0 
              . PreserveColumnInfo = True 
              . Refresh (BackgroundQuery: = False) 
  End With 
  xlsheet. Range ( "A1"). Select () 
  appExcel. ActiveWorkbook. SaveAs (sFichier) 
  appExcel. ActiveWorkbook. Close ()

If anyone knows a way to manage this "limit" of 65,536 in a batch extraction?
Thank you in advance

[Alejandro]

If anyone knows a way to manage this "limit" of 65,536 in a batch extraction?

What do you mean by "manage"?
Want the applicable 65,536 lines or you want to add the additional results on a new sheet / file?

[Pikachoo]

I want to add the additional results on a new sheet / file.

[Mecurtis]

I had an idea, but I have not had time to test it.
The idea would be to split your SQL into portions of 65535 lines.

It would be East to say but More difficult is the writing of the relevant application (s)! The Transact-SQL functions could help you: Functions (Transact-SQL) Quickly by browsing the doc, I saw that ROW_NUMBER keywords and / or RANK might serve you.

[Pikachoo]

Thank you very much for the info.
I think that will help me ROW_NUMBER.
I will test it to see the performance, and I would look back on this post.

[Mecurtis]

To create an atmosphere I insert the following line:
My.Computer.Audio.Play ( "C: \ Users \ Sal \ Desktop \ Raffle \ Acclamations.wav" AudioPlayMode.WaitToComplete)
I leads to: Exception Filenotfound
After checking the path is good and the audio file is read and played without problem with Windows Media Player...