Return Focus to Access after Excel Export

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
Alt255
11/25/2009 3:45:02 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

5 Replies
908 Views

Similar Articles

[PageSpeed] 25

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
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
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
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
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
Utf
12/3/2009 7:01:01 AM
Reply:

Similar Artilces:

Eliminating Duplicates from a Query Access 2007
I've run a query that captures the data set I want. Now I want to eliminate Duplicate names and retain only the unique names. These are text fields, can I do this in the Criteria in Design View? Many thanks. (B^>)-]=[ hi, On 01.04.2010 16:46, WSR wrote: > I've run a query that captures the data set I want. Now I want to eliminate > Duplicate names and retain only the unique names. > > These are text fields, can I do this in the Criteria in Design View? Take a look at the Unique Values property of your query. mfG --> stefan <-- Und...

Customer Returns
I'd like to know if there someone has written a RMS report that shows the customer account number, customer's name (last, first), and other customer statistics that INCLUDE pieces purchased and pieces returned. Or, if someone with more knowledge of SQL would care to share their coding expertise to accomplish the same, it would be appreciated. ...

Excel
1. An Excel tabulation begins at Row 6 ; and then, Cell G6 is subject to Conditional Formatting as follows :- =AND(ROWS($G$6:G6)<=COUNTIF($B$6:$B$35,$B$6)+COUNTBLANK($G$6:G6),G6<>"") 2. Subsequently, the Conditional Formatting of Cell G6 is "Paint-Brushed" to the succeeding cells. 3. And there, a blank row is inserted over Row 6 ; Look at the Conditional Formatting again, it has changed as follows :- =AND(ROWS($A$1:$G$7)<=COUNTIF($B$7:$B$36,$B$7)+COUNTBLANK($A$1:$G$7),G6<>"") 4. Now, is that change justifiably outrageous ? Expected, or what ...

Publisher
I am not sure if this post belongs in this newsgroup or in Access. I have not recieved an anser in the Access group so I will try it here. I have a document designed in Publisher 2003 that I want to do a merge with a query in Access 2003. When I choose the Access front end on the local computer to link to and test the connection I get a "Microsoft Data Link Error" window that states "Test onection failed because of an error initializing provider. Cannot start you application. The workgroup information file is missing or opened exclusively by another user." The Acce...

How set focus to subform
When I click on a button on my main form I want to switch the focus to a control on a subform. What is the syntax? I tried Me!mySubForm.Form.Setfocus and it said 'Invalid method". Try this for the syntax: Forms![YourFormName]![YourSubFormName].Form![ControlName].SetFocus -- HTH Mr B askdoctoraccess dot com "mscertified" wrote: > When I click on a button on my main form I want to switch the focus to a > control on a subform. What is the syntax? I tried Me!mySubForm.Form.Setfocus > and it said 'Invalid method". Actually, all I had to code was: Me...

Decimals in Excel
Hi , I am using P0 and N0 to get rid of decimals and they are still appearing in excel ? Any suggestions ? Regards Malcolm ...

Work to Excel
Can I import Ms Works spread sheet to Excel 2007 In Vista ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=9a6295a6-c821-45dd-bb27-4dbe4ae805e1&dg=microsoft.public.excel.newusers 2007 doesn't seem ...

exporting email from outlook express to microsoft outlook 2003
I have several email accounts on outlook express. When I try to import them to the new accounts I set up in Outlook, all the email/addresses/etc. from one of my accounts (the default email account in Express) goes into each new Outlook account. How can I import just the email from one account into a new account? "Wendy" <Wendy@discussions.microsoft.com> wrote in message news:08C323C8-BF1D-44CF-863D-02E9E7341A46@microsoft.com... >I have several email accounts on outlook express. When I try to import them > to the new accounts I set up in Outlook, all the email/addres...

Description of Statistical functions in Excel 2002
Hello, I am looking for the English description of the statistical functions in Excel 2002 Could some one mail me the text of this help file in a doc file or show me a website where this is shown? The Help function has a page with a listing and description of all the statistical functions. I hope someone can help me. Best regards, You can copy and paste the text from Help into Word. Jerry Erik Gerets wrote: > Hello, > > I am looking for the English description of the statistical functions in > Excel 2002 > > Could some one mail me the text of this help file in a d...

