Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 27-02-2009
Member
 
Join Date: Feb 2009
Posts: 72
Batch extraction in 1 Excel file

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
Reply With Quote
  #2  
Old 27-02-2009
Member
 
Join Date: Dec 2008
Posts: 161
Re: Batch extraction in 1 Excel file

Quote:
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?
Reply With Quote
  #3  
Old 27-02-2009
Member
 
Join Date: Feb 2009
Posts: 72
Re: Batch extraction in 1 Excel file

I want to add the additional results on a new sheet / file.
Reply With Quote
  #4  
Old 27-02-2009
Member
 
Join Date: Apr 2008
Posts: 193
Re: Batch extraction in 1 Excel file

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.
Reply With Quote
  #5  
Old 27-02-2009
Member
 
Join Date: Feb 2009
Posts: 72
Re: Batch extraction in 1 Excel file

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.
Reply With Quote
  #6  
Old 27-02-2009
Member
 
Join Date: Apr 2008
Posts: 193
Re: Batch extraction in 1 Excel file

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...
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Batch extraction in 1 Excel file"
Thread Thread Starter Forum Replies Last Post
VB Script or batch file to run Excel Macro 2010 Fakhry Software Development 2 19-06-2012 12:23 PM
make file name list in excel using batch file shibinpanayi Windows Software 1 04-06-2011 03:44 AM
run Batch-file export in excel (its for a Movie database) rayg Software Development 4 22-12-2010 08:31 PM
CAB file extraction error Fame Windows Software 3 09-05-2009 11:51 AM
Extraction of data on interval - Excel machok Software Development 3 16-02-2009 07:31 PM


All times are GMT +5.5. The time now is 02:49 AM.