automatic export 1

[ztm]

hello all,
I'm having trouble making a clean app. I run a series of queries in access and put the results in a temporary table. I export the table's contents to an excel spreadsheet. I have macros set up to format the data, perform calculations, etc once inside excel. Everything works fine, what I need is to automate the whole process for the user from a form. I have button that runs a macro that first runs the make-table query, then opens the excel spreadsheet that contains the autorun macro to format the imported data. What I can't seem to get working is automating the export after the make-table query.

 

[Aristarco]

ztm:

I've made somewhat simmilar separating a table in categorized smaller tables my means of an iteration that executes a [tt]SELECT ... INTO ...[/tt] query. Then the temporary table is exported to excel with the [tt]DoCmd.TransferSpreadsheet acExport[/tt] instruction.

What I've found is that this instruction has a bug that avoids exporting when the temporary table name or the path has some punctuation characters like the , so I had to write a few lines that uses an alternative name when the comma appears.

I don't know what yo want to do exactly, but here's my code for you to analyze. I insist that this fails sometimes for unknown reasons.
[tt]
CurrentDb.Execute "SELECT * INTO Temporary FROM New_Products] WHERE ProductType Like 'Meat'"
'Exports temporary table to an Excel 97 spreadsheet
DoCmd.TransferSpreadsheet acExport, 8, "Temporary", "C:\Prods\"
'Deletes the temporary table
CurrentDb.TableDefs.Delete "Temporary"
[/tt] To boldly code, where no programmer has compiled before!

 

[ztm]

Thanks very much! TransferSpreadsheet works great- solved my problem, thanks again.