By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Export to Excel - Choose Fields

P: 5
Hi There,

I am hoping to be able to export a table from Access to Excel, however I would like to be able to select the fields that I want to export...

For example, my Table has 23 fields, but not all of them are required to be exported to Excel. I want to be able to create a form to select the fields required to be exported, and then on this form, click a command button to export only those fields selected.

Is this possible? And if so, can you please give me some direction on how to go about this?

Much appreciated.
Sep 12 '07 #1
Share this Question
Share on Google+
2 Replies


P: 68
for example: create a form showing the underlying data. next to each field place an unbound textbox and enter the associated fieldname as the dwefault value, and make the visible property false. next to this place an unbound checkbox and call each one e.g. chxMyFieldName (where MyFieldName is the name of the associated field). add a command button called cmdExport to trigger the export and set it's on click event as follows;

Private Sub cmdExport_Click()
dim MySQL as String
dim MySQLSelect as String
dim MySQLFrom as String
Dim qdfNew As QueryDef
Dim MyDB As Database

Set MyDB = CurrentDb

MySQLSelect = "Select "
MySQLFrom = "FROM ([tableorqueryname] "

'then for each field, run an IF routine to add it to the SQL statement if checked
If me.[fieldcheckboxname1] = -1 then
MySQL = MySQLSelect & [me.txtfieldname1] & MySQLFrom
end if

If me.[fieldcheckboxname2] = -1 then
MySQL = MySQLSelect & [me.txtfieldname2] & MySQLFrom
end if

'..and so on for each field

'then create a new query based on this sql
If acbDoesObjExist("ExportQuery", acQuery) Then
DoCmd.DeleteObject acQuery, "ExportQuery"
End If
With MyDB
Set qdfNew = .CreateQueryDef("ExportQuery", MySQL)
End With

'then export the newly created query to excel
DoCmd.OutputTo acOutputQuery, "ExportQuery", acFormatXLS, "[filename].xls", True

end sub

There will be a better way of evaluating whether to include each field within the sql statement by evaluating the status of each checkbox control, something like 'for each checkbox control in the form, if checked then mysql =... etc' and you might find it worthwhile searching the forums for that method, but I don't remember it off the top of my head I'm afraid.

HTH
Sep 12 '07 #2

P: 5
for example: create a form showing the underlying data. next to each field place an unbound textbox and enter the associated fieldname as the dwefault value, and make the visible property false. next to this place an unbound checkbox and call each one e.g. chxMyFieldName (where MyFieldName is the name of the associated field). add a command button called cmdExport to trigger the export and set it's on click event as follows;

Private Sub cmdExport_Click()
dim MySQL as String
dim MySQLSelect as String
dim MySQLFrom as String
Dim qdfNew As QueryDef
Dim MyDB As Database

Set MyDB = CurrentDb

MySQLSelect = "Select "
MySQLFrom = "FROM ([tableorqueryname] "

'then for each field, run an IF routine to add it to the SQL statement if checked
If me.[fieldcheckboxname1] = -1 then
MySQL = MySQLSelect & [me.txtfieldname1] & MySQLFrom
end if

If me.[fieldcheckboxname2] = -1 then
MySQL = MySQLSelect & [me.txtfieldname2] & MySQLFrom
end if

'..and so on for each field

'then create a new query based on this sql
If acbDoesObjExist("ExportQuery", acQuery) Then
DoCmd.DeleteObject acQuery, "ExportQuery"
End If
With MyDB
Set qdfNew = .CreateQueryDef("ExportQuery", MySQL)
End With

'then export the newly created query to excel
DoCmd.OutputTo acOutputQuery, "ExportQuery", acFormatXLS, "[filename].xls", True

end sub

There will be a better way of evaluating whether to include each field within the sql statement by evaluating the status of each checkbox control, something like 'for each checkbox control in the form, if checked then mysql =... etc' and you might find it worthwhile searching the forums for that method, but I don't remember it off the top of my head I'm afraid.

HTH
Thanks for the help. Have copied your code above using my own parameters, but I am having problems with:

If acbDoesObjExist("ExportQuery", acQuery) Then
DoCmd.DeleteObject acQuery, "ExportQuery"
End If

Error message comes up "Compile Error: Sub or Function not defined" with acbDoesObjExist highlighted. What does this mean?
Sep 14 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.