[RESOLVED] Set Default Output (Access 97)

[MethadoneBoy]

Hi all,

I have a feeling that I know the answer to this already, but I'd like to confirm -

Currently, when outputting tables to Excel, my version of Access outputs to Excel 5.0. This means I receive an error every time I try to output a table with over 16k rows. Is there anyway to set the database so that it default outputs to 97/2000 format?

Thanks

[si_the_geek]

How are you currently doing it?

[MethadoneBoy]

To export, I merely click the "Office Links -> Analyze it with MS Excel" icon on the toolbar. Presumably because it's Access 97 that I'm using, it's automatically set to output to the 97 Excel format?

[si_the_geek]

Yep, when writing Access 97 they obviously didn't know about the file format of future versions of Excel.

As you have Access 97 I presume you only have Excel 97 too, is this the case? If so, you cant use Excel Automation to help.

You could theoretically use ADO, but I'm not sure how you would create the document to add the data to.

Another option would be to export to a CSV file, which Excel will happily open.

[MethadoneBoy]

Well, actually we've got Excel 2003 here in the company. It's just Access that's a leftover from the Office 97 days.

We were initally outputting into a csv file, but were just wondering if there was another way around it.

Thanks for the help, all the same!

[RobDog888]

If you add a reference to Excel in your access db VBA IDE you can write some code to export to Excel. You can also use late binding so it can support various versions of Excel.

There is a .CopyFromRecordset method that you can use with the EOM to perform the export quite simply.

Option Explicit
'Add reference to Excel xx.0 Object Model
'Add reference to MS ActiveX Data Object 2.x Library
Private Sub Export2Excel()
    Dim oApp As Excel.Application
    Dim ws As Excel.Worksheet
    Dim iCol As Integer
    Dim rs As ADODB.Recordset
    
    'Create rs object
    'Blah, blah, blah...
    
    Set oApp = New Excel.Application
    Set ws = oApp.Sheets("Sheet1")
    For iCols = 0 To rs.Fields.Count - 1
        ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
    Next
    ws.Range(ws.Cells(1, 1), ws.Cells(1, rs.Fields.Count)).Font.Bold = True
    ws.Range("A2").CopyFromRecordset rs
    rs.Close
    Set rs = Nothing
    
End Sub

[MethadoneBoy]

Thanks, RobDog!

EDIT - We've got around the issue by reverting the process so we can just send a CSV file. Thanks to anyone who contributed.