Automation and communication between Access and Excel

[Peter Danes]

I'm starting a new database for the geological department in our museum. It'
s still in the pencil and paper, discussion and design stage. It will track
both publications and actual specimens, so the main structure will be a
many-to-many relationship. (One publication may talk about several rocks,
one rock may be mentioned in several publications.)



The linking table in the middle of the one-to-many-to-one will also have an
offshoot, a set of Excel workbooks. One of the properties of the members of
BOTH main relations is a set of analyses. Either, both or neither
(publications and rocks) may have one or more analyses attached. Each
analysis is essentially a table of numbers listing proportions of components
of a particular specimen. I don't want to embed the workbooks in the
database, for two reasons - database bloat and the desirability of being
able to mess with the analyses directly from Excel, without starting the
database. There are many different types, X-ray, photo-spectrometry, various
chemical methods and others. Each combination of rock and/or publication
will have at most one attendant workbook, whose name will be a combination
of the unique identifiers of the publication and/or rock, and the individual
analyses will be on separate worksheets in the workbook.



So far so good (although comments on this are welcome as well). What I want
to do from the database is automate some of Excel's operations. How might
this best be accomplished? I know how to start and operate Excel from
Access, I have done similar stunts automating Word from Access. But this
time I have several asynchronous tasks I would like to accomplish.



1. When the database starts, I want to immediately start a hidden instance
of Excel, and have Excel cruise through the subfolder below the database,
which will contain all the workbooks with the analysis worksheets. Excel's
task will be to read and store the name of every worksheet in every
workbook. When this is done, the list of names will be handed to Access, so
that as the user wanders about in the database, he will immediately know
whether a particular publication or rock has an analysis, and if so, what
all kinds, without the necessity of Access looking for a workbook and
examining the worksheet names each time a record becomes current. (The
response time would be unacceptable.) But neither do I want to force the
user to wait while Excel does this. The data waiting to loaded into this
database is already fairly large (cca. 100,000 members) with the possibility
to grow considerably. What is the best method for Access to determine when
Excel is done? It is of course possible to write a file and have Access
constantly look for this file, but that seems rather crude. I would have to
put an timer on every form that the user might have open and have each form
repeatedly interrogate the disk drive until the file is found, then read in
the data and cancel the timer event on each form. It seems there should be
some way for the Excel application to notify Access directly and have Access
respond, whether the user is doing something right then or not.



2. When the user sees that there is an analysis that he might like to
examine in detail, a button on the Access form (labeled "Show analysis")
will send a message to Excel that a particular workbook should be opened to
a particular worksheet and displayed to the user. I need to be able to deal
with two possibilities, one is an idling Excel process, possibly the same
one that read in all the names upon start-up, and the other is no Excel
running. There may have been a previous use of Excel, which the user closed
by completely closing Excel, or they may have simply Alt-Tabbed back to
Access and the Excel process is still there. I know how to deal with both
those possibilities, but again, I need to communicate between the two
processes. This time, I need Access to tell the Excel process something. If
I was starting a new process each time, there are command line parameters
that may be used, but how do I pass information to an already running
process?



Pete



----------------------------------------------------------



(This e-mail address is fake, to keep spammers and their auto-harvesters out
of my hair. If you want to get in touch personally, I am 'pdanes' and I use
yahoo mail. But please use the newsgroups whenever possible, so that all may
benefit from the exchange of ideas).

 

[strive4peace]

Hi Pete,

it seems like there would be more efficient ways to handle your
requirements. What about keeping all the data in Access and generating
reports as Excel workbooks? then, you can immediately know where you
have data...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[aaron.kempf]

just use Access Data Projects; MDB is for retards.

MDB isnt scalable enough; and what happens when you need to store more
info?

DO IT NICE OR DO IT TWICE

and excel isn't for reporting; for reals-

you should be using 'Analysis Services' and then just allow pivotTable
abilities via Analysis Services.
this is a much more robust long term architecture.

-Aaron

 

[Peter Danes]

Hi Crystal,



Thank you for the suggestion, but I did already consider that. One of the
problems is that people have to be able to perform numeric manipulations
with these datasets. Access does not have anything like the computational
apparatus available in Excel. It would also enormously complicate the
database design, since there are so many different types of analysis, and
each analysis has varying amounts of data in varying numbers of fields. The
complexity necessary to accommodate all this would dwarf the rest of the
database and still not give the user the numerical tools that Excel already
has.



Pete

 

[strive4peace]

Hi Peter,

I can give you some code to loop through a specified directory, open
each Excel file, and read the workbook names into a table for displaying
on a list. I need to look a bit, though, so I will have to do it
later... I will also take a better look at your original post.

I am kind of busy right now, but I responded to you because I love rocks
-- have a very nice collection thanks to the Geology Museum at the
college I went to. I also understand a lot of rock properties,
different type of analysis, flow properties (my back ground is
petroleum) so I figured I'd be a good one to help you.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[aaron.kempf]

your people need to do calculations?

TEACH THEM HOW TO WRITE A QUERY!

