BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

IBIS 2014: Limitless BI 2.0 
IBIS 2014: Limitless BI 2.0 (Opens a new window)  

General Notice: Upcoming Events: BONYMAUG: May 14, BI2014 Nice, France: May 21.

How to handle more than 65536 rows in EXCEL?


 
Search this topic... | Search SDK (VBA/ASP/JSP)... | Search Box
Register or Login to Post    Forum Index -> SDK (VBA/ASP/JSP)  Previous TopicPrint TopicNext Topic
Author Message
Ravi Amara
Principal Member
Principal Member



Joined: 02 Oct 2002

Posts: 243
Location: Pittsburgh, PA


flag
PostPosted: Thu Nov 07, 2002 5:17 pm 
Post subject: How to handle more than 65536 rows in EXCEL?

Hi All,

I am generating an excel sheet from a tab page using macro. But, Excel has a limit of 65536 rows and my report has more than 100, 000 rows. Excel's 65536 rows limit is for each tab page(Am I right?). So, How can I place the data into the next tab page (in Excel) after it reaches 65536 rows?

Thanks,

_________________
--There's only one way to fail, and that's to quit.
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002
ASUG Icon
speaker.gif*16
Posts: 21811
Location: Dallas, Texas


flag
PostPosted: Thu Nov 07, 2002 5:46 pm 
Post subject: Re: How to handle more than 65536 rows in EXCEL?

How are you generating the output now?

If you are using the option to export (I think it's ConvertTo, right?) to Excel, it will simply truncate to 65,536 rows. If you are writing out one line at a time using the Excel object model, then you should be able to stop at some point, switch to the next sheet, and continue writing out. I can't imagine doing that second option, as it will be incredibly slow.

Can you use a different format? Try CSV, and see what happens in Excel when you try to open the file...

Dave

_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2014-02-26 Big Universe + Security Profiles = Slow Query Generation
• 2013-12-11 Diversified Semantic Layer Guest Appearance
• 2013-09-19 Using OLAP Functions to Extend Calendar Capabilities
Back to top
Ravi Amara
Principal Member
Principal Member



Joined: 02 Oct 2002

Posts: 243
Location: Pittsburgh, PA


flag
PostPosted: Thu Nov 07, 2002 5:59 pm 
Post subject: Re: How to handle more than 65536 rows in EXCEL?

Dave Rathbun wrote:
How are you generating the output now?

If you are using the option to export (I think it's ConvertTo, right?) to Excel, it will simply truncate to 65,536 rows. If you are writing out one line at a time using the Excel object model, then you should be able to stop at some point, switch to the next sheet, and continue writing out. I can't imagine doing that second option, as it will be incredibly slow.

Can you use a different format? Try CSV, and see what happens in Excel when you try to open the file...

Dave


Hi Dave,

Thanks for responding so fast.. I am replying back in a HOPE that you are still there icon_biggrin.gif

Code:

Sub ExcelRepGenerate(doc As busobj.Document, ByVal FileName As String)
    ...
    Set rep = doc.ActiveReport
    OrigName = rep.Name
    FileName = FileName & "\default.xls"
    x = rep.ExportAsText(FileName)
    ..       
       
End Sub


OK..I am not facing any problem while generating the report as I am creating an xls sheet and exporting the data as text into the xls sheet. But, the main problem is, when the end-user try to download(we are providing this option) this file, he can see only 65536 rows.

Microsoft gave the following code in their web page for tackling more than 65536 rows:

Code:


'All lines that begin with an apostrophe (') are remarks and are not
   'required for the macro to run.
   Sub LargeFileImport()

      'Dimension Variables
      Dim ResultStr As String
      Dim FileName As String
      Dim FileNum As Integer
      Dim Counter As Double
      'Ask User for File's Name
      FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
      'Check for no entry
      If FileName = "" Then End
      'Get Next Available File Handle Number
      FileNum = FreeFile()
      'Open Text File For Input
      Open FileName For Input As #FileNum
      'Turn Screen Updating Off
      Application.ScreenUpdating = False
      'Create A New WorkBook With One Worksheet In It
      Workbooks.Add template:=xlWorksheet
      'Set The Counter to 1
      Counter = 1
      'Loop Until the End Of File Is Reached
      Do While Seek(FileNum) <= LOF(FileNum)
         'Display Importing Row Number On Status Bar
          Application.StatusBar = "Importing Row " & _
             Counter & " of text file " & FileName
          'Store One Line Of Text From File To Variable
          Line Input #FileNum, ResultStr
          'Store Variable Data Into Active Cell
          If Left(ResultStr, 1) = "=" Then
             ActiveCell.Value = "'" & ResultStr
          Else
             ActiveCell.Value = ResultStr
          End If
         
          'For xl97 and later change 16384 to 65536
          If ActiveCell.Row = 16384 Then
             'If On The Last Row Then Add A New Sheet
             ActiveWorkbook.Sheets.Add
          Else
             'If Not The Last Row Then Go One Cell Down
             ActiveCell.Offset(1, 0).Select
          End If
          'Increment the Counter By 1
          Counter = Counter + 1
      'Start Again At Top Of 'Do While' Statement
      Loop
      'Close The Open Text File
      Close
      'Remove Message From Status Bar
      Application.StatusBar = False

   End Sub


It takes a text file as input and splits it into multiple work sheets. My proble is how to embed this code into the xls sheet that I am creating!!

Hope I am clear..

_________________
--There's only one way to fail, and that's to quit.
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002
ASUG Icon
speaker.gif*16
Posts: 21811
Location: Dallas, Texas


flag
PostPosted: Fri Nov 08, 2002 9:21 am 
Post subject: Re: How to handle more than 65536 rows in EXCEL?

txamara wrote:
It takes a text file as input and splits it into multiple work sheets. My proble is how to embed this code into the xls sheet that I am creating!!

Well, if you look at their code, they are doing exactly what I suggested might be required... they are importing the text file one line at a time, and starting a "new sheet" when they fill up the first one.

Instead of trying to get that code into every sheet, what I would do is create one sheet that includes that code, then reference it in your export process.

In other words, you would:

1. Refresh your query
2. Export to CSV
3. Call (from within BusObj) your Excel Macro in the specific sheet
4. After the import finishes, add a "Save As..." to save your imported workbook (multiple sheets) to the desired name.

In other words, the eventual output wks file doesn't have any macro code. But you have a worksheet that you call from your busobj macro that does include that code.

You can call Excel macros from within BusObj if you load the Excel object model. I've done it before, but don't have any sample code that I can find. I don't remember it being particularly difficult.

Dave
Back to top
Ravi Amara
Principal Member
Principal Member



Joined: 02 Oct 2002

Posts: 243
Location: Pittsburgh, PA


flag
PostPosted: Fri Nov 08, 2002 10:04 am 
Post subject: Re: How to handle more than 65536 rows in EXCEL?

Dave Rathbun wrote:

In other words, you would:

1. Refresh your query
2. Export to CSV
3. Call (from within BusObj) your Excel Macro in the specific sheet
4. After the import finishes, add a "Save As..." to save your imported workbook (multiple sheets) to the desired name.

In other words, the eventual output wks file doesn't have any macro code. But you have a worksheet that you call from your busobj macro that does include that code.

You can call Excel macros from within BusObj if you load the Excel object model. I've done it before, but don't have any sample code that I can find. I don't remember it being particularly difficult.

Dave


Thats a great idea Dave icon_biggrin.gif Thank you very much. Now, will try to implement in the similar lines.

icon_question.gif When I am adding the next sheet using
Code:
ActiveWorkbook.Sheets.Add

Excel is adding the sheet to the left side.

How Can I make it to the right side?

More to follow icon_smile.gif

_________________
--There's only one way to fail, and that's to quit.
Back to top
Ravi Amara
Principal Member
Principal Member



Joined: 02 Oct 2002

Posts: 243
Location: Pittsburgh, PA


flag
PostPosted: Fri Nov 08, 2002 3:31 pm 
Post subject: Re: How to handle more than 65536 rows in EXCEL?

txamara wrote:
When I am adding the next sheet using
Code:
ActiveWorkbook.Sheets.Add

Excel is adding the sheet to the left side.

How Can I make it to the right side?

More to follow icon_smile.gif


The following code will do the trick!

Code:

Sheets.Add
ActiveWindow.SelectedSheets.Select
ActiveWindow.SelectedSheets.Move After:=Sheets(Sheets.Count)

_________________
--There's only one way to fail, and that's to quit.
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002
ASUG Icon
speaker.gif*16
Posts: 21811
Location: Dallas, Texas


flag
PostPosted: Fri Nov 08, 2002 3:42 pm 
Post subject: Re: How to handle more than 65536 rows in EXCEL?

Once you get everything working, if it's something you can share, send it to busobj@forumtopics.com and we'll get it posted in the code samples forum. If you can't, no problem. But I bet it would be educational for other folks to see.
Back to top
winningratings
Forum Member
Forum Member



Joined: 09 Jun 2004

Posts: 2



PostPosted: Wed Jun 09, 2004 12:22 am 
Post subject: Re: How to handle more than 65536 rows in EXCEL?

Hi All

I am having a problem with Microsoft Excel - I need to go beyond 65K lines - is it not possible to do this??

One solution I have thought of is to use Access as the main Table and see if I can run VLOOKUP type formulas from excell onto the table in Access - will this work?? If so I would need to also run from queries

The problem I have had with Access in the past is when trying to use Excell type formulas such as =IF, =IF(AND), =IF(OR) etc. These are quite complicated formulas and Im not sure if Access can cope with them - or do I have to do something in Visual Basic (which I dont know anything much about!!)

If I cant find a solution I might have to look for alternative database

Any suggestions appreciated - thanks

_________________
"The Best Yet" Horse Ratings - http://www.winningratings.com
Back to top
Steve Krandel
Forum Devotee
Forum Devotee



Joined: 24 Jun 2002

medal_bronze.gifspeaker.gif*7
Posts: 12411
Location: San Jose, California


flag
PostPosted: Wed Jun 09, 2004 12:40 am 
Post subject: Re: How to handle more than 65536 rows in EXCEL?

Is this really a problem about Business Objects?
_________________
Image link Steve Krandel
Manager, Enterprise BI Tools
Image link
Back to top
winningratings
Forum Member
Forum Member



Joined: 09 Jun 2004

Posts: 2



PostPosted: Wed Jun 09, 2004 12:46 am 
Post subject: Re: How to handle more than 65536 rows in EXCEL?

Yes!!

I need more than 65K rows!!

I am rapidly running out of space - shortly I will have to simply write my own database program - at least that way - it will at least work!!

_________________
"The Best Yet" Horse Ratings - http://www.winningratings.com
Back to top
Eileen King
Forum Fanatic
Forum Fanatic



Joined: 10 Jul 2002
ASUG Icon
speaker.gif*3
Posts: 5784
Location: Cleveland, OH


flag
PostPosted: Wed Jun 09, 2004 7:30 am 
Post subject: Re: How to handle more than 65536 rows in EXCEL?

Steve Krandel wrote:
Is this really a problem about Business Objects?


The limitation is in Excel...not Business Objects.

_________________
Eileen King, PMC Image link
Claraview
Eileen.King@claraview.com
www.claraview.com
Back to top
Dwayne Hoffpauir
Forum Groupie
Forum Groupie



Joined: 19 Sep 2002
ASUG Icon
medal_gold.gif*2speaker.gif*5medal_bronze.gif
Posts: 8639
Location: Plano, TX USA


flag
PostPosted: Wed Jun 09, 2004 8:34 am 
Post subject: Re: How to handle more than 65536 rows in EXCEL?

Not knowing exactly what you're trying to accomplish, it's rather hard for us to offer any Business Objects advice.

With that said, as has been covered, the 65535 limit is quite firm in Excel. I think long-term you will be more satisfied with an Access solution. The formulas are a bit different with Access, but not terribly so. They can certainly handle the if / and / or logic you mentioned. And vlookups are handled even easier in Access with a simple join.

If you absolutely must stay with Excel, your code would need to break the data into 65535 row chunks and create multiple spreadsheet tabs as needed.

_________________
Dwayne Hoffpauir
Image link
Back to top
Dave Doran
Forum Member
Forum Member



Joined: 25 Jun 2004

Posts: 1
Location: Ithaca, NY



PostPosted: Fri Jun 25, 2004 3:14 pm 
Post subject: Re: How to handle more than 65536 rows in EXCEL?

I know we're not suppose to advertise here but my company is working on an Excel add-in that will solve Excel's 65536 row limitation. We should have some beta code by September. To find us, do a google search on 65536.
Back to top
BOB_DW
Principal Member
Principal Member



Joined: 26 Aug 2004

Posts: 380



PostPosted: Fri Mar 10, 2006 2:10 pm 
Post subject: Re: How to handle more than 65536 rows in EXCEL?

I can imagine users resorting to other means like saving as a text file when using full client. But when infoview users save a report as an excel onto their machines which has more than 65k rows, is there a resolution?
Please advice.
TIA.
Infoview Version 6.0
Back to top
Sebastien Goiffon
Forum Fanatic
Forum Fanatic



Joined: 29 Sep 2004
ASUG Icon
Posts: 6369
Location: France


flag
PostPosted: Tue Mar 14, 2006 5:02 am 
Post subject: Re: How to handle more than 65536 rows in EXCEL?

Hi all,

I just want to alert (pop up ?) users if they make an export to Excel from Webi that there are more than 65536 rows in the dataprovider or in the report ! I'm using Webi (.wid) doc only. Do you know how can i do this ? using SDK ? (Webi is under Win2003)

Thanks for answers

Regards

_________________
360Suite: Security, backup, promotion, impact analysis, bursting, metadata solutions.
Spend 9 minutes to save HOURS. Alternative of CMC and LCM.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> SDK (VBA/ASP/JSP)  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0493 seconds using 17 queries. (SQL 0.0019 Parse 0.0345 Other 0.0129)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo