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

access search form - export results only to excel file

P: 1

i am new to this website. But i was looking for answers to solve a problem which I have with access.

would you be able to help me please?

I have a query named "qry_BO_NA_Search".
I also have a form which it's job is to search by different criteria from the query.

In the form, I want a button to export the search results only according to my preferred name of excel file too.

What VBA can i input linked to the button.
Is it possible please?
4 Weeks Ago #1
Share this Question
Share on Google+
1 Reply

Expert Mod 5K+
P: 5,397
welcome to!

.. for a very simple means of exporting the data ..
- As you already have the query made convert it to use the tempvars collection.
Expand|Select|Wrap|Line Numbers
  1. SELECT aa.test,
  2. FROM aa
  3. WHERE ((([TempVars]![myFind]));
- From your form's code either check for the [tempvars]![foo] value and/or set/clear as needed.
- Use the values from your form for the file path and name
- Then you can use the docmd.transfer method in VBA
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet _
  2.     Transfertype:=acExport, _
  3.     spreadsheettype:=acSpreadsheetTypeExcel12Xml, _
  4.     TableName:="yourQueryHere", _
  5.     Filename:="yourFileAndPathHere", _
  6.     Hasfieldnames:=True
I would actually use string variables for the tablename and filename parameters and build them outside of the function - makes troubleshooting malformed strings a lot easier!

It will transfer the data in a very vanilla fashion to a new excel file.

If you need something fancier then we'll need to know your skill level with Access and VBA.
4 Weeks Ago #2

Post your reply

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