MS Access 2010 export queries into one workbook but different worksheet (1 Viewer)

[yolo]

i have three Queries and i need to export three queries into one workbook but different worksheet , i google it and i cant find an answer

currently im using ExportWithFormatting , but the result came out is three different workbook .

is there anyway i can export to one workbook ?

Please help me , Thank you so much ><

 

[sxschech]

in code, you can export more than one query to the same excel file into different tabs. Depending on your version of excel, you may need to change the number at the end of this "acSpreadsheetTypeExcel9"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryOne", "c:\temp\ExcelFileName.xls", True
   
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryTwo", "c:\temp\ExcelFileName.xls", True
    
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryThree", "c:\temp\ExcelFileName.xls", True

 

[yolo]

in code, you can export more than one query to the same excel file into different tabs. Depending on your version of excel, you may need to change the number at the end of this "acSpreadsheetTypeExcel9"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryOne", "c:\temp\ExcelFileName.xls", True
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryTwo", "c:\temp\ExcelFileName.xls", True
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryThree", "c:\temp\ExcelFileName.xls", True

where should i write this code at ? i got a button , i write it behind the button ??
sorry im quite new to MS Access, btw if i wanna it to export already and open the excel workbook when the button clicked , what code should i write ?

 

[sxschech]

where should i write this code at ? i got a button , i write it behind the button ??

• Open the form in design view, click on the button and if the Properties sheet is not visible, click on the "Property Sheet" icon in the ribbon (or right click on the button and choose properties).
• In the Properties Window, Click on the Event Tab
• Click in the box that says "On Click" and then click on the button to the right that has "..." (three dots)
• Paste the code there.

to export already and open the excel workbook when the button clicked

• After the transfer spreadhseet code, add this line and change as appropriate for your location and filename

FollowHyperlink "c:\temp\ExcelFileName.xls", , True, False

Also, if you want to start with a new file each time, you can use the following to delete the file. This would be placed before the Transfer Spreadsheet code.

 Kill "c:\temp\ExcelFileName.xls"

If you need further explanation, please let me know.

 

[morerockin]

Hello sxschech,

I need clarification please.

I have followed your steps but I do not see the event tab in the property sheet. Is this because I've not created a macro? How do I create the macro? Where in the table do I create the macro?

I am sorry for these dumb questions.

I have 2 Queries and 3 tables that I need to export into one single Excel file.

Would I put this code in each table/query or find one place to put all of the code?

I am very sorry for being a newb. Any help is appreciated.

 

[morerockin]

Pardon me for speaking in newb.

But I have clicked on the table I wish to export, went to the create tab and clicked Form, and then clicked property sheet. I do now see the event tab.

I have followed your stops but after I click the 3 dots, it pops up a box that asks Choose Builder. Which one do I pick? Macro, Expression, or Code?

Next question, why are we doing this in the Form and not in the actual table or query?

 

[morerockin]

Is there a way to pay for help here?

 

[sxschech]

Hi morerockin. I don't know what the forum's policy about paying for help is.

As for the questions...

We are using a form rather than the individual table or query so that you can export the items to the same excel file.


For the builder, you would choose Event.

Also, on the form, be sure you added a button.

The code then gets pasted in between Sub and End Sub. Attached is a screen shot that may help give you the concept.

Also note that this code is "hard coded" meaning that it only will work for the tables, queries and location specified in the code. The code can be made more flexible "dynamic", but will require more skill as you will need to use a combobox and perhaps textbox and variables.