TransferSpreadsheet from Excel to Access or Vice-versa

[Stacy Rueda]

Hi All, I hope someone can help me..

My problem is, I have Report in MS Access with Main form and subform on which subform linked to main form. How can I export to excel the main and subform altogether, because as of now I can't export both. I have found a code below on the net but I don't know how to execute in Access, or maybe someone knows how to directly get/linked data from excel to Access. Thank you, please help me.. My boss keeps asking me for this report. Please..

[COLOR=#660066][FONT=inherit]DoCmd[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]TransferSpreadsheet[/FONT][/COLOR][COLOR=#000000][FONT=inherit] acExport[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] acSpreadsheetTypeExcel12[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Table1 Query"[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#008800][FONT=inherit]"C:\Book1.xlsx"[/FONT][/COLOR]

 

[ranman256]

you can either export them to different sheets:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12,"Table1 Query","C:\Book1.xlsx", "sheet1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12,"Table2 Query","C:\Book1.xlsx", "sheet2"

or make a query, that joins both tables into a single dataset, then export that:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12,"qsJoinQry","C:\Book1.xlsx", "allTogether"