I have a database that automatically imports several text file every night.
This happens automatically and was programmed in VB by a guy I work with.
Every morning when I open the database, I am dealing with data that was
current the night before. This is current enough for my needs

I then export a series of queries to excel files via a macro. The Excel
files are overwritten every time. I then open a master excel file (formatted)
and via code I import data from these newly created excel files and I get
sort of a summary report of the state of my business. Again via code, a
historical dated copy is saved in a seperate folder whenever I open the
master excel document.

What I would like to do is automatically export the queries from Access
after the automatic import VB procedure has occurred so I simply have to open
the excel master file every day. The Access DB is saved on a shared drive
and does not remain open.

(I would also like the excel file to open, link, save a copy and close
automatically but I think I need to tackle this one step at a time)

Does anyone have any advice?

Thanks
Ru

rudawg, what I think you want to do is set up your Excel application an
automation client with Access serving it data. You can do this in a fairly
straight forward way. Knowing little about your Excel sheets or what the data
comming in to Access looks like or what the queries do... I can only tell you
that If the data is comming into your Access database ok, and you are happy
with that, I think you may find the simplest solution is to set up queries in
Excel that pulls directly from the queries you have in Access (as opposed to
exporting from Access. A couple of reasons I suggest this route is Excel will
give you an option to automatically requery the Access database every time
you open the spread sheet. I used to do build some fairly complex automation
solutions that sound similar to what you are describing. Read on for an
explanation of the process I used for my solution.

My data was HMO benefit summary data. My client wanted me to deliver a
solution in Excel that gave them a two page Excel report showing a comparison
of this year's benefit level on the left side, and Last Year's on the right.
One Report for each of 128 HMOs spanning the US. The data was held in SQL
Server 2000, and to shape it from it's normalized structure in SQL Server to
a side by side flat file required several select and crosstab queries, which
Access is well suited for. So the reports could have been printed in Access
but they liked the 'Grid' look of the Excel spreadsheets, and if they needed
to make a change, they could just open up the appropriate Excel Spreadsheet
and make the change. I automated this process thusly:
1 select queries in Access pulled appropriate data from SQL,
2. several Access queries shaped the data just as I needed it.
3. A Make Table query made the final flat file that had all the data shaped
as I needed it. This table was called "MasterSTFM2005"
I created a VBA module that saved my "MasterSTFM2005" table with the current
date and time as its new table name, then it would run the queries and
MakeTable so that I would have a fresh copy of my "MasterSTFM2005"
I created Spreadsheet and formatted it the way my client specified on
Sheet1. On Sheet2, I created a query that pulled an exact copy of the
MasterSTFM2005 table, which, of course was always in the same location on the
network file share (R:\STFM\2005\MasterSTFM.MDB)
When you set up a query in Excel, it remembers where to pull data from and
it will ask you if you want to refresh the linked data when you open the
spreadsheet. This worked well.
The Next step was to 'Map' the fields in the formatted sheet1 to the
appropriate fields in Sheet2. You do this by using moving to the cell where
you want A1 in the Sheet2 data, click = then navigae with the mouse to
Sheet2, click on A1 and hit enter. Sheet1:B3 = Sheet2:A1. This was tedious
work but I got it done, and after setting it up, each time my client asked
me to re-run their 128 Excel Sheets, I simply opened my Access database, ran
the macro, then opened my Excel sheet and clicked the Yes button on the
pop-up form that reliably asked me if I would like to refresh the data in the
linked table.
So, to summarize you need a macro (VBA Module or Macro) that imports your
data, runs the qureies to shape your data, at the end of that process have a
Make Table query make tables with the same names each time (you can write
code to save this table before so it is not overwritten) which it sounds like
you are already doing. Set up Ecxel to query the data from Access and choose
the option to have Excel automatically update the data every time you open
the spreadsheet. a couple of mouse clicks... Good luck.
--
Everything in EXCESS!!! If you want to taste the flavor of LIFE, Take BIG
Bytes. Moderation is for monks…


"rudawg" wrote:

> I have a database that automatically imports several text file every night.
> This happens automatically and was programmed in VB by a guy I work with.
> Every morning when I open the database, I am dealing with data that was
> current the night before. This is current enough for my needs
>
> I then export a series of queries to excel files via a macro. The Excel
> files are overwritten every time. I then open a master excel file (formatted)
> and via code I import data from these newly created excel files and I get
> sort of a summary report of the state of my business. Again via code, a
> historical dated copy is saved in a seperate folder whenever I open the
> master excel document.
>
> What I would like to do is automatically export the queries from Access
> after the automatic import VB procedure has occurred so I simply have to open
> the excel master file every day. The Access DB is saved on a shared drive
> and does not remain open.
>
> (I would also like the excel file to open, link, save a copy and close
> automatically but I think I need to tackle this one step at a time)
>
> Does anyone have any advice?
>
> Thanks
> Ru

Ru,

If I understand correctly what you are requesting then it is very
simple, just locate the code procedure (sub or function) that does the
imports, and at the very end (right before the End Sub or End Function
line) insert this line of code:

DoCmd.RunMacro "MyExportMacro"

changing MyExportMacro to the actual name of the macro that does the
exports.

Note: if there is a DoCmd.Quit at the end of the procedure (to close
Access when finished) then insert the new line before that one, rather
than at the very end.

HTH,
Nikos

Rev,

Thanks for your reply. It sounds like I am attempting to do something very
close to what you have done. I have not tried your suggestions but believe
it will work up to a point. It will bring me closer to full automation but I
have another problem. In the process of creating the exportable queries from
the imported text files, I need to run a "make table" query.

So I can import the data into Access and possibly per your suggestion have
Excel query the database but I don't know that I can run the Access
"make-table" queries from Excel. I am also a little concerned about the time
required to run this process straight from Excel. My "manual" process (using
a macro to run all export queries) currently only takes a few seconds so it
might not be too problematic.

Assuming the time to run is not excessive, I am going to see if I can come
up with a workaround for the make table queries. Currently I export 13
queries and 4 of them are crosstab queries. Several of the queries are three
step queries meaning I query the raw dat three times to get to the export
query. My total munber of queries is 23. Each of these queries has a
parameter which filters for the year. The parameter is entered to a field in
a form. My data goes to 2002. I could create an individual query set for
each year but my math has that at more than 115 queries. (probably less with
a little thinking)

My goal is to view my historical data in a current perspective so I will
need to periodically run the queries for previous years but not every time.
Primarily I am interested in this years data vs last years data.

So if i haven't bored you to tears yet, do you think I would be better off
learning how to export automatically from Access or to create a bunch of
queries and have excel pull the data down from the database.

My current master Excel workbook has links to the thirteen exported queries
so I am familiar with the tedious work you mention in your post.

Thanks again



"Rev" wrote:

> rudawg, what I think you want to do is set up your Excel application an
> automation client with Access serving it data. You can do this in a fairly
> straight forward way. Knowing little about your Excel sheets or what the data
> comming in to Access looks like or what the queries do... I can only tell you
> that If the data is comming into your Access database ok, and you are happy
> with that, I think you may find the simplest solution is to set up queries in
> Excel that pulls directly from the queries you have in Access (as opposed to
> exporting from Access. A couple of reasons I suggest this route is Excel will
> give you an option to automatically requery the Access database every time
> you open the spread sheet. I used to do build some fairly complex automation
> solutions that sound similar to what you are describing. Read on for an
> explanation of the process I used for my solution.
>
> My data was HMO benefit summary data. My client wanted me to deliver a
> solution in Excel that gave them a two page Excel report showing a comparison
> of this year's benefit level on the left side, and Last Year's on the right.
> One Report for each of 128 HMOs spanning the US. The data was held in SQL
> Server 2000, and to shape it from it's normalized structure in SQL Server to
> a side by side flat file required several select and crosstab queries, which
> Access is well suited for. So the reports could have been printed in Access
> but they liked the 'Grid' look of the Excel spreadsheets, and if they needed
> to make a change, they could just open up the appropriate Excel Spreadsheet
> and make the change. I automated this process thusly:
> 1 select queries in Access pulled appropriate data from SQL,
> 2. several Access queries shaped the data just as I needed it.
> 3. A Make Table query made the final flat file that had all the data shaped
> as I needed it. This table was called "MasterSTFM2005"
> I created a VBA module that saved my "MasterSTFM2005" table with the current
> date and time as its new table name, then it would run the queries and
> MakeTable so that I would have a fresh copy of my "MasterSTFM2005"
> I created Spreadsheet and formatted it the way my client specified on
> Sheet1. On Sheet2, I created a query that pulled an exact copy of the
> MasterSTFM2005 table, which, of course was always in the same location on the
> network file share (R:\STFM\2005\MasterSTFM.MDB)
> When you set up a query in Excel, it remembers where to pull data from and
> it will ask you if you want to refresh the linked data when you open the
> spreadsheet. This worked well.
> The Next step was to 'Map' the fields in the formatted sheet1 to the
> appropriate fields in Sheet2. You do this by using moving to the cell where
> you want A1 in the Sheet2 data, click = then navigae with the mouse to
> Sheet2, click on A1 and hit enter. Sheet1:B3 = Sheet2:A1. This was tedious
> work but I got it done, and after setting it up, each time my client asked
> me to re-run their 128 Excel Sheets, I simply opened my Access database, ran
> the macro, then opened my Excel sheet and clicked the Yes button on the
> pop-up form that reliably asked me if I would like to refresh the data in the
> linked table.
> So, to summarize you need a macro (VBA Module or Macro) that imports your
> data, runs the qureies to shape your data, at the end of that process have a
> Make Table query make tables with the same names each time (you can write
> code to save this table before so it is not overwritten) which it sounds like
> you are already doing. Set up Ecxel to query the data from Access and choose
> the option to have Excel automatically update the data every time you open
> the spreadsheet. a couple of mouse clicks... Good luck.
> --
> Everything in EXCESS!!! If you want to taste the flavor of LIFE, Take BIG
> Bytes. Moderation is for monks…
>
>
> "rudawg" wrote:
>
> > I have a database that automatically imports several text file every night.
> > This happens automatically and was programmed in VB by a guy I work with.
> > Every morning when I open the database, I am dealing with data that was
> > current the night before. This is current enough for my needs
> >
> > I then export a series of queries to excel files via a macro. The Excel
> > files are overwritten every time. I then open a master excel file (formatted)
> > and via code I import data from these newly created excel files and I get
> > sort of a summary report of the state of my business. Again via code, a
> > historical dated copy is saved in a seperate folder whenever I open the
> > master excel document.
> >
> > What I would like to do is automatically export the queries from Access
> > after the automatic import VB procedure has occurred so I simply have to open
> > the excel master file every day. The Access DB is saved on a shared drive
> > and does not remain open.
> >
> > (I would also like the excel file to open, link, save a copy and close
> > automatically but I think I need to tackle this one step at a time)
> >
> > Does anyone have any advice?
> >
> > Thanks
> > Ru

Ru,

Stepping back from the details of what you're trying to accomplish,
here's an idea on the process which you might find useful: from within
Excel, you can run a macro which opens the Access database; the database
has an Autoexec macro which does all the import/manipulation/export, and
then closes again. I have something similar in use for years, I have a
command button in my Excel sheet to open the Access database and run the
macro, and the beauty of it is that Access runs in the background,
without the user even seeing it!

HTH,
Nikos

Nikos,

I think this idea would work. I already have most of the macros written
because I currently can do this all "manually". How do I call Access from
Excel and run the autoexec macro?

My process is

Open Master Excel sheet.
via code, data is refreshed from 40 downloaded excel files
via code, a historical copy is saved in a different directory with a dated
name
via code, a "last updated" field is populated with today's date
This is done so that when a second copy is opened or a historical copy is
opened the file is not refreshed again from the current data.

Before I do this.

I open Access form
select year (it defaults to this year)
Export queries to be grabbed by Excel
( My inventory data is always current and my sales information is dated)
This explains the 13 exports for each year and the 40 imports into excel.
When I want to re-assign accounts or products into different categories, I
re-export the queries for prior years.

In the background via ??? and VBA:
Text files are exported from our corporate system.
They are FTP'd to my server
Sometime between 3am and 9am access is called via VBA and my tables are
updated. The FTP'd text files are renamed from "*.txt" to "*.old" so after
they are updated for the day the process stops importing them.

My question then: Would I call Access from my Master Excel file? I think I
would have to as the others are created via the Access export. I want to try
this.....How do I call Access from Excel?

Thanks
Ru



"Nikos Yannacopoulos" wrote:

> Ru,
>
> Stepping back from the details of what you're trying to accomplish,
> here's an idea on the process which you might find useful: from within
> Excel, you can run a macro which opens the Access database; the database
> has an Autoexec macro which does all the import/manipulation/export, and
> then closes again. I have something similar in use for years, I have a
> command button in my Excel sheet to open the Access database and run the
> macro, and the beauty of it is that Access runs in the background,
> without the user even seeing it!
>
> HTH,
> Nikos
>

Try this within Excel VBA:

Set AccessDatabase =
GetObject("\\ServerName\FolderPath\MyDatabase.mdb")

substituting the full server\path\file name.

HTH,
Nikos