back in '97; I was a software tester.. and they brought 30 testers
into a room; and they taught them ALL how to write Access queries in
the matter of about a half hour.

If your users aren't capable of learning how to write Access queries
then find some new end users.

EXCEL IS FOR RETARDS; IT DOES NOT SUPPORT CODE REUSE

 

[strive4peace]

Hi Peter,

I found the code ... but will have to strip it to make it generic. Post
back if you want it, and I will take the time.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Peter Danes]

Hello Crystal,

Thank you, that would be very helpful. I played with the protoype over the
weekend and discovered that I don't need quite as much communication ability
as I first planned. Normal automation lets me use Access to start an
instance of Excel and display the correct worksheet. Also, the task of
scanning the workbooks runs rather slowly even on a good machine and
generates quite a large amount of data, probably more than would be
appropriate for passing as a parameter in some inter-process communication.
(So it seems, at least, from what I have been able to discover on the
subject. But I could be wrong.) Maybe more appropriate would be to have
Excel write the list of names to a text file and have Access read them in,
in which case all I need is for Excel to be able to yank Access by the tail
to say, "Hey, it's done." Not that I would turn up my nose at more, I'd love
to learn how to do this reliably and well in general, but that single
"interrupt request" from Excel to Access would pretty much serve my
immediate needs.

I did find several postings of VB code to accomplish this, but I'm not a VB
programmer and couldn't get them to work in VBA.

Pete

 

[Peter Danes]

Hello Crystal,

Something's munged up with my newsreader, this post didn't show up in the
Access thread.

Thank you for the offer, but before you spend a lot of time on directory
reading code, please don't. I already know how to do that, it's a very
straightforward programming exercise. I have done it both in Excel directly
and from Access as an automation routine.

My only problem is the communication between processes. I want Excel to do
the directory reading asynchronously, so that the Access user can do other
things while Excel performs this scan, then have Excel let Access know when
the scan is done. Some sort of event processing, I would suppose, except
that I don't know how to make Excel generate an event that Access would see.

Neat that you have a geo background. My father was a petroleum geologist for
Gulf Oil back in the late fifties, taught geophysics at a university for
many years, had many geologist family friends and my former girlfriend was a
PhD. submarine vulcanologist. So, although I'm not a geologist myself, I've
soaked up a fair bit by osmosis, in addition to several university geo
courses. I'm working for the National Museum in Prague at the moment, which
has a enormous mineral collection on display. If you ever get by here, stop
in, it's worth a visit.

Pete

 

[strive4peace]

Hi Peter,

"I want Excel to do the directory reading asynchronously"

I must tell, you, I do not know how to accomplish that -- not with code
from Access. The only way I can think of is to launch a seperate
process as Access cannot just "hand-over" execution like that. You can
put DoEvents in your code so that Access could still be used -- but the
time it take to loop through and read sheetnames is not long (human time).

"National Museum in Prague"

if I am ever in the neighborhood, that would definely be on my list of
things to see

I do want to go there; a good friend of mine (Tatiana, I am sure it is a
common name), who used to live in Houston, moved back to Slovakia 10 yrs
ago and I want to get back in touch with her.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[aaron.kempf]

Access and SQL Sever have THOUSANDS more power than Excel.

Lose the ****ing training wheel you stupid ****ing monkey

you are stuck in the first grade of the technology ladder

 

[Peter Danes]

Hi Crystal,

You're right, the way to do it is launch Excel as a separate process. That
part works fine. What has me stumped is detecting when that separate Excel
process has finished its task. If I put code in Access to continuously check
to see whether it's done, the whole point of a separate process is lost, I
might as well do it directly from Access. I need some sort of interrupt or
event that will notify Access when Excel is finished.

And unfortunately, this -does- take a long time, due to the number of
workbooks. It's possible that I may have to abandon this feature.

Actually, though, I just thought of another approach that *might* lead
somewhere. It's a bit of a tangent, however, so I'll start another thread
for that.

Thanks for your time,

Pete

 

[strive4peace]

Hi Peter,

one way to find out when Excel is done is to have it write a text file
in a particular directory that Access can check for


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Peter Danes]

Hi Crystal,

Yes, it's possible to have Access do that test that you mention, but that's
still the same approach to the underlying problem - Access has to keep doing
something to test whether Excel is done or not, which pretty much precludes
turning the database over to the user to operate as needed while Excel
munches away in the background and ACTIVELY NOTIFIES Access somehow when
it's done.

I don't want to keep testing things in Access, I want Access and the
database user to go on their merry way and have some sort of event fire,
like a mouse click, that would tell Access that Excel is done and Access
should go pick up the completed list of worksheet names. That means some
sort of interrupt, which would activate a section of Access code only once,
when it is needed. What you are suggesting is a polling approach, which
means VBA code in Access running constantly, repeatedly testing and waiting
until a certain condition is met. It's sometimes the only way to do
something, but in this case, it would be very inefficient. It would tie up
the Access process with continual tests, preventing the user from doing
anything, slowing the scan, so that it would make more sense in the end to
simply do the scan directly in Access. Starting an Excel process to do the
scan and then constantly testing to see if it's done only adds an extra
layer of complexity over using Access directly, while gaining nothing.

