Exporting to excel

[lipin]

I have an application that auditors enter error information into. Well this information needs to go to another person in EXCEL that tracks this information and corrects the mistakes and enters the time and day the corrections are made on a spreadsheet.

I was wondering if there is a way to set up this file in excel and when the auditors come across a certain error it will send the record to the excel file. I know how to export a query of the records to excel. But I was hoping someone knew of a way to add these records in real time at the point the record is saved in access to the excel spreadsheet even if it is open. Is there any way to do this?

Thanks

 

[The_Doc_Man]

But I was hoping someone knew of a way to add these records in real time at the point the record is saved in access to the excel spreadsheet even if it is open. Is there any way to do this?

Probably, but it depends on the cooperation the person(s) who open Excel. The killer part is "even if it is open."

You COULD put in code underneath your form, perhaps in the BeforeUpdate or AfterUpdate events, to open Excel as an application object. (See the help files on appication objects). You would have to open the workbook file (see Excel help files in the section on Visual Basic programming of Excel functions, for info on opening a workbook file.)

You would then traverse the collection (see Access help files on collections) of worksheets (see Excel help files).

You would then have to open the worksheet's collection of Rows (see Excel help files) to find the row you want. Then you would have to perhaps open the Row's collection of Cells (see Excel help files.)

If you do this, make DARNED sure you close whatever you open.

If you cannot open the workbook in the first place because it was opened in a non-shared manner, though.

Access is meant to be multi-user, so you can implement workgroup security and allow sharing. But I'm not going to swear that Excel sharing is quite as comprehensive. Files can be located in a shared directory, sure, and there is a help topic for setting up a shared workbook. However, there is a question in my mind about using apps other than Excel to do the sharing.