Solved

Outputting a table to XLS Format

Posted on 2006-06-28
8
657 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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 …

730 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