export a subform to excel

[starfailure]

is there an easy way to use a button that simply opens the contents of a subform as an excel sheet?
i have a ton of forms and would like to do the same for each.

thanks

 

[audrey]

You can create a macro using the DoCmd.OutputTo and put this on a button's On_Click. Choose your subform under the Object Name, then type of file exl. Macros make life so much easier

 

[starfailure]

thanks audrey. that's what i was looking for.

 

[starfailure]

Actually, one more thing. how would I use the same macro for every button. can I make a variable to reference the loaded subform? or do i just have to make a macro for each subform/button?

 

[audrey]

This might be the long way, if anybody else has suggestions, i won't get my feelings hurt

: (1) For a different button for each form to send to excel: I would create one macro. Make sure you have the "Macro Names" icon clicked on the tool bar. Name your macros like Form1 then put in the arguments for that form then For your second form, in the macro names colomn, put form2 then enter your arguments. This is for if you have a single button for each form that you want to send to excel. Then in the On_Click Property for your button you would select Macroname.Form1 For the 2nd form and second button you would select macroname.form2.

(2)If you have one button and you want this one button to send all of your forms to excel... just don't enter anything in the macronames, but have all your actions for your different forms. If this sounds confusing let me know and i'll send you a screen shot of what your macros would look like.

 

[archiecone]

I would create the macro then convert it to a module. Make the module to where you pass it the form name....

Sub MacroName(formname as string)


end sub

 

[starfailure]

the OutputTo works fine, but it seems to require that you save the output before it even displays the spreadsheet. Can this be bypassed? What if I don't want to save it until I look at it? What if I don't want to save it at all?

 

[audrey]

are you wanting to preview it (before it's saved) in access or excel?

 

[Fornatian]

outputting requires a destination because Excel opens the outputted file.

I suggest using a standard filename which can be overwritten each time. Is this an academic question or is filespace at a premium?

 

[starfailure]

more of an academic question, i guess. and i wanted to preview the file in excel so it could be formatted and manipulated before it's saved. it's just kinda annoying to have to save "previews" if you know what i mean.