Solved

Outputting a table to XLS Format

Posted on 2006-06-28
8
640 Views
Last Modified: 2008-01-09
I have a table containing around 20,000 rows and I want to be able to output this to excel. when I use the docmd.outputto blah blah acformat XLS, I get a message saying there are too many rows to output. I have tried outputting to acformatTXT then creating a csv but each line then in the spreadsheet is separated by lines ie:

----------------------------
jdshfkjhsadkfjhgh
----------------------------
l;gfjdklhjdsgljkhwa
----------------------------

Is there a way I can output to xls with so many rows?

Carl.
0
Comment
Question by:Carl2002
  • 4
  • 3
8 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16999594
Carl, Excel has a limit on the number of rows

16k or something I think

What you could possibly do is perhaps extract so many rows per sheet

U need to use Excel automation for this
0
 
LVL 4

Author Comment

by:Carl2002
ID: 16999611
Hi Rockiroads

Coventry made any super signings yet?

I ideally would need all rows in the same worksheet, is there anything I can do?
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 100 total points
ID: 16999639
Hi Carl2002,

Don't use the Analyze With Excel button.

Use File>Export and choose Excel97-2000 format.

The Analyze button uses a very old XL format which limits to 16k rows.



Pete
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 65

Expert Comment

by:rockiroads
ID: 16999661
Some signings, not great stuff though
Try Pete's approach

Actual row limit for Excel is 65k something so u can fit in one worksheet

I don't know if this will work in code though

use CopyFromRecordset


e.g.

Public Sub CreateExcelWB()

    Dim objXL As Object
    Dim objWB As Object
    Dim rs As DAO.Recordset
   
   
    Set rs = CurrentDb.OpenRecordset("Table1")
   
    Set objXL = CreateObject("Excel.Application")
    Set objWB = objXL.WorkBooks.Add
   
    objWB.Sheets("Sheet1").Cells(1, 1).CopyFromRecordset rs
   
    objXL.Visible = True
End Sub



if that works, then u can just do this to save the file



Public Sub CreateExcelWB()

    Dim objXL As Object
    Dim objWB As Object
    Dim rs As DAO.Recordset
   
   
    Set rs = CurrentDb.OpenRecordset("Table1")
   
    Set objXL = CreateObject("Excel.Application")
    Set objWB = objXL.WorkBooks.Add
   
    objWB.Sheets("Sheet1").Cells(1, 1).CopyFromRecordset rs
   
    objWB.SaveAs "Fred.xls"
    objWB.Close
    objXL.Quit

End Sub

0
 
LVL 4

Author Comment

by:Carl2002
ID: 16999694
All

Seems to work with the exception of it loses the header row, if I can get it to include the header I'll be well away.
0
 
LVL 4

Author Comment

by:Carl2002
ID: 16999745
Also the file I'm outputting to already exists, therefore I would want to overwrite the existing, is there a way of automating the click of the yes button ?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16999790
ok, because Im doing a CopyFromRecordset, it copies the data

Drawback with Excel automation is formatting

You will have to export the column headings yourself

u could try this



Public Sub CreateExcelWB()

    Dim objXL As Object
    Dim objWB As Object
    Dim rs As DAO.Recordset
    Dim i as integer    
   
    Set rs = CurrentDb.OpenRecordset("Table1")
   
    Set objXL = CreateObject("Excel.Application")
    Set objWB = objXL.WorkBooks.Add

    'create header row    
    For i = 0 To rs.Fields.Count - 1
        objWB.Sheets("Sheet1").Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i

    objWB.Sheets("Sheet1").Cells(2, 1).CopyFromRecordset rs
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 400 total points
ID: 16999798
regarding saveas, I had a look but couldnt find the force overwrite

so as a temp thing, u could try this

sSaveFile = "C:\Fred.xls"


if dir$(sSaveFile) <> "" then kill sSaveFile
objWB.SaveAs sSaveFile


0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question