In any case, I've come up with a another approach. It's not what I wanted,
but several days of determined searching have turned up nothing that would
work exactly how I'd like.

I discovered that it's possible to use the ADOX.Catalog object to retrieve
the worksheet names without all the overhead of actually opening the entire
workbook. If you scan the archives for the words: adox catalog sheet names,
you'll find several very nice examples, complete with code. I rigged up a
simple test and it scans around twenty workbooks per second, which is not
exactly lightspeed, but is a vast improvement over the previous method of
opening them. So what I'm planning now is to have Access NOT scan for
worksheet names on start-up, but put a control somewhere where the user can
elect to do the scan manually, with the warning that it will take
approximately some amount of time, estimated from the number of workbooks I
see in the analysis folder.

If I run across something better, I'll post it in the groups, since my
searching did turn up many questions about similar situations, just no
answers.

Thank you for your effort,

Pete

 

[strive4peace]

Hi Peter,

"ADOX.Catalog"

good information, thanks!

"polling"

unfortunately, I can't think of anything else

in the Access db, make a general procedure to issue a message and then,
from Excel, grab the instance of Access and run the procedure... once
(if?) you get a message to work, you could modify it to do something else.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Robert Morley]

I haven't followed the entire thread, so excuse me if I'm way off-track
here, but if you've got an Excel object declared in Access, can you maybe
just follow Excel's events (declare it using "WithEvents") and then monitor
for whenever Excel is closed as a way of telling when it's done?


Rob

 

[Peter Danes]

Hi Robert,

yes, you're right, I can do that. The thing is, I don't want to. I want
Access to be entirely free for the user to fiddle with the database, not
have code running and constantly testing to see if Excel is done. If I do
that, I tie up Access and gain no advantage over simply having Access do the
work itself. The point of all this was to try establishing Excel as an
independent entity, which would do a job and activate an event in Access
when the job was done, much like a button press activates event code in a
form, rather than the form constantly testing a button to see if it is
pressed.

In VB it seems that it's possible to use WithEvents to make some code
available to be activated by an outside process, but I have been unable to
make any of the examples I found work in VBA. If you know how to do that, I
would be quite grateful for some pointers.

Pete

 

[Peter Danes]

Hi Crystal,

"ADOX.Catalog"
good information, thanks!

You're welcome. Glad something came of all this.

unfortunately, I can't think of anything else

Well, I have, the inter-process communication with which I started this
post. Unfortunately, none of my attempts to do this have worked. If there is
a way to do this, I haven't been able to discover it and nobody who does
know how has chimed in.

in the Access db, make a general procedure to issue a message and then,
from Excel, grab the instance of Access and run the procedure... once
(if?) you get a message to work, you could modify it to do something else.

Yeah, I found several examples of how to issue and receive messages. But
they're all from VB or other languages. Not one of them have I managed to
make functional in VBA. Do you know how to do that?

Pete

 

[aaron.kempf]

uh you've always got GetObject and CreateObject right?

GetObject can 'get' an open Excel instance and continue with it right?
GetObject can 'get' an open Access instance and continue with it
right?

make it single threaded and have either Excel do all the hardwork; or
Access do all the hardwork.

But having 2 trains that are mvoing in opposite directions that are
constantly looking for each other doesn't sound efficient or
architecturally sound

 

[strive4peace]

MsgBox in Access when Excel is Done
---

Hi Peter,

Got it!!

put this into a general module in your Access database

'~~~~~~~~~~~~`
Sub DoneWithStuff()

MsgBox "done with stuff"

End Sub

'~~~~~~~~~~~

and put this into Excel...

'~~~~~~~~~~~
Sub TestMessageToAccess()
'NEEDS REFERENCE TO
'Microsoft Access Object Library

Dim accApp As Access.Application

'since Access is already running, use that instance
On Error Resume Next
Set accApp = GetObject("c:\testdb.mdb")
On Error GoTo Proc_Err

If TypeName(accApp) = "Nothing" Then
'THIS DOESN'T WORK IF DB NOT OPEN...
'BUT YOU NEED A WAY TO KNOW IF YOU GOT THE OBJECT
MsgBox "Database is not open"
GoTo Proc_Exit

End If

Wait10Seconds


accApp.Run "DoneWithStuff"

Proc_Exit:
On Error Resume Next
'close and release object variables
Set accApp = Nothing
Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " TestMessageToAccess"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume

Resume Proc_Exit

End Sub

'~~~~~~~

Sub Wait10Seconds()

Dim mStart As Date
mStart = Now()

Do While DateDiff("s", mStart, Now) < 10
DoEvents
Loop

End Sub
'~~~~~~~~~~~~~~~~

to test, manually start the TestMessageToAccess program in Excel --
click in the routine and press F5

then switch over to Access and do stuff in your database. You will see
a message box in Access when Excel is done

well, the error stuff doesn't work if the db is not open, but now that
you have a start, you can tweak this -- you'll need to make
substitutions anyway

"c:\testdb.mdb"

is the name of the database you have open that contains the
DoneWithStuff sub


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*