Hide fields before exporting form to excel

[iamthebestuk]

Hi,

I've trawled the site to see if I could find an answer to this - but no luck - so here goes ...

I currently have a button on my form that exports the results to excel, letting the user choose the location and name of the file. Code is below.

DoCmd.OutputTo acForm, "frmCriteriaResults", "MicrosoftExcel(*.xls)", "", True, ""

What I need to do is hide (not delete) some of the fields shown on the form when it is displayed/stored in excel .

Remember I don't know what the user will name the file or it's possible location.

Help needed.
Much appreciated.

 

[lupins46]

Create a query containing the fields you want and export from that instead of the form.

 

[iamthebestuk]

This won't hide the fields when exported to excel - it will simply omit them. I want the users to be able to view the additional fields if they ever wanted to.

 

[dhulbert]

You will have to use VB code.

Get the file name from the user

fileSaveName = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")

use that in your output file name. then use it again to open the Excel spreadsheet, hide the colums and then save and close the sheet agin.



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[PHV]

Have a look at the GetObject function to grab the running instance of Excel and then play with OLE Automation.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[iamthebestuk]

I tried out the method recommended by dhulbert, but I get the error message "Compile Error: Method or data member not found".

Able to help?

 

[PHV]

GetSaveAsFilename is a method of the Excel.Application object.
With recent version of Access you may play with the Application.FileDialog object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GummowN]

Why not force the file to save as a certain filename - open it using the excel object, hide teh columns, save it again and then leave it open for the user.

You could even have cell A1 contain the filepath of the file so the user knows where the file is.

99% of my exports are generated into fixed paths. Because of the quantity I use date based folder names - eg \Export\2005\06\07\Daily_PnL.xls or \Export\2005\06\07\Daily_Rates.xls

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.