S
sendto3283
I run around 100 excel reports with data from sql queries every month.
The process for each report is fairly simple: I first run a saved
sql query to get the data. Next, I use excel query wizard to import
the data to an preformatted excel sheet. Finally, the report is saved
and an email goes out.
For example, to run the "Jones" report, I run jones.sql, open
jones.xls, import the data using query wizard, save it as
jones_yymm.xls, then send off an email to Jones herself. jones.sql
and jones.xls are kept in one folder; jones_yymm.xls is kept in
another
My dream has been to automate the process. My thinking is that I
could have VBA run through a table or spreadsheet containing the sql
query name & path, the excel name and path; the path for the finished
report, and the email address for the recipient and ccs's . A VBA
process would go through the table and
.. . run the saved query
.. . open the excel sheet then refresh/import the data
.. . run macros
.. . save the worksheet with a different name
.. . send the email
It seems like this would be fairly easy but I have spent several hours
this morning googling for some answers and not getting far. For
example, i can find code to run a query text (DoCmd.RunSql) but I
think DoCmd.OpenQuery "jones.sql" is going to work only on access
queries...
Any advice or suggestions will be appreciated.
NOTE: In some cases the sql code is embedded in the xls sheet and
refreshed on open. However, most reports went through several
revisions before finalizing, and it was simply easier to work on
revising code in sql query analyzer than excel. Similarly, it is
easier to refresh a report "template".
The process for each report is fairly simple: I first run a saved
sql query to get the data. Next, I use excel query wizard to import
the data to an preformatted excel sheet. Finally, the report is saved
and an email goes out.
For example, to run the "Jones" report, I run jones.sql, open
jones.xls, import the data using query wizard, save it as
jones_yymm.xls, then send off an email to Jones herself. jones.sql
and jones.xls are kept in one folder; jones_yymm.xls is kept in
another
My dream has been to automate the process. My thinking is that I
could have VBA run through a table or spreadsheet containing the sql
query name & path, the excel name and path; the path for the finished
report, and the email address for the recipient and ccs's . A VBA
process would go through the table and
.. . run the saved query
.. . open the excel sheet then refresh/import the data
.. . run macros
.. . save the worksheet with a different name
.. . send the email
It seems like this would be fairly easy but I have spent several hours
this morning googling for some answers and not getting far. For
example, i can find code to run a query text (DoCmd.RunSql) but I
think DoCmd.OpenQuery "jones.sql" is going to work only on access
queries...
Any advice or suggestions will be appreciated.
NOTE: In some cases the sql code is embedded in the xls sheet and
refreshed on open. However, most reports went through several
revisions before finalizing, and it was simply easier to work on
revising code in sql query analyzer than excel. Similarly, it is
easier to refresh a report "template".