Excluding fields to export (1 Viewer)

Access_Help

Registered User.
Local time
Today, 02:55
Joined
Feb 12, 2005
Messages
136
Hi,

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

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

June7

AWF VIP
Local time
Today, 01:55
Joined
Mar 9, 2014
Messages
5,423
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

Forum Troll
Local time
Today, 04:55
Joined
Oct 6, 2019
Messages
2,884
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.
 
Last edited:

Access_Help

Registered User.
Local time
Today, 02:55
Joined
Feb 12, 2005
Messages
136
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

Forum Troll
Local time
Today, 04:55
Joined
Oct 6, 2019
Messages
2,884
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

MVP / VIP
Local time
Today, 09:55
Joined
Jan 14, 2017
Messages
18,186
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

I’m here to help
Staff member
Local time
Today, 02:55
Joined
Oct 29, 2018
Messages
21,357
Hi,

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

Code:
DoCmd.OutputTo acOutputForm, "Frm_Reports", acFormatXLSX, "test123.xlsx", True
Hi. Here's how I would modify that code to accomplish your requirement:
Code:
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.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 09:55
Joined
Jan 14, 2017
Messages
18,186
Surely that should be:
Code:
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...
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12xml, "QueryName", "ExcelFileName&FullPath", True

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

theDBguy

I’m here to help
Staff member
Local time
Today, 02:55
Joined
Oct 29, 2018
Messages
21,357
Hi Colin. Thanks. You're correct. I forgot to change that part too. Cheers!
 
Last edited:

Mark_

Longboard on the internet
Local time
Today, 02:55
Joined
Sep 12, 2017
Messages
2,111
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.
 

Users who are viewing this thread

Top Bottom