Exporting a Form As-Is into Excel

[randommetalguy]

Hey guys,

I'm having trouble exporting from Access into Excel. My database is set up like this.

Input Form
Table to store data from Input From
Output Form that Grabs some data from the table and still makes a few additional calculations in a VBA module based on data from the Input Form.


One of the advanced users who has the ability to override some of the formulas and look ups needs to make changes to the output form before converting to a PDF. She would like other users to export the Output Form as an Excel file and email it to her so that she can make these changes. Then she wants to be able to either print a PDF that looks exactly like the Output Form from Excel or Import the edited Excel File back into Access and export as a PDF that way.

So what I'm thinking is to create a new Excel Spreadsheet to look like the Access Output Form and export to XML. Is there a way to put data from txtFirstName in the output form into cell A2 of an Excel Spreadsheet?

Another solution could be to export the Output Form as an XML file, have her make changes that way and import it back into Access into tblOutputForm and make a new Form that is bound to that table that only she has access to.

What's the best way to approach this?

 

[CEH]

Sounds like it's getting complicated Just a thought for you... you do know you can use an Excel spreadsheet as if it were a table and link your Access DB to it.....

 

[Mr. B]

randommetalguy,

I am curious as to why the changes and overrides cannot be made by the power user using the Access application?

More to the point, why does the data need to go in the path that you are mapping out?

I am sure you feel that you have a valid reason, but it might help if we could get the full picture.

 

[randommetalguy]

Well in the input form the user is designing up to three products. They will manually type a sequence of specifications and through table look ups and formula calculations, the items that are missing get filled in. Then you can temporarily save the product you are working on into a table and switch to a new product. At the bottom of the form it will show the temporarily saved data that has been entered. Once you've designed a few products you ultimately will select one. So all of the data relevant to the selected product on the input form will get saved into tblSelectedProduct. The output form will use that table to populate about 40% of it's fields. The other 60% are calculated using additional formulas, calculations, and table lookups.

I have suggested that if a product is so hard to design for a basic user that the power user should be making changes from the get-go and that will cut out the basic user making mistakes, emailing and the power user editing.

Now the XML and stuff from my original post was basically me brainstorming. But I've already suggested allowing the labels and fields on the OutputForm to be locked to anyone but the power user so the Power User can make changes on the form directly way.

What if I move the output form to a PDF and use the Leban's ReportToPDF code? If the power user has Adobe professional will the power user be able to edit the PDF that gets created? That is another potential workaround for this problem. I already have Access and PDF involved and want to keep it that simple, I don't want to involve another application or file format unless it's absolutely necessary.

Glen