Well, can’t export via wk4, cause the rows are even less. So, I can only download via text, problem is that last column data don’t get downloaded (wonder is this normal, or something wrong with my view, yeah?). But if downloaded under text, still cannot open via Excel, cause rows not more than 68000++. So does that mean text is the only way thus far?
Subject: I’ve got a silly question
What are you going to do with 70000+ lines of data in a spreadsheet or text file anyway?
I would suggest either doing it in text or smaller chunks. Or writing some LotusScript that creates the Excel files for you (you could even put groups of the 70k lines on different worksheets).
Dave
Chub Toad
Subject: 2 sheets: Good Idea
I did not thought about using 2 sheets, that’s one way to overcome it.
The reason for 70000 line items is that currently my view is taking too long for refresh, and can’t sent it over via email, size too big. The other reason for the many line items is due to having 6 or 7 categorization.
I have not came across any script written on posting it to 2nd sheet thus far, guest I have to KIV this option first till I got time to work out the script for this.
Cheerio!
Subject: Here’s a sample
Will,
I have a script that I’ve modified from someone else’s original stuff (looks like it was originally based on an import!) that writes data to two worksheets in a single Excel file. If you’d like to see more or have me show you, drop me an email (navarre_dave@yahoo.com) and I’ll answer questions (and direct you to some of the places I learned some of these things).
Hope this helps!
Dave
Chub Toad
Sub BuildTownHallExcelReport
' This module creates an Excel file, and puts all data from the currently open Town Hall report there
' All error-checking and error-routing is handled within this module
' 3 May 05: Dave Navarre, FAA NexGen Development Team
' A modification of an agent from Chub Toad, LLC, 26 May 04 (from an even older Import module)
' Modified 16 May 05 (DJN): include raw values on a second sheet and use 4 subroutines
' 6 Jun 05 (DJN): Corrected labelling above questions 10-21 in both raw and statistical data
' 17 Nov 05 (DJN): Copied from BuildExcelReport to BuildTownHallExcelReport
Dim primaryfilter As Variant
Dim surveysubject As Variant
Dim totalresponses As Variant
Dim xlFilename As Variant
Dim ans As Integer
Dim row As Integer
Dim xlWorkbook As Variant
Dim statsSheet As Variant
Dim rawdataSheet As Variant
’ On Error Goto PrintErrorNumber
On Error 213 Goto WrongFileName
On Error 13 Goto Cancelling
Filename:
xlFilename = doc.GetItemValue ( "FilterValue" )
If xlFilename (0) = "" Then
xlFilename (0) = "SummaryReport"
End If
Set xlWorkbook = Excel.ActiveWorkbook
Set statsSheet = xlWorkbook.ActiveSheet
statsSheet.Name = "Report"
Set rawdataSheet = xlWorkbook.Sheets(2)
rawdataSheet.Name = "Raw Data"
Print "Starting export to Excel file..."
statsSheet.Cells( 1, 1 ).Font.FontStyle = "Bold"
statsSheet.Cells( 1, 1 ).Font.Size = 14
primaryfilter = doc.GetItemValue ( "PrimaryFilter" )
surveysubject = doc.GetItemValue ( "SurveySubject" )
If primaryfilter (0) = "" Then
statsSheet.Cells( 1, 1 ).Value = "Summary Report on " & surveysubject (0)
Else
statsSheet.Cells( 1, 1 ).Value = "Filtered Report on " & surveysubject (0) & " (Filtered for " & primaryfilter (0) & ")"
End If
totalresponses = doc.GetItemValue ( "TotalResponses" )
statsSheet.Cells( 2, 1 ).Font.FontStyle = "Bold"
statsSheet.Cells( 2, 1 ).Value = "Responses: " & totalresponses (0)
row = 0 ' the row in the Excel file being exported
' question 6 answers are in the file attachment
Do While row < 6
'// Cycle through the rows of the Excel file, pulling the data over to Notes
row = row + 1
' place column labels
Select Case row
Case 1
Call PrintRawLabels ( 1, 1, rawdataSheet )
Call PrintStatisticalLabels ( 1, 4, statsSheet )
End Select
If row > 21 Then
Call PrintStatisticalValues ( row, row + 6, statsSheet )
Call PrintRawValues ( row, row + 3, rawdataSheet )
Else
If row < 10 Then
statsSheet.Cells( row +4, 2 ).RowHeight = 25.5
statsSheet.Cells( row +4, 2 ).WrapText = "True"
Call PrintStatisticalValues ( row, row + 4, statsSheet )
rawdataSheet.Cells( row +1, 1 ).RowHeight = 25.5
rawdataSheet.Cells( row +1, 1 ).WrapText = "True"
Call PrintRawValues ( row, row + 1, rawdataSheet )
Else
Call PrintStatisticalValues ( row, row + 5, statsSheet )
Call PrintRawValues ( row, row + 2, rawdataSheet )
End If
End If
Loop
' auto fit the columns to match the width of the text
Call statsSheet.Columns("C:E").EntireColumn.AutoFit
Call rawdataSheet.Columns("A:F").EntireColumn.AutoFit
statsSheet.Cells( 1, 2 ).ColumnWidth = 60
rawdataSheet.Cells( 1, 2 ).ColumnWidth = 15
rawdataSheet.Cells( 1, 7 ).ColumnWidth = 11
Print "Saving " & xlFilename (0)
Call xlWorkbook.SaveAs ("C:\" & xlFilename(0))
If AttachmentComplete ( xlFilename (0) ) Then
Call doc.Save ( True, False )
' close the new spreadsheet and delete it from the disk
xlWorkbook.Close
Kill "C:\" & xlFilename(0) & ".xls"
Call Excel.Workbooks.Add()
End If
Exit Sub
WrongFileName:
ans = Messagebox ( "The system could not find file." & Chr$(10) & "Would you like to re-enter the filename?", 5 + 32 + 0 + 0, "Continue?" )
If ans = 4 Then
Goto Filename
End If
Exit Sub
Cancelling:
ans = Messagebox ( "Would you like to quit?", 5 + 32 + 0 + 0, "Continue?" )
If ans = 4 Then
Goto Filename
End If
Exit Sub
PrintErrorNumber:
Messagebox "Error Number: " & Err & Chr$(10) & Error$ & Chr$(10) & "Continue?", 4 + 32 + 0 + 0, "Continue?"
Print "Disconnecting from Excel..."
xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
Excel.Quit '// Close Excel
Set Excel = Nothing '// Free the memory that we'd used
End Sub
Subject: Tqvm, Dave
Tqvm, hopefully I’d be able to analyse to the stage where I know how to tell notes to break up one view into two sheet. ( if first sheet reach its max in Excel, I think around 68000 line items).