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:
Code:
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
Bookmarks