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).

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
*



Peter Danes wrote:
> 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).
>
>
>
>

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" <(E-Mail Removed)> píse v diskusním príspevku
news:%23$(E-Mail Removed)...
> 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
> *
>
>
>
> Peter Danes wrote:
> > 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).
> >
> >
> >
> >

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
*



Peter Danes wrote:
> 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" <(E-Mail Removed)> píse v diskusním príspevku
> news:%23$(E-Mail Removed)...
>> 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
>> *
>>
>>
>>
>> Peter Danes wrote:
>>> 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).
>>>
>>>
>>>
>>>
>
>

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
*



strive4peace wrote:
> 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
> *
>
>
>
> Peter Danes wrote:
>> 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" <(E-Mail Removed)> píse v diskusním príspevku
>> news:%23$(E-Mail Removed)...
>>> 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
>>> *
>>>
>>>
>>>
>>> Peter Danes wrote:
>>>> 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).
>>>>
>>>>
>>>>
>>>>
>>
>>

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




"strive4peace" <(E-Mail Removed)> píse v diskusním príspevku
news:OPUOg$(E-Mail Removed)...
> 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
> *
>
>
>
> strive4peace wrote:
> > 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
> > *
> >
> >
> >
> > Peter Danes wrote:
> >> 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" <(E-Mail Removed)> píse v diskusním príspevku
> >> news:%23$(E-Mail Removed)...
> >>> 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
> >>> *
> >>>
> >>>
> >>>
> >>> Peter Danes wrote:
> >>>> 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).
> >>>>
> >>>>
> >>>>
> >>>>
> >>
> >>

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" <(E-Mail Removed)> píse v diskusním príspevku
news:%(E-Mail Removed)...
> 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
> *
>
>
>
> Peter Danes wrote:
> > 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" <(E-Mail Removed)> píse v diskusním príspevku
> > news:%23$(E-Mail Removed)...
> >> 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
> >> *
> >>
> >>
> >>
> >> Peter Danes wrote:
> >>> 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).
> >>>
> >>>
> >>>
> >>>
> >
> >

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
*



Peter Danes wrote:
> 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" <(E-Mail Removed)> píse v diskusním príspevku
> news:%(E-Mail Removed)...
>> 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
>> *
>>
>>
>>
>> Peter Danes wrote:
>>> 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" <(E-Mail Removed)> píse v diskusním príspevku
>>> news:%23$(E-Mail Removed)...
>>>> 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
>>>> *
>>>>
>>>>
>>>>
>>>> Peter Danes wrote:
>>>>> 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).
>>>>>
>>>>>
>>>>>
>>>>>
>>>
>
>

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




On Feb 9, 4:32 am, "Peter Danes" <SkruSpamm...@no.spam> wrote:
> 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" <strive4peace2...@yahoo.com> píse v diskusním príspevkunews:%23$(E-Mail Removed)...
>
>
>
> > 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
> > *
>
> > Peter Danes wrote:
> > > 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 bea
> > > 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' andI
> use
> > > yahoo mail. But please use the newsgroups whenever possible, so that all
> may
> > > benefit from the exchange of ideas).- Hide quoted text -
>
> - Show quoted text -

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" <(E-Mail Removed)> píse v diskusním príspevku
news:(E-Mail Removed)...
> 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
> *
>
>
>
> Peter Danes wrote:
> > 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" <(E-Mail Removed)> píse v diskusním príspevku
> > news:%(E-Mail Removed)...
> >> 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
> >> *
> >>
> >>
> >>
> >> Peter Danes wrote:
> >>> 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" <(E-Mail Removed)> píse v diskusním príspevku
> >>> news:%23$(E-Mail Removed)...
> >>>> 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
> >>>> *
> >>>>
> >>>>
> >>>>
> >>>> Peter Danes wrote:
> >>>>> 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).
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>
> >
> >