Excel Row Limit

[Guest]

Hi,
Might sound rediculous.. but I have some data that exceeds Excel's 65536 row limit. Basically the data is some sort of database table. Any ideas how to get past this limitation. Our company is on a tight budget, so my boss doesn't want to invest in other software. Need to do some reports.
Any ideas guys?

 

[Mudface]

Would it be possible to split the data across a few sheets, maybe?

 

[Guest]

Well, I am looking into that possibility.. but I am 'programming' the sheet -> its to be used by ppl with limited Excel knowledge. Since I am using VBA, would splitting it be possible?? Plus the report would make extensive use of array formulas + formulas. Will it break if multipe sheets are used?? Here's the loading code anyway..

Sub Load(strFileName As String)

Workbooks.OpenText FileName:=strFileName _
, Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(30, 1), Array(43, 1), Array(57, 1), Array(74, 1), Array(79, 1), _
Array(84, 1), Array(95, 1), Array(117, 1), Array(133, 1), Array(152, 1), Array(171, 1), _
Array(185, 1), Array(193, 1), Array(197, 1), Array(199, 1), Array(200, 1), Array(201, 1), _
Array(204, 1))
Selection.EntireRow.Insert

End Sub

Sub FormatData()

Columns("B:B").NumberFormat = "0"
Columns("C:C").NumberFormat = "0"
Columns("D:D").NumberFormat = "0"
Columns("G:G").NumberFormat = "dd-mmm-yy"
Columns("H:H").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Columns("I:I").NumberFormat = "#,##0.00;[Red]#,##0.00"
Columns("J:J").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Columns("K:K").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Columns("M:M").NumberFormat = "dd-mmm-yy"

End Sub

Sub FormatColumn()

Range("A1").FormulaR1C1 = "NAME"

Range("B1").FormulaR1C1 = "PV"

Range("C1").FormulaR1C1 = "BV"

Range("D1").FormulaR1C1 = "LOAN #"

Range("E1").FormulaR1C1 = "STATE"

Range("F1").FormulaR1C1 = "RESP COLL"

Range("G1").FormulaR1C1 = "DUE DATE"

Range("I1").FormulaR1C1 = "BALANCE"

Range("J1").FormulaR1C1 = "OVL AMT"

Range("K1").FormulaR1C1 = "TOTAL DUE"

Range("L1").FormulaR1C1 = "TRXN"

Range("M1").FormulaR1C1 = "DATE"

Range("N1").FormulaR1C1 = "TIME"

Range("O1").FormulaR1C1 = "ACTIVITY"

Range("P1").FormulaR1C1 = "PLACE"

Range("Q1").FormulaR1C1 = "CONTACT"

Range("R1").FormulaR1C1 = "RTE"

Range("S1").FormulaR1C1 = "LINE 10"

Range("T1").FormulaR1C1 = "ABV"

With Range("A1:T1")
.Font.ColorIndex = 2
.Interior.ColorIndex = 1
.HorizontalAlignment = xlCenter
.Font.Bold = True
End With

Columns("A:T").AutoFit

End Sub

 

[mrowe]

Had you thought about connecting direct to the data source via SQL and doing a query at Data Base level rather than dumping all the data in a spreadsheet.

If you can get access to your db you can use the Data->pivot table and choose the access exteral DB option. You will need to set up ODBC drivers to connect if you don't have them done already. From there it's just a matter of selecting the tables that contain the data you want and creating a query.

Let me know if your interested, I've done this a few times and it's quick, easy and cheap!

Cheers
Matt

 

[Guest]

Hi..
Sorry.. went home to get some sleep.. Well, actually, am working in a bank. As part of an automation project, we are looking into ways to automate the Site Visit assignment. All the assignments are done using a mainframe system. The following morning, we get a dump of all transactions done. I would love to have SQL support... But I am interested.. maybe if you could show me how to do it, I could persuade my bosses to inves in SQL... Thanks..

 

[mrowe]

sure, send me a mail to work and I'll go thru what I know.

matthew.rowe@tmp.com