Hiya.

Things are finally starting to work for me... whoopie. But I need a bit of help in something:

I am getting a load of recordsets in Access, opening a workbook and pasting in the recordsets and then emailing the workbook.

2 problems:

1. With my method, when the procedure runs it goes over to Excel to perform the copyrecordset etc. I need the focus to stay on the Access database when it is doing all the work. How can I do this (my code is below).

2. I have something (XLRunning) to assess whether Excel was running before the procedure was done - if it wasn't I want to quit Excel - but I don't know how to do this without referencing my excel object (which will have already been closed). Can you help? (notes on code below).

Code:

Set XLObject = GetObject(TargetFile)

With XLObject
.Application.Visible = True 'If I don't make visible, copyrecordset fails
.Parent.windows(1).Visible = True
.Sheets(2).Range("A9").CopyFromRecordset rstref
.Sheets(3).Range("A9").CopyFromRecordset rstpreop
.Sheets(4).Range("A9").CopyFromRecordset rstbl
.Sheets(5).Range("A9").CopyFromRecordset rstip
.Sheets(6).Range("A9").CopyFromRecordset rstpostop
End With

XLObject.SendMail patrepemail, "RPH Patient Report"
XLObject.Close SaveChanges:=True
If XLRunning = False Then XLObject.Application.Quit 'This won't work coz XLObject is already closed

Thanks for any suggestions.

Basil

"Basil" <(E-Mail Removed)> wrote in message
news:3F75D52E-9F5E-4894-9A9F-(E-Mail Removed)...
> Hiya.
>
> Things are finally starting to work for me... whoopie. But I need a bit of
help in something:
>
> I am getting a load of recordsets in Access, opening a workbook and
pasting in the recordsets and then emailing the workbook.
>
> 2 problems:
>
> 1. With my method, when the procedure runs it goes over to Excel to
perform the copyrecordset etc. I need the focus to stay on the Access
database when it is doing all the work. How can I do this (my code is
below).

What do you mean by "focus", here? Normally, "focus" means the application
or control that will receive the user's keystrokes. The receipt of
keystrokes does not seem relevant to what your are doing.


> 2. I have something (XLRunning) to assess whether Excel was running before
the procedure was done - if it wasn't I want to quit Excel - but I don't
know how to do this without referencing my excel object (which will have
already been closed). Can you help? (notes on code below).

Your code does not make sense to me. Suirely the Close method only closes
the open workbook (not the whole Excel application)? If yes (true), then,
the following Quit should work ok. If no (false - Close >does< close Excel
itself) - why do you then do a Quit?

As for XLRunning, you don't show how that variable is set initially (unless
I'm missing that), so it's tough to comment on that variable, no?

HTH,
TC


> Code:
>
> Set XLObject = GetObject(TargetFile)
>
> With XLObject
> .Application.Visible = True 'If I
don't make visible, copyrecordset fails
> .Parent.windows(1).Visible = True
> .Sheets(2).Range("A9").CopyFromRecordset rstref
> .Sheets(3).Range("A9").CopyFromRecordset rstpreop
> .Sheets(4).Range("A9").CopyFromRecordset rstbl
> .Sheets(5).Range("A9").CopyFromRecordset rstip
> .Sheets(6).Range("A9").CopyFromRecordset rstpostop
> End With
>
> XLObject.SendMail patrepemail, "RPH Patient Report"
> XLObject.Close SaveChanges:=True
> If XLRunning = False Then XLObject.Application.Quit 'This
won't work coz XLObject is already closed
>
> Thanks for any suggestions.
>
> Basil
>

Thanks for the comments TC - here is a bit more detail (I must be crap at writing these things coz I always seem to have to give more detail!)

1. Focus - not everyone is a programmer and knows the official terminologies. I meant that on screen the Excel application becomes visible on top of the Access database using this method. I want to have a form up during the time in Access - which is always on top - saying "please wait whilst I do all your work for you..." - and then on completion of the procedure it closes the form. I do not want Excel to be shown on the screen (if I don't make it visible, the copyfromrecordset method won't work) - so I would rather it be visible, but in the background behind the database

2. You are correct, the code does only close the workbook - but the code to quit will not work because my XLobject variable references the workbook - when closed it is no longer connected to Excel, hence will result in an error when you try and reference the Excel Application from it. The reason I do not want to simply quit is because I loop through many workbooks (I have not shown all this code) - at the end of all the workbooks, I would then want to quit Excel. The method I use to determine if excel is running in the first place isn't relevant - it simply populates a boolean variable

Thanks for any suggestions, I hope I have helped clarify the problem - English and Programming terminology ain't my strong points

Basi

----- TC wrote: ----


"Basil" <(E-Mail Removed)> wrote in messag
news:3F75D52E-9F5E-4894-9A9F-(E-Mail Removed)..
> Hiya
>> Things are finally starting to work for me... whoopie. But I need a bit o
help in something
>> I am getting a load of recordsets in Access, opening a workbook an
pasting in the recordsets and then emailing the workbook
>> 2 problems
>> 1. With my method, when the procedure runs it goes over to Excel t
perform the copyrecordset etc. I need the focus to stay on the Acces
database when it is doing all the work. How can I do this (my code i
below)

What do you mean by "focus", here? Normally, "focus" means the applicatio
or control that will receive the user's keystrokes. The receipt o
keystrokes does not seem relevant to what your are doing


> 2. I have something (XLRunning) to assess whether Excel was running befor
the procedure was done - if it wasn't I want to quit Excel - but I don'
know how to do this without referencing my excel object (which will hav
already been closed). Can you help? (notes on code below)

Your code does not make sense to me. Suirely the Close method only close
the open workbook (not the whole Excel application)? If yes (true), then
the following Quit should work ok. If no (false - Close >does< close Exce
itself) - why do you then do a Quit

As for XLRunning, you don't show how that variable is set initially (unles
I'm missing that), so it's tough to comment on that variable, no

HTH
T


