Solved: Output a single record/form as Excel File?

[andysuth]

Hi,

Does anyone know how to output a single Access Record or view of a form as an Excel format file?

I don't want the whole database, I just want one where people can scroll through the records and highlight the record that is appropriate and click a macro button for "Save as Excel"

Pref. Excel 2003 or 97 or compatible.

I get the Highlight current record/"Export/XLS/Selection", but how do I do that automatically?

Thanks.

-Andy

[andysuth]

I'd just love one or two commands for doing this.

Any clues?

If it were excel, I'd record a macro doing it and butcher the code, but Access doesn't give you the option of recording the macros. Why are MS so inconsistant in features across the Office range?

Cheers,

-Andy

[OBP]

To output the Current record you can just create a Query that uses the Form as a Reference in it's Criteria Row. Once you have estbalished that it does what you want you can then Create the VBA code to "Transfer Spreadsheet" naming the Query as the data source to be "Transferred".
You can also do it all in VBA using the Current record on the open Form, even to specifying which Cells on the worksheet you want the data to go in to.
If you have a look at the Knowledge Database (KB) you can Search for "Access to Excel". As you can also do on this forum.

[andysuth]

I hate to be dumb, but I can only add the table or another query to the relations board.

Am I doing something wrong?

Thanks,

-Andy.

[OBP]

Andy, you do not use the Relationships, you use the Query's Criteria Row.
You enter this in the Criteria row of the Field (ID perhaps) that specifies the record.
forms![FormName]![FieldName]
where FormName is the name of your open form and FieldName is the Field that specifies the record.
It can be the Record ID field, a person's name etc.

[andysuth]

Cheers,

I'll give that a go. Currently I'm using a line I worked out yesterday that just highlights the record page before entering the macro, so the macro looks like this:

[vba]
Private Sub Command26_Click()
On Error GoTo Err_Command26_Click
'DoCmd.SelectObject acForm, "fulldetails", True
'acForm , "fulldetails", True
RunCommand acCmdSelectRecord
DoCmd.OutputTo acForm, "fulldetails", "MicrosoftExcelBiff8(*.xls)", "c:\harn\outer.xls", False, "", 0
Exit_Command26_Click:
Exit Sub
Err_Command26_Click:
MsgBox Err.Description
Resume Exit_Command26_Click

End Sub
[/vba]

Thanks for your input.

-Andy.