Return Focus to Access after Excel Export

  • Follow


This is something thats been bothering me for a while. I have a VBA
subroutine that takes a recordset and pastes the data to a blank Excel
worksheet. Then Access gives a message that it's complete.
The problem is that the active window is the excel worksheet, and not
the access form. Anyone know how to return the focus back to Acess
after the copyfromrecordset is complete?
0
Reply Alt255 11/25/2009 3:45:02 PM

Could you post your code so we can try and help.
-- 
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Alt255" wrote:

> This is something thats been bothering me for a while. I have a VBA
> subroutine that takes a recordset and pastes the data to a blank Excel
> worksheet. Then Access gives a message that it's complete.
> The problem is that the active window is the excel worksheet, and not
> the access form. Anyone know how to return the focus back to Acess
> after the copyfromrecordset is complete?
> .
> 
0
Reply Utf 11/25/2009 4:38:03 PM


On Nov 25, 10:38=A0am, Daniel Pineault
<DanielPinea...@discussions.microsoft.com> wrote:
> Could you post your code so we can try and help.
> --
> Hope this helps,
>
> Daniel Pineaulthttp://www.cardaconsultants.com/
> For Access Tips and Examples:http://www.devhut.net
> Please rate this post using the vote buttons if it was helpful.
>
>
>
> "Alt255" wrote:
> > This is something thats been bothering me for a while. I have a VBA
> > subroutine that takes a recordset and pastes the data to a blank Excel
> > worksheet. Then Access gives a message that it's complete.
> > The problem is that the active window is the excel worksheet, and not
> > the access form. Anyone know how to return the focus back to Acess
> > after the copyfromrecordset is complete?
> > .- Hide quoted text -
>
> - Show quoted text -

The code is too long, but here is a trimmed psuedo code

set recordset1 =3D database.querydefs(Trade Query")

set xlapp =3D excel.application
xlapp.displayalerts =3D false
xlapp.visible =3D true
xlapp.usercontrol =3D true
set xlwb =3D xlapp.workbooks("C:\Excel Template.xls")
set xlws =3D xlwb.worksheets("sheet1")
xlws.select
xlws.cells(10,1).copyfromrecordset recordset1
xlws.close
recordset1.close
msgbox("Done")

It is the msgbox done I want the user to see, but the active window is
the excel file. Once I go to Access, I will see the message, but I
want the code to automatically return to access once the recordset has
been copied, so the user knows that it is done. Right know some of the
users are just staring at excel not realising that the data exports
are done.



0
Reply Alt255 11/25/2009 5:12:22 PM

On Nov 25, 11:12=A0am, Alt255 <alt255.2...@gmail.com> wrote:
> On Nov 25, 10:38=A0am, Daniel Pineault
>
>
>
>
>
> <DanielPinea...@discussions.microsoft.com> wrote:
> > Could you post your code so we can try and help.
> > --
> > Hope this helps,
>
> > Daniel Pineaulthttp://www.cardaconsultants.com/
> > For Access Tips and Examples:http://www.devhut.net
> > Please rate this post using the vote buttons if it was helpful.
>
> > "Alt255" wrote:
> > > This is something thats been bothering me for a while. I have a VBA
> > > subroutine that takes a recordset and pastes the data to a blank Exce=
l
> > > worksheet. Then Access gives a message that it's complete.
> > > The problem is that the active window is the excel worksheet, and not
> > > the access form. Anyone know how to return the focus back to Acess
> > > after the copyfromrecordset is complete?
> > > .- Hide quoted text -
>
> > - Show quoted text -
>
> The code is too long, but here is a trimmed psuedo code
>
> set recordset1 =3D database.querydefs(Trade Query")
>
> set xlapp =3D excel.application
> xlapp.displayalerts =3D false
> xlapp.visible =3D true
> xlapp.usercontrol =3D true
> set xlwb =3D xlapp.workbooks("C:\Excel Template.xls")
> set xlws =3D xlwb.worksheets("sheet1")
> xlws.select
> xlws.cells(10,1).copyfromrecordset recordset1
> xlws.close
> recordset1.close
> msgbox("Done")
>
> It is the msgbox done I want the user to see, but the active window is
> the excel file. Once I go to Access, I will see the message, but I
> want the code to automatically return to access once the recordset has
> been copied, so the user knows that it is done. Right know some of the
> users are just staring at excel not realising that the data exports
> are done.- Hide quoted text -
>
> - Show quoted text -

I finally figured it, use:
 AppActive "<<Name of the Running Program>>"
0
Reply Alt255 11/25/2009 7:07:59 PM

It depends on how you are transferring the data to Excel, but without seeing
your code, we can only guess.  Please post the relevant parts of
CopyFromRecordset (?) so we can get an idea of what you are doing.

Thanks

john


Alt255 wrote:
>This is something thats been bothering me for a while. I have a VBA
>subroutine that takes a recordset and pastes the data to a blank Excel
>worksheet. Then Access gives a message that it's complete.
>The problem is that the active window is the excel worksheet, and not
>the access form. Anyone know how to return the focus back to Acess
>after the copyfromrecordset is complete?

-- 
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200911/1

0
Reply J_Goddard 11/25/2009 8:03:30 PM

http://accessaidprogramming.blogspot.com/2009/12/export-access-table-data-to-excel.html

"Alt255" wrote:

> This is something thats been bothering me for a while. I have a VBA
> subroutine that takes a recordset and pastes the data to a blank Excel
> worksheet. Then Access gives a message that it's complete.
> The problem is that the active window is the excel worksheet, and not
> the access form. Anyone know how to return the focus back to Acess
> after the copyfromrecordset is complete?
> .
> 
0
Reply Utf 12/3/2009 7:01:01 AM

5 Replies
640 Views

(page loaded in 1.701 seconds)


Reply: