Export sveral tables to same Spreadsheet

[patyavila]

Working with a form in which the user select files to be exported to a single spreadsheet using TransferSpreadsheet action. In help I noticed that "if the file name is the same of an existing... exporting to an Excel version 5.0 or later Access copies the exported data to the next available new worksheet.." Tried creating the spreadsheet with enough worksheets but Access allways replace the old file resultin in a one sheet workbook only.

Appreciate any help.

Patricia

 

[kjv1611]

Well, if you are exporting all to the same table, then I would think you have 2 different options: One append all tables to same spreadsheet in excel, or use a make table query, along with one or more append queries to join all the tables in access into one table, and then export it as one table to one spreadsheet. I know there is some code floating around in here to do the first task, you just have to look for it, b/c I've seen it.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[patyavila]

Thanks Stephen, but the format MUST be one table per sheet :-( , in fact I'm writing code as this should run transparent for the user (who happends to know almost nothing of Access) and it won't bother me to go through something more sophisticated.

Patricia

 

[kjv1611]

Are the tables not compatible? In that case, I would assume you would need to run some sort of looping code to set the start of the first table to Cell A1 for instance, and then the second table at some point after the end of data on the first table, and so on. I am assuming that you mean all the tables are totally different, but yet the user needs all the data on one worksheet.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV