Solved

Outputting a table to XLS Format

Posted on 2006-06-28
8
619 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
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 …

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now