Export Visible Fields only to Excel

[Jenaccess]

Hi,


I have a query tool that allows users to create their own custom queries. Basically, it's a form that allows the user to check boxes for the fields they want to see. The code behind it simply hides the fields in the query for which the user has not checked the box. That works very well.

My problem is I would like to have a command button that will export the query to excel. The OutputTo and TransferSpreadsheet commands will just export the whole query into excel regardless of whether or not the field is checked. I'm looking for a way to only export the columns the user has checked. Is this possible, and if not is there a workaround that would do something similar?

Thank you for your help!
Jen
Access Version: 2010 (though can work with 2007)
Skill Level: Advanced Beginner

 

[JHB]

A workaround could be to create a query on the fly, (using VBA code), only containing the required fields the user had selected.

 

[mh123]

The easiest way to do it in my mind is how JHB suggests, or you could specify which columns to pull from the query to excel based on which ones are hidden but that sounds like much more work than is needed.

Do you currently have a sample of what you're working with (the code)?

 

[Jenaccess]

A workaround could be to create a query on the fly, (using VBA code), only containing the required fields the user had selected.

Thank you so much for taking the time to respond. I like the idea of creating a query on the fly. I think that's a better way to go than simply hiding the fields. What I still a little confused about is how to get that done. My original idea was to have a couple of hidden text boxes on my selection form, and just concatenate each of the fields chosen, then somehow pass that to my strSQL query string. Do you think that would work? Or is there a better way I'm not thinking of?

 

[Jenaccess]

Thank you mh for replying as well. I think JHB's suggestion is the best one as well. I'm just trying to figure out the best way to do it. In the past I didn't care much about efficiency or the best way to design things. In one instance I wrote 18 different stored queries to grab max values when one crosstab would have done the job. So I'm really focused on trying to get better.

Here's a sample of the code I am now using. I have a command button on my selection form that the user clicks after they've checked the boxes for the options they want. The button's name is btnNext.

Private Sub btnNext_Click()


Dim stDocName, stDocName1 As String
stDocName = "qryCustomFields"
stDocName1 = "frmCustomFields"

DoCmd.OpenForm stDocName1, acFormDS

If Me.chkID = False Then
Forms!frmCustomFields.ID.ColumnHidden = True
End If

If Me.chkLastName = False Then
Forms!frmCustomFields.[Last Name].ColumnHidden = True
End If

If Me.chkFirstName = False Then
Forms!frmCustomFields.[First Name].ColumnHidden = True
End If

…And so on. I have 26 different fields, but the syntax is exactly the same as demonstrated above

End Sub

 

[JHB]

It could be something like below, (not tested):

Dim stDocName, stDocName1 As String
stDocName = "qryCustomFields"
stDocName1 = "frmCustomFields"

DoCmd.OpenForm stDocName1, acFormDS

stDocName = "Select "
If Me.chkID = False Then
Forms!frmCustomFields.ID.ColumnHidden = True
Else
  stDocName = stDocName & "ID,"
End If

If Me.chkLastName = False Then
Forms!frmCustomFields.[Last Name].ColumnHidden = True
Else
  stDocName = stDocName & "[Last Name],"
End If

If Me.chkFirstName = False Then
Forms!frmCustomFields.[First Name].ColumnHidden = True
Else
  stDocName = stDocName & "[First Name],"
End If
'Until you have been through all controls. 
stDocName = Left(stDocName, Len(stDocName) - 1) & " From YourTableName"

 

[Jenaccess]

Just wanted to come back and say thank you for the help. This strategy worked perfectly and I was able to apply it to multiple tools in my database.

 

[JHB]

You're welcome, luck with your project.