Solved

Outputting a table to XLS Format

Posted on 2006-06-28
8
661 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

695 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