How do I use DoCmd.TransferSpreadsheet acExport

[morerockin]

Hello All,

I am trying to export my queries and tables from Access into one workbook on multiple tabs/worksheets within that workbook.

I have researched how to do this and the common theme is doing the DoCmd.TransferSpreadsheet acExport. However, I have no clue how to start doing this.

Do I go into the each of the tables/queries and create a macro with DoCmd.TransferSpreadsheet acExport to do this?

I am looking for baby steps here I guess.

Thanks

[ItsMe]

I use forms for users to interact with. I also use VBA to do 'things' in response to Events created by the user. So, for instance, I might have a button on a form that when clicked, executes some code to export data.
Here is an example of code that would export data
https://www.accessforums.net/program...cel-54133.html

You can create an Event Handler while in Design View of a form. After you add a Command Button to a form you can go to the Command Button's properties via the Properties Sheet. Within the Properties Sheet is a tab named Events. Under the Events tab is an event named On Click. there is an ellipses(...) to the right of each event. Clicking the ellipses and selecting code builder will open the VBA editor and place your cursor where your code belongs. The cursur will be in the middle of your new Click Event procedure.

[morerockin]

I use forms for users to interact with. I also use VBA to do 'things' in response to Events created by the user. So, for instance, I might have a button on a form that when clicked, executes some code to export data.
Here is an example of code that would export data
https://www.accessforums.net/program...cel-54133.html

You can create an Event Handler while in Design View of a form. After you add a Command Button to a form you can go to the Command Button's properties via the Properties Sheet. Within the Properties Sheet is a tab named Events. Under the Events tab is an event named On Click. there is an ellipses(...) to the right of each event. Clicking the ellipses and selecting code builder will open the VBA editor and place your cursor where your code belongs. The cursur will be in the middle of your new Click Event procedure.

Thank you ItsMe.

I don't mean to share info from another website, but I saw that someone else says to use forms.

http://www.access-programmers.co.uk/...d.php?t=250397

Forms confuse me. I want to export tables and queries, but I have to create a form of that query/or table in order to export it using "DoCmd.TransferSpreadsheet acExport"?

Are these the correct steps:

• Select Query/Table you wish to export
• Create Tab ->Form->Design View
• Install a button
• Click Property Sheet
• On Click-> ...
• Choose Code Builder
• Enter the code

I am not sure why my test is now bold! My apologies!

[ItsMe]

If you plan on avoiding forms, I would suggest looking into the Export Wizard. I do not have a lot of experience using the Wizard to export to Excel, but it is there and I believe you will be able to save different export processes.

Check the External Data tab in the Ribbon for the "Excel" option. It should get you going. Oh and the best thing to do is select the object that will be considered by the Wizard before launching the Wizard. For instance, click on the Query from within the Navigation Pane and then the Excel option under the External Data group.

[morerockin]

Can I use Forms to export my tables and queries into one workbook? I am not opposed to using Forms, I just don't know what forms are. I want to use the most efficient method possible, but have to learn the steps in order for me to get there.

Was my previous post in regards to the steps not accurate at all?

[morerockin]

Is there a way to pay for help here?

[ItsMe]

I had to take care of some things so I was not available to respond earlier. If you want to create a form, I would recommend doing it via Design View. So, yes, your list that you were able to bold is correct. The exception would be selecting a query object or table prior to creating a new form. Selecting a query is not necessary.

As for paying for help here. The simple answer is no. This is a, free, help forum. It is up to you to learn the correct questions to ask of the community. As more of your questions get answered, you will continue to learn. Having said that, if you want to hire someone to do work for you, you might find someone to contract with. If you are trying to learn how to do stuff and be self sufficient, I suggest you participate in this forum and teach yourself how.

If you have a huge project and a deadline to meet, it may be in your best interest to hire a legit company to build something for you. But I would still recommend participating here. I am rather busy now. I usually am busy. If time is of the essence and you need something managed for you, I may be able to help. You can shoot me a PM if you wish. There may be others here willing to contract with you. I am not sure. But understand this is not a pay for help website.