R
ridawg
Not sure this is the right area but hopefully someone can help me out.
Basically, I'm trying to automate the creation of several monthly reports
using vbs, vba, sql, access, excel, word, and outlook (office 2003). Below is
an outline of what I've done to date and where I've run into problems.
Using the the Task Scheduler on the first day of each month it executes
(opens) a vbscript file. This vbscript file opens a word document
(Run_Monthly_Reports).
The word document contains several different things.
1. Code for to verify the date and time (document_open). If it is the first
of the month and between 7-7:15 am then call the Create_All_Reports sub. The
purpose of this sub is to only execute the Create All Reports sub on certain
dates and only during a specific time rather than everytime the document is
opened.
2. Button & Code for each report - i.e. Sub btnReport1_Click(), Sub
btnReport2_Click(), etc. Each of these subs contains code that opens up an
Access database (executes 1 to many queries against SQL to create a set of
tables for the report), then opens up an Excel template which is used to
create the new monthly report, and finally creates a new Outlook message that
is sent with the new report attached to it.
3. Button & Code for the Sub Create All Reports. This is where I'm having
problems. This code consists of call statements to each of the Report Subs.
Example: call btnReport1_Click
call btnReport2_Click
What I would like to have happen is when the document is opened on the
appropriate date and time is for all reports to be created and for the word
document to close (another area I'm having problems with - I can't seem to
figure out how to close word once the reports are run). I think one issue is
make word the active document after a report is created. I'm pretty new to
vba so any help is greatly appreciated. I have no idea if this is the best
way to do this so if there is a better way please let me know but keep in
mind I'm limited to accomplishing this with tools on my desktop (office 2003
- I have access to sql as a data source but that's it). Thanks!
Basically, I'm trying to automate the creation of several monthly reports
using vbs, vba, sql, access, excel, word, and outlook (office 2003). Below is
an outline of what I've done to date and where I've run into problems.
Using the the Task Scheduler on the first day of each month it executes
(opens) a vbscript file. This vbscript file opens a word document
(Run_Monthly_Reports).
The word document contains several different things.
1. Code for to verify the date and time (document_open). If it is the first
of the month and between 7-7:15 am then call the Create_All_Reports sub. The
purpose of this sub is to only execute the Create All Reports sub on certain
dates and only during a specific time rather than everytime the document is
opened.
2. Button & Code for each report - i.e. Sub btnReport1_Click(), Sub
btnReport2_Click(), etc. Each of these subs contains code that opens up an
Access database (executes 1 to many queries against SQL to create a set of
tables for the report), then opens up an Excel template which is used to
create the new monthly report, and finally creates a new Outlook message that
is sent with the new report attached to it.
3. Button & Code for the Sub Create All Reports. This is where I'm having
problems. This code consists of call statements to each of the Report Subs.
Example: call btnReport1_Click
call btnReport2_Click
What I would like to have happen is when the document is opened on the
appropriate date and time is for all reports to be created and for the word
document to close (another area I'm having problems with - I can't seem to
figure out how to close word once the reports are run). I think one issue is
make word the active document after a report is created. I'm pretty new to
vba so any help is greatly appreciated. I have no idea if this is the best
way to do this so if there is a better way please let me know but keep in
mind I'm limited to accomplishing this with tools on my desktop (office 2003
- I have access to sql as a data source but that's it). Thanks!