Exporting selected records to excel - hiding certain columns (1 Viewer)

[janeyg]

Hi

I have a form set to datasheet view. When records are filtered, I have a macro set up to export this selection to an excel spreadsheet. This works fine, however I have 2 calulated fields which appear in the export and I been asked if these can be hidden or deleted from the export? I have search on how to hide or delete these from the export but I just cannot find out how to do this or even if it is possible? I also set up a macro set up to export from a query to excel, however I do not know how to get this to export the selected records or hide/delete these columns in the export.

Can anyone help with a solution to this - I just can't seem to crack it.
thanks
Janeyg

 

[nschroeder]

Create a second query that uses the first one as the datasource but does not include the calculated fields, and use the second query for your export.

 

[janeyg]

The export to excel runs from the form, with the filtered records selected. This exports to excel - which works fine but as the calculatations are in the form, and I export from the form - the excel output includes the calculations. I dont know how to exclude those columns from apprearing. If I run the export from a query based on the form, I then don't know how to specify to export the filtered records selected in the form?

Does that make sense? Really, I just want to be able to export from a form, the filtered records selected but not includes certain columns, like the calulation fields and possibly a notes field.

Is this possible?

thanks
Janeyg

 

[nschroeder]

Sorry Janeyg. I misread your 1st post.

When I export a form, it only exports visible fields, so my suggestion is to have your code set the Visible property to No on the fields you don't want, then do the export, then set them back to Yes.

 

[janeyg]

Yes, but I will not be actioning the exports, this is a database I have set up for someone else. So I want to set up a command button that when clicked exports and opens in excel from the filtered selected on a form with those columns removed.

It's a tricky one. They could always delete the columns in excel but wanted to make this automated for them.

thank you for advice.
Janeyg

 

[nschroeder]

I'm not sure what you mean by actioning the exports, but coding your command button to do what I suggested should work.

 

[janeyg]

I mean, I have created the database for someone else, I will not be using the database so I have added an export button on the form which the user will click to export the selected records from the form.

I dont know how to set the query up to export just the selected records that have been filtered on the form. When I use a query, it exports all the records and not just those selected, though it does remove the required columns, which is why I ran the export from the form - only trouble is, it shows the calulated fields from the form on the excel export. My colleagues don't want these to show. They could delete them in excel but I was trying to cover this off in the export macro.

I wont be able to go in to the database form and make visible and unvisible before running the export as I am not working on it so have setthe macro is set to do this for them.

Hope this makes sense. I just know there must be a way to do this - I just don't know how.
thanks
Janeyg

 

[nschroeder]

Ok, take a deep breath and read with comprehension :banghead:

In the code for the button you are putting on the form for the users to click, have it hide the calculated fields you don't want to include, then have it do the export, then have it make the fields visible again. There are no queries involved, and no intervention required by you.

I see no reason why this wouldn't be exactly what you need. Good luck!

 

[janeyg]

It is currently set as a macro, not a code. I don't know how to do it. I am new to code. I have been searching for a code to modify but its all new to me.

 

[nschroeder]

Ok, now I'M reading with comprehension!

Open the form in design mode, right-click the command button that will do the export, and select Properties. In the Properties window, select the All tab. Note the button name. You may want to change it to something like cbExportToXLS. Also, you'll need to know the names of the fields you want to exclude from the export.

With the button selected, click the Event tab. In the On Click event, which should contain the name of your macro, select [Event Procedure]. Then click on the elipses (...) button to the right to bring up the MS Visual Basic code window, which will included the empty subroutine you just created. Modify to look something like this:

Private Sub cbExportToXLS_Click()
[INDENT]DoCmd.Echo (False)
me.field1.visible = false
me.field2.visible = false
DoCmd.OutputTo acOutputForm, Me.Name, acFormatXLS, "<pathname>.<filename>.xls", True
me.field1.visible = true
me.field2.visible = true
DoCmd.Echo (False)[/INDENT]
End Sub

The Echo command prevents the hide/show of the fields from being seen.
The True parameter of the OutputTo command causes the workbook to be automatically opened after it is created.

 

[janeyg]

I followed your instructions and the code for my fields is as follows. It exports and opens the file but it still does not exclude the columns and it locks up the database and I have to force close it and reopen to use the database? Not sure why this is or why it does not work?

Private Sub cbExportToXLS_Click()

DoCmd.Echo (False)
Me.Label750.Visible = True
Me.Label718.Visible = True
Me.Label269.Visible = True
Me.Label695.Visible = True
Me.Label696.Visible = True
Me.Label697.Visible = True
DoCmd.OutputTo acOutputForm, Me.Name, acFormatXLS, "U:\BOB\RenewCancelList.xls", True
Me.Label750.Visible = True
Me.Label718.Visible = True
Me.Label269.Visible = True
Me.Label695.Visible = True
Me.Label696.Visible = True
Me.Label697.Visible = True
DoCmd.Echo (False)
End Sub



I really appreciate your help on this. I don't see what I have done wrong?

 

[ganal]

Hallo,
Thought I would say Hi and I have a problem in my database ... I need to convert formic to Excel and search on Google and found part of the solution to the problem in your site
But I almost need to show a large number of lines (ROWS) 50,000-row when run command message appears: -

There are too many rows to output, based on the limitation specified by the output format r by as-store.]
I hope to help solve the problem
Thank you for all

Cheers,
Ganal