Solved: Hidden form fields, spit out XLS file

[andysuth]

Hi,

I'm modding a bit of code I wrote previously for a more simple form.

The original form kept all options on screen at the same time, but the new form has a pull down menu which changed what appeared on the form.

The issue is this XLS file isn't used in Excel, it's read into SolidEdge 2D - the free 2D cad program, and alters the drawings and then save the new drawing.

For this reason I'd always assumed that what was output was the record output, but it seems to be the form data. Hence when the fields are hidden, they don't appear on the XLS output.

This *can* be worked around, but for simplicities sake I'd prefer to have EVERY field of the record output. Can anyone seee a quick way of doing this?

I'd prefer not to monkey it up with a "show all", output XLS, then "re-hide the hidden" style sequence.

here is the two lines I currently use:



RunCommand acCmdSelectRecord
DoCmd.OutputTo acForm, "PanelOrderForm", "MicrosoftExcelBiff8(*.xls)", pathstring & "outfile.xls", False, "", 0



Thanks,

-Andy.

[CreganTur]

Instead of using "RunCommand acCmdSelectRecord" you can create a Query that pulls all of the fields you want. In the Criteria section you can reference the textbox on your Form that contains the primary key of the record you want to export using syntax similar to: Forms![FormName]![ControlName] (use the Build button to drill down to the desired form control if you need to).

Then you can use the TransferSpreadsheet method to export an excel spreadsheet that is based off of the query.

HTH:thumb

[andysuth]

I see what you mean now. I think last time I was new to Access so went as Macro-based as possible.

I have now had some success with the OutputTo acOutputQuery method.

code I used (inside temporary button macro is as follows:

Thanks for your help.

-Andy.


Private Sub TEMPTEST_Click()

RunCommand acCmdSelectRecord
DoCmd.OutputTo acOutputQuery, "OrderPanelQ Query", "MicrosoftExcelBiff8(*.xls)", "C:\st\outftemp.xls", False, "", 0
End Sub