How to export four tables to one excel file?

[Guest]

I have four tables in access: tlbTable1, tlbTable2, tlbTable3 and tlbTable4
Now i would like to export these tables to one excel "Report" file

The code i know:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "tlbTable1",
"C:\Report"

how i can modify this code and archive it, thank you.

 

[Guest]

If you are wanting to create one "long" table of the 4 you could create a
union query and export that just like you would export a table. If they
contain completely different data fields and you wish to display all four
table "side by side" you could create the usual select query and export
that... I am guessing you already know how to make a select query so If you
need help with a "Union Query" open a new query in design mode and then close
the table select go to the top left and change the view from design to SQL
and then paste the following:

SELECT tblTable1.* FROM tblTable1 UNION
SELECT tblTable2.* FROM tblTable2 UNION
SELECT tblTable3.* FROM tblTable3 UNION
SELECT tblTable4.* FROM tblTable4;

then save it and export it like you would a table to get all four tables
into one export to Excel...
-- Take Care & God Bless ~ SPARKER ~

 

[Guest]

Sorry, I haven't said my problem clearly. actually, i do not want export four
tables to an excel in one sheet. I would like to export these tables to an
excel file is sheet by sheet. table1 to sheet1 and table2 to sheet2 ... .
Thank you very much

 

[Guest]

oh i c... Did you know that you can open up your excel workbook and link each
worksheet to a table in your database? In other words you can have each
worksheet display the data directly from your database. Open your Excel
workbook and navigate to the Data menu at the top and then drop down to the
Get External Data and then over to New Database Query and then from the
databases tab select MS Access Database and then use the navigation window to
locate your database and then you have a wide range of options from tables,
queries what fields etc... Repeat this process for all 3 sheets then add a
fourth and do it one more time. Tada you have an Excel workbook that displays
all four tables from your database. You can write a little code to have it
make copies of itself or email itself etc... Does that help?

 

[Nikos Yannacopoulos]

Hmm... risky? I would stick with the export approach. The trick is to
use the Range argument of TransferSpreadsheet. Although Acess help says
it is not used in exporting, in fact it does direct the output to a
sheet named after the argument, if no defined range with the same name
exists in the target workbook. Some interesting features of
TransferSpreadsheet:
* if the workbook does not exist, it will create it; if it does, it will
overwrite or create new sheets (see next point)
* if a sheet by the name of the Range argument does not exist, it will
be created; if it does, its contents will be overwritten.
Just make sure you don't use any funny characters in the argument; most
punctuation characters fail to get across, and get converted to
underscores. Alphanumeric and spaces are fine.

Sampel code:

Dim vTable(3) As String
vTable(0) = tlbTable1
vTable(1) = tlbTable2
vTable(2) = tlbTable3
vTable(3) = tlbTable4
For i = 0 to 3
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, _
vTable(i), "C:\Report",True, vTable(i)
Next

This will name the sheets after the tables. If you want different names,
just make the array two-dimensional and define different names.

HTH,
Nikos