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,
Ravi Amara 
(BOB member since 2002-10-02)
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 Rathbun (BOB member since 2002-06-06)
Hi Dave,
Thanks for responding so fast… I am replying back in a HOPE that you are still there 
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:
'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…
Ravi Amara (BOB member since 2002-10-02)
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:
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
Dave Rathbun (BOB member since 2002-06-06)
Thats a great idea Dave Thank you very much. Now, will try to implement in the similar lines.
When I am adding the next sheet using
ActiveWorkbook.Sheets.AddExcel is adding the sheet to the left side.
How Can I make it to the right side?
More to follow 
Ravi Amara (BOB member since 2002-10-02)
The following code will do the trick!
Sheets.Add
ActiveWindow.SelectedSheets.Select
ActiveWindow.SelectedSheets.Move After:=Sheets(Sheets.Count)
Ravi Amara (BOB member since 2002-10-02)
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.
Dave Rathbun (BOB member since 2002-06-06)
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
winningratings (BOB member since 2004-06-09)
Is this really a problem about Business Objects?
Steve Krandel (BOB member since 2002-06-25)
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!!
winningratings (BOB member since 2004-06-09)
The limitation is in Excel…not Business Objects.
Eileen King (BOB member since 2002-07-10)
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 (BOB member since 2002-09-19)
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.
Dave Doran (BOB member since 2004-06-25)
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
BOB_DW (BOB member since 2004-08-26)
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