0

I have a form which has a combobox and a button. When a value is selected in it, I have to retrieve the value selected in combo box and based on that value run a query. The query produces a table of 5 columns. I want this table to be exported to Excel Sheet. Please help me out.

user1804254
  • 59
  • 4
  • 5
  • 13

2 Answers2

2

As Tom said, you can use the Docmd.TransferSpreadsheet command. First you need to have your query set to be a parameterized query that references your combobox. I'm assuming it's part of your WHERE clause. So, your query may look like:

 SELECT * FROM yourTable WHERE values = [Forms]![yourForm]![yourComboBox];

Assuming your query you just wrote is named "qryExport", You'll put this code into a button on your form on the on click event:

 DoCmd.TransferSpreadsheet acExport, , "qryExport", "C:\yourPath\exportedReport.xlsm", True

You should then be able to go in the path you specified and open that excel file and make sure everything worked.

Hope this helps.

Scotch
  • 3,186
  • 11
  • 35
  • 50
  • Thank you. Where should this query be written by changing it in the way you said. I was doing it by having a query in string and feeding it to OpenRecordSet in VBA. Please help. – user1804254 Dec 08 '12 at 00:21
  • Just create a query, go to SQL view, and type that query in. Save the query as whatever name you reference on your form, and you should be good to go. – Scotch Dec 08 '12 at 21:19
  • Dear Scotch...Thank You. It worked. Is there a way in which I can format the contents of the Excel to make it look better. And I want an Excel to be created opened up without a name. I want to leave it to the user to choose where and name it. How can I do it? – user1804254 Dec 09 '12 at 01:35
  • I'm not sure how to have it automatically format nicely in Excel, I've had that same problem. There is a way to use an existing formatted Excel sheet, but I'm not too sure. if you want the user to be able to name it from access, create a text box control "txtExportName", and in your vb code, I think you could do docmd.TransferSpreadsheet acExport, , "qryExport", "C:\yourPath\" & [Forms]![yourForm]![txtExportName] & ".xlsm", True – Scotch Dec 11 '12 at 17:21
1

OK, simple enough. You'll have to use VBA.

On the click event of the button, you'll just have to execute a DoCmd.TransferSpreadsheet command. There's several options so you can just look it up here.

For the table name, use a query instead that includes a reference to your ComboBox. I.e. =[Forms]![FormName]![ControlName]

Tom Collins
  • 4,069
  • 2
  • 20
  • 36