Excluding fields to export

[Access_Help]

Hi,

Can anyone help me modify this VBA to exclude or only include certain fields from a form for an excel export:

DoCmd.OutputTo acOutputForm, "Frm_Reports", acFormatXLSX, "test123.xlsx", True

 

[June7]

There is no way to modify that code to accomplish that requirement. You need to export a query or report that has only desired data.

 

[vba_php]

There is no way to modify that code to accomplish that requirement. You need to export a query or report that has only desired data.

not only that, but I can't remember a single time in 12 years of doing this that i actually exported a form object to excel. why would this ever be needed anyway? excel is meant to work with data directly, similar to a scenario in access whereby you would give the user access to edit the table instead of working through forms.

 

[Access_Help]

My form only displays the relevant fields for the user.

The query has many calculated fields which are not needed by the user.

I want the user to export these visible fields to an excel document which they can import into their own system.

Currently, the excel document displays all the query fields.

 

[vba_php]

My form only displays the relevant fields for the user.

The query has many calculated fields which are not needed by the user.

I want the user to export these visible fields to an excel document which they can import into their own system.

Currently, the excel document displays all the query fields.

why can't you just export the query dataset and choose what fields you want in there? isn't there an option in access, either thru the interface or vba that allows u to do this? I would think that you could find this somewhere! if it isn't anywhere to be found, you can always write a user defined function to do it, as a last ditch effort.

 

[isladogs]

Your form may only display the required fields but if there are additional 'unused' fields in the form record source these will be included using the OutputTo method. You could edit the record source to remove those fields if you want

However, as already stated, its easier to just create a query with the required fields then use the wizard (or code) to export the query to Excel. Or you can right click the query in the navigation pane and export from there.

 

[theDBguy]

Hi,

Can anyone help me modify this VBA to exclude or only include certain fields from a form for an excel export:

DoCmd.OutputTo acOutputForm, "Frm_Reports", acFormatXLSX, "test123.xlsx", True

Hi. Here's how I would modify that code to accomplish your requirement:

DoCmd.OutputTo acOutput[B]Query[/B], [B]"QueryName"[/B], acFormatXLSX, "test123.xlsx", True

So, as everyone already said, the only way to eliminate the unwanted fields from the Excel file is to create a query without them and export it instead of the form.

 

[isladogs]

Surely that should be:

DoCmd.OutputTo [B][COLOR="DarkRed"]acOutputQuery[/COLOR][/B], "QueryName", acFormatXLSX, "test123.xlsx", True

Should that be the full path or does it default to the current folder if not specified - I can't remember offhand

But I would use this...

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12xml, "QueryName", "ExcelFileName&FullPath", True

Or just save an Export task so it can be reused at any time

 

[theDBguy]

Hi Colin. Thanks. You're correct. I forgot to change that part too. Cheers!

 

[Mark_]

Something important to note; if you are doing filtering or sorting in your FORM to make sure that only the proper records are exported, you will need to make sure your source query has the same filters/sorts. This could be something as easy as building up your WHERE clause and putting it into a hidden control on the form, then referencing said control in your query. This has the added advantage of letting you double check (by un-hiding the control) exactly what you are exporting.