automated process (export)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
Try this within Excel VBA:

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

substituting the full server\path\file name.

HTH,
Nikos
 
Back
Top