Excel Row Limit
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Excel Row Limit

  1. #1
    Guest

    Default

     
    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?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    Guest

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    232
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Guest

    Default

    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..

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    232
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    matthew.rowe@tmp.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com