Excel 2002 document not opening with a proper view in Excel 2003
On opening an Excel 2002 document, I do not get the actual text in column names or values. For example Name is showing as #NAME? and not "Kevin Depeter". You might want to widen the column. It might not have enough room to show the name. "taj" wrote: > > On opening an Excel 2002 document, I do not get the actual text in column > names or values. For example Name is showing as #NAME? and not "Kevin > Depeter". ...

Re: Access 97 to Access XP conversion Issues
I have posted here twice previously with no reply or even offers for solutions from Microsoft Corp or anyone else. I know, that I am not the only one suffering from this terrible Access XP condition! Oh, I have already downloaded and installed Office SP3, if that means anything! So please, if you too have suffered like I have, feel free to chime in, okay? I have converted an Access97 application to Access XP. Only problem is, whenever the application is run it blows out generating this error: "AppName: msaccess.exeAppVer: 10.0.6501.0 ModName: vbe6.dll ModVer: 6.4.99.69 Offset: 00...

Date and Day in Excel
I have 12 Sheets, each representing each month of the year...Jan, feb......Dec. I need Date and Day in 1st and 2nd colum of each sheet representing that month. Need to know how to formulate this...Thanks in advance for any help.. Hi try the following formula to get the first day of the current year for this montn: =DATEVALUE("1-" & RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND ("]",CELL("filename",A1),1)) & "-" & TEXT(TODAY(),"YYYY") If you put this in cell A1 you could use the following formula in B...

VC++ 6.0 dll can not access a network drive
Hi All, I have a VC 6.0 dll, which is called from one another exe. This dll in turn will execute couple of programs in a network with CreateProcess. When I ran the CreateProcess, my getlast error returned with error code 3 (directory not found). I opened a dos box and I was able to switch to the network drive without any problem. I checked and made sure the user has full permission to the drive where he is trying to access the files. The program is running under vista OS. The server is Windows 2K3. I wrote a simple console application in VC 6.0, which does nothing but do a lookup on a file o...

Return-Path: <> from Postmater
Hi, I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive bad mail the response is made from postmaster@mydomain.com but the return-path field is blank. With some anti-spam filter this Email is Junk. Return-Path: <> Anybody know how to specifeid a return-path to the NDR mail? _____________ Thank you Sebastien On Fri, 4 Nov 2005 12:48:04 -0800, "Sebas" <Sebas@discussions.microsoft.com> wrote: >Hi, > >I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive >bad mail the response is made from postmaster@mydomain.c...

Report showing which users have access to a particular window/repo
Is there a report that can be printed (or a particular table that can be looked at) that will list all users that have access to a particular window or report? We're on GP 10, upgraded from GP 9, so all the roles and tasks are CNV_..... In particular, we need a quick and easy way to see which users have access the the payroll check history report. Sure there is. Abreviated instructions follow but detailed ones can be found in our Dynamics GP Security Handbook on page II-38. Load the Security Resource Table first by going to Maintenance, select Clear Tables (BE CAREFUL)...

Excel Colours
Hi all Each Excel workbook can hold a palette of 56 colours, why is it tha the colour picker tool on the Formatting toolbar will only show 4 colours (on a 5 x 8 grid)? Thanks for anyone supplying enlightenment! DJ -- DJ ----------------------------------------------------------------------- DJB's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2122 View this thread: http://www.excelforum.com/showthread.php?threadid=38983 I think it's the same reason hotdogs are sold in packages of 10, but hotdog buns come in packages of 8. Who knows why? But you could r...

