Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
BOOK: Access 2010 VBA Programmer's Reference
This is the forum to discuss the Wrox book Access 2010 Programmer's Reference by Teresa Hennig, Rob Cooper, Geoffrey L. Griffith, Jerry Dennison; ISBN: 978-0-470-59166-6
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Access 2010 VBA Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 12th, 2012, 07:45 PM
Registered User
Points: 24, Level: 1
Points: 24, Level: 1 Points: 24, Level: 1 Points: 24, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2012
Posts: 6
Thanks: 2
Thanked 0 Times in 0 Posts
Default CopyDataFromQuery

Good evening,

In the chapter 18 there is an example of a public sub called CopyDataFromQuery.

I don't find a way to use it.

Can you please explain how to make it work?

Thanks in advance.
Reply With Quote
  #2 (permalink)  
Old June 12th, 2012, 07:56 PM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default What is the issue?

Hello Genoma,

Thank you for posting your question about the Access 2010 Programmers Reference book. You asked:

Quote:
I don't find a way to use it. Can you please explain how to make it work?
So, I'm not sure I understand the question. Are you trying to call it from VBA code and getting an error? It looks like it is working to me! Please provide as much information about the problem as you can and I will do what I can to help!

Thanks,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
Reply With Quote
  #3 (permalink)  
Old June 12th, 2012, 08:26 PM
Registered User
Points: 24, Level: 1
Points: 24, Level: 1 Points: 24, Level: 1 Points: 24, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2012
Posts: 6
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Hello Geoffrey.

Thanks for answering.

Yes I'm trying to call it from a command button in a form, but the first parameter generates errors (ByRef argument type mismatch) if I don't dimension a variable as Excel.Application

If I dimension it (that seems weird) I don't get any error but it doesn't do anything (or at least do not display any excel workbook).

I use to do may things between Access and Excel by using automation, but this approach seems interesting... if I can make it work ;)

So, can you please provide an example of how you called this sub from VBA?

Many thanks,

Diego

Last edited by Genoma; June 12th, 2012 at 08:47 PM.. Reason: Add the Error type
Reply With Quote
  #4 (permalink)  
Old June 12th, 2012, 08:54 PM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default The XLS file must have a Workbook open!

Hello Diego,


Thanks for the follow up here. You said:

Quote:
If I dimension it (that seems weird) I don't get any error but it doesn't do anything (or at least not display any excel workbook).
Ok, so I see what your problem is here. To use this function, you must: 1. have already created an Excel.Application object, AND 2. have a workbook open in that Excel.Application object. So, to provide an example for end-to-end usage of the "CopyDataFromQuery()" function:

Code:
Public Sub TestCode()

    ' Create a new instance of the Excel.Application object
    Dim xlApp as New Excel.Application
    
    ' Open your XLS file in the Excel.Application object
    xlApp.Workbooks.Open "C:\MyExcelFile.xls"   ' This should be the path to your XLS file

    ' Call the CopyDataFromQuery() function with the query name that you need copied
    CopyDataFromQuery xlApp, "TheQueryToCopy"   ' This should be the name of your query.

End Sub
And really that should be it! Does this all make sense? If you still have questions, please just let me know and I'll do what I can to help!

Regards,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
Reply With Quote
The Following User Says Thank You to gjgriffith For This Useful Post:
Genoma (June 12th, 2012)
  #5 (permalink)  
Old June 12th, 2012, 09:00 PM
Registered User
Points: 24, Level: 1
Points: 24, Level: 1 Points: 24, Level: 1 Points: 24, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2012
Posts: 6
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Thank you Geoffrey!
Yeap that works.
I'd just add this couple of lines at the end
Code:
    xlApp.Visible = True
    xlApp.UserControl = True
Regards,
Diego
Reply With Quote
  #6 (permalink)  
Old June 12th, 2012, 09:13 PM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Great!

Hi Diego,

Awesome - that sounds great, I'm glad you got it to work. And yes, you can certainly add that code as well, if you want the user to be able to see the XLS file while your code runs. I didn't want to add too much info here, so as not to confuse, but I would probably also do it like this (if I were using this code in a real life application):

Code:
Public Sub TestCode()
On Error GoTo HandleErrors

    ' Create a new instance of the Excel.Application object
    Dim xlApp as New Excel.Application
    
    ' Open your XLS file in the Excel.Application object
    xlApp.Workbooks.Open "C:\MyExcelFile.xls"   ' This should be the path to your XLS file

    ' Call the CopyDataFromQuery() function with the query name that you need copied
    CopyDataFromQuery xlApp, "TheQueryToCopy"   ' This should be the name of your query.

    ' Save the Changes
    xlApp.Workbooks(1).Save

ExitSub:

    ' Close the XLS file and exit Excel
    xlApp.Quit
    Set xlApp = nothing

    Exit Sub

HandleErrors:

    ' Handle erros here
    Resume ExitSub

End Sub
Doing it this way will do everything behind the scenes and the user will never see the changes being made to the workbook. But really, as you point out, there may be lots of different user scenarios here, as is in the case where you want the XLS file to be visible for the user.

Anyway, I'm glad to have helped out here! Thank you for reading the Microswoft Access 2010 Programmer's Reference and if there is anything else we can answer about the book for you, you know where to post !

Thanks again,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->

Last edited by gjgriffith; June 12th, 2012 at 09:17 PM..
Reply With Quote
The Following User Says Thank You to gjgriffith For This Useful Post:
Genoma (June 12th, 2012)
  #7 (permalink)  
Old June 12th, 2012, 09:26 PM
Registered User
Points: 24, Level: 1
Points: 24, Level: 1 Points: 24, Level: 1 Points: 24, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2012
Posts: 6
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Thank you Geoffrey.
I have both the 2007 and 2010 versions of your book.
I like them a lot!!!

I was a little bit confused with the examples in that chapter, but now all them seems to be clear to me.

It definitely seems a good idea to have reusable functions for interacting with Excel instead of having to write all the code over and over again (as I usually do).

So, many thanks for the explanation... :)

Regards,

Diego
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off




All times are GMT -4. The time now is 07:35 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.