> Code
>> Set XLObject = GetObject(TargetFile
>> With XLObjec
> .Application.Visible = True 'If
don't make visible, copyrecordset fail
> .Parent.windows(1).Visible = Tru
> .Sheets(2).Range("A9").CopyFromRecordset rstre
> .Sheets(3).Range("A9").CopyFromRecordset rstpreo
> .Sheets(4).Range("A9").CopyFromRecordset rstb
> .Sheets(5).Range("A9").CopyFromRecordset rsti
> .Sheets(6).Range("A9").CopyFromRecordset rstposto
> End Wit
>> XLObject.SendMail patrepemail, "RPH Patient Report
> XLObject.Close SaveChanges:=Tru
> If XLRunning = False Then XLObject.Application.Quit 'Thi
won't work coz XLObject is already close
>> Thanks for any suggestions
>> Basi
>

Basil wrote:

> Thanks for the comments TC - here is a bit more detail (I must be
> crap at writing these things coz I always seem to have to give more
> detail!).

You should consider that an asset. You obviously have a feel for
condensed writing. See if you can find a way to put that to use more
often--it may even pay.
--
Bas Cost Budde
http://www.heuveltop.nl/BasCB

Ok, two things.

First, I can't imagine why you would have to make Excel visible in order to
use the copyfromrecordset method. Are you saying, that if you include the
..application.visible=true line, it works, but if you re-add that line (and
do not make any other changes), it does not work? What is the error number
and message that you get when it does not work? How are you certain that the
error is occurring on the copyfromrecordset statement, & not from some other
statement?

In summary of point (1), I feel you would be better-off to determine >why<
the copyfromrecordset statement does not work when excel is not visible. Fix
that problem, then you can leave Excel invisible. That will solve the
"focus" problem.

As for (2), I see your problem now. You have said:

Set XLObject = GetObject(TargetFile)

That will start Excel, open the file, & return a reference to the >file< (or
workbook, whatever) - not a reference to Excel. So when you close the
workbook, that reference is indeed kaput! Here's what I would do to fix
that. Use CreateObject to start Excel. Now, you have a reference to Excel.
Then use the appropriate Excel method (Open?) to open the target file. Now
you have a seperate reference to the target file. Now you can do things to
the target file - eg. close it - >without< distirbing the reference to
Excel.

Something like this (untested)

dim XLDocument as object
Set XLObject = CreateObject ("Excel.Application") ' <- ref. to EXCEL.
with XLObject
set XLDocument = .Open (TargetFile) ; <- ref. to DOCUMENT.
...
XLDocument.Close ' close the DOCUMENT.
.Quit ' quit ECEL.
end with

HTH,
TC



"Basil" <(E-Mail Removed)> wrote in message
news:1A0E7A1E-A6ED-4A57-90AC-(E-Mail Removed)...
> Thanks for the comments TC - here is a bit more detail (I must be crap at
writing these things coz I always seem to have to give more detail!).
>
> 1. Focus - not everyone is a programmer and knows the official
terminologies. I meant that on screen the Excel application becomes visible
on top of the Access database using this method. I want to have a form up
during the time in Access - which is always on top - saying "please wait
whilst I do all your work for you..." - and then on completion of the
procedure it closes the form. I do not want Excel to be shown on the screen
(if I don't make it visible, the copyfromrecordset method won't work) - so I
would rather it be visible, but in the background behind the database.
>
> 2. You are correct, the code does only close the workbook - but the code
to quit will not work because my XLobject variable references the workbook -
when closed it is no longer connected to Excel, hence will result in an
error when you try and reference the Excel Application from it. The reason
I do not want to simply quit is because I loop through many workbooks (I
have not shown all this code) - at the end of all the workbooks, I would
then want to quit Excel. The method I use to determine if excel is running
in the first place isn't relevant - it simply populates a boolean variable.
>
> Thanks for any suggestions, I hope I have helped clarify the problem -
English and Programming terminology ain't my strong points!
>
> Basil
>
> ----- TC wrote: -----
>
>
> "Basil" <(E-Mail Removed)> wrote in message
> news:3F75D52E-9F5E-4894-9A9F-(E-Mail Removed)...
> > Hiya.
> >> Things are finally starting to work for me... whoopie. But I need
a bit of
> help in something:
> >> I am getting a load of recordsets in Access, opening a workbook
and
> pasting in the recordsets and then emailing the workbook.
> >> 2 problems:
> >> 1. With my method, when the procedure runs it goes over to Excel
to
> perform the copyrecordset etc. I need the focus to stay on the
Access
> database when it is doing all the work. How can I do this (my code
is
> below).
>
> What do you mean by "focus", here? Normally, "focus" means the
application
> or control that will receive the user's keystrokes. The receipt of
> keystrokes does not seem relevant to what your are doing.
>
>
> > 2. I have something (XLRunning) to assess whether Excel was running
before
> the procedure was done - if it wasn't I want to quit Excel - but I
don't
> know how to do this without referencing my excel object (which will
have
> already been closed). Can you help? (notes on code below).
>
> Your code does not make sense to me. Suirely the Close method only
closes
> the open workbook (not the whole Excel application)? If yes (true),
then,
> the following Quit should work ok. If no (false - Close >does< close
Excel
> itself) - why do you then do a Quit?
>
> As for XLRunning, you don't show how that variable is set initially
(unless
> I'm missing that), so it's tough to comment on that variable, no?
>
> HTH,
> TC
>
>
> > Code:
> >> Set XLObject = GetObject(TargetFile)
> >> With XLObject
> > .Application.Visible = True 'If
I
> don't make visible, copyrecordset fails
> > .Parent.windows(1).Visible = True
> > .Sheets(2).Range("A9").CopyFromRecordset rstref
> > .Sheets(3).Range("A9").CopyFromRecordset rstpreop
> > .Sheets(4).Range("A9").CopyFromRecordset rstbl
> > .Sheets(5).Range("A9").CopyFromRecordset rstip
> > .Sheets(6).Range("A9").CopyFromRecordset rstpostop
> > End With
> >> XLObject.SendMail patrepemail, "RPH Patient Report"
> > XLObject.Close SaveChanges:=True
> > If XLRunning = False Then XLObject.Application.Quit
'This
> won't work coz XLObject is already closed
> >> Thanks for any suggestions.
> >> Basil
> >