Query regarding A,B,C char. width values returned in 16-bit and 32-bit OS
I am trying to retrieve individual character widths of a string (from a TrueType font) and I tried using GetCharWidth (which weirdly works for TrueType fonts too tho' MSDN says otherwise) and/or GetCharABCWidths to retrieve the A,B and C spacings. Please note that I have selected the TrueType font (hFont) into the Device Context (hDC). I used GetCharABCWidths. Problem is this: GetCharABCWidths returns correct A,B and C widths in Win 98 and incorrect (much lesser A,B and C widths such that the characters OVERLAP) in other 32-bit OS (NT, XP). The effect is seen across all TrueType fonts, th...

Formula Does not work on excel 2003
This formula does not work in excel 2003. Does anyone know how to convert it to the correct formula for excel 2003. It works perfectly on my system with 2007. But when I put it on a system with 2003 it returns nothing. What it is supposed to do is pull data from a sheet name in the format dd.mm.yy. Sometimes the sheet is not there so should return nothing or 0. =IF(ISERROR(INDIRECT("'"&TEXT($A4,"dd-mm-yy")&"'!$k$12")),"",INDIRECT("'"&TEXT($A4,"dd-mm-yy")&"'!$k$12")) Your formula work...

accessing cable TV
I have an old Toshiba laptop (M105-S3084) that came with XP Media Center 2002 - but have never bothered with the Media Center part. At home I also have Cox cable TV, both standard and HD. What additional hardware or connections would I need to add to receive SD cable TV on my laptop? If a USB tuner is needed which one is recommended? Anything else? Thank you. Jeff Go through the following URL:- http://www.microsoft.com/windows/windows-media-center/learn-more/record-tv/articles/broadcast-tv-overview.aspx AVerTV Hybrid Volar MAX H826 a good one. regards, S.Seng...

Turn off gridline in partial Excel worksheet
I know how to turn off gridlines for an entire worksheet, but is it possible to turn off for just a portion of a sheet? I am pasting a link into PowerPoint of a partial worksheet, but it brings across the gridlines. Cannot just copy a picture, as I need the dynamic link for updating the data. Thanks for any ideas! Hi, it's impossible. You can only change formatting of selected cells to white color. "Queen Glitter Bunny" wrote: > I know how to turn off gridlines for an entire worksheet, but is it possible > to turn off for just a portion of a sheet?...

links to excel file question
Hi all, I have a hyperlink in an Outlook 2000 email that links to a Microsoft Excel 2000 file that contains vba code. The Excel program generates the error however it's based on how the link is handled in Outlook. When I click the link through the preview window in Outlook everything works fine. The link looks like this - <\\sfna-fs4\hrs\shared\ctm training\ctmtrainingsignup.xls> When I open the email (i.e. view the email in it's own window) and then click the link I get the runtime error in excel. The link display look likes this - \\sfna-fs4 \hrs\shared\ctm trai...

Counting how many times a number appears in a list?(EXCEL)
hi, got a small problem in excel, situation as below: grade 1 2 3 1 2 1 2 3 4 1 1 what formula do I use to count how many times (e.g) 1 appears in th list? I thought I could do it with the Count function but haven managed to. Cheers -- Message posted from http://www.ExcelForum.com The "countif" formula should do! Dunca -- Message posted from http://www.ExcelForum.com =COUNTIF(A1:A15,1) -- Regards, Peo Sjoblom "sox >" <<sox.152stn@excelforum-nospam.com> wrote in message news:sox.152stn@excelforum-nospam.com... > hi, got a small problem in excel...

Access 2000 Upsizing Wizard with SQL Server 2000 Developer
I am getting the following message and I am not sure why. Connection failed: SQL State: '01000' SQL Server Error: 53 [Microsoft][ODBC SQL Server Driver][DBNETLIB]Connection Open (Connect()). Connection failed: SQL State: '08001' SQL Server Error: 17 [Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server does not exist or access denied. I used the default settings to name the Server [local] and I double checked through Enterprise Manager that I could see the server and the databases. Any ideas on what is wrong? I am new to SQL Server. CLM In the newsgroup post, yo...

Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard Orders? Yes. You must make sure to change the site when performing a Return Transaction. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Everton Raymond" wrote: > Is it possible to have a different default Site ID for Returns than Standard > Orders? Sorry, I did not see the word "default" in your post until after I r...

Program Error -- EXCEL.exe has generated errors and will be closed by Windows.
Program Error -- EXCEL.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being created. [OK] I get this message almost everytime I open Excel now. I have Office Standard 2003 SR-1 and have all the patches from Windows Update. I have not made any changes to Excel or Office in the last several months -- no patches, etc. I ran the Help...Detect & Repair... feature and this still happens. It's about 8/10 times I try to open Excel I get this message now. Any advice??? THanks! Hi Pablo! Close down and re-boot. Empty your Wind...