[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Outputting a table to XLS Format

Posted on 2006-06-28
8
Medium Priority
?
676 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 400 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

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

Technology Partners: 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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

656 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