Query questions in new Access 2007 database

[mishmish3000]

Hi there! Hey, I'm developing a new database in Access to deal with reporting influenza-like illnesses in Tennessee. I've made a Word document with screen shots and a couple of specific questions about queries. One is how to automate importing a CSV from a specific location on a user's PC (MY DOCUMENTS). One is how to export the processed result from Access to a tab in an established Excel workbook. We've done some cool stuff with exporting to Excel, but the way the end user needs the data organized on the tab in the workbook is sort of odd. Please let me know what you think!
I'll attach the database and the Word document...
Thanks!
MishMish3000

[mishmish3000]

Aha! I chased down the user and clarified... we only need a summary table exported to the Excel file, not as shown in the first Word document. Here's the revised Word document, with simpler notes. Whew!

[mishmish3000]

OK, I can now prompt the user to enter in the CDC week they're wanting to see... in the query that makes the data export table.
http://www.databasedev.co.uk/parameter_queries.html" onclick="window.open(this.href);return false; helped out! Hope it can help others!
Now maybe someone can help me figure out how to export the summary data I need to the tab in the Excel file... YAY!
Have a great weekend! MishMish3000

[mishmish3000]

If you add "TRUE" to the fifth parameter of the code importing the CSV, it'll pull in the header row too, which is convenient.

[mishmish3000]

Yup, I fended off Outlook questions and DBAs, and figured that much out... now if I can get help on exporting the file to a tab in the Excel file, we'll be super! I'll attach another zipped copy of the database.

Have a wonderful weekend!

[HansV]

It seems to me that TblExportTable is the one you want for your Excel table. You shouldn't simply add the ILI percentages, however, but compute it as Number with ILI / Number of Patients * 100.

[mishmish3000]

Yes, Hans, you're right! Can you show me how to export just that table as a tab in an existing Excel file? I have the name of the existing Excel file in the notes in the document I sent in earlier. But you're right about the ILI percentages. I'll fix that!
Thanks, MishMish3000

[HansV]

Code to export could look like this:

Code: Select all

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
        "TbllExportTable", "H:\Excel\ILI.xlsx",True, "Reg Summaries"

Replace H:\Excel\ILI.xlsx with the path and filename of the workbook. TbllExportTable will be exported to the sheet named Reg Summaries.

[mishmish3000]

Excellent, Smithers! LOL