Wrox Programmer Forums
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 12th, 2012, 07:45 PM
Registered User
 
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.
 
Old June 12th, 2012, 07:56 PM
gjgriffith's Avatar
Wrox Author
 
Join Date: Jul 2009
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! *** ---------------------------------------------------------------------------------------------------------->
 
Old June 12th, 2012, 08:26 PM
Registered User
 
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
 
Old June 12th, 2012, 08:54 PM
gjgriffith's Avatar
Wrox Author
 
Join Date: Jul 2009
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! *** ---------------------------------------------------------------------------------------------------------->
The Following User Says Thank You to gjgriffith For This Useful Post:
Genoma (June 12th, 2012)
 
Old June 12th, 2012, 09:00 PM
Registered User
 
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
 
Old June 12th, 2012, 09:13 PM
gjgriffith's Avatar
Wrox Author
 
Join Date: Jul 2009
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..
The Following User Says Thank You to gjgriffith For This Useful Post:
Genoma (June 12th, 2012)
 
Old June 12th, 2012, 09:26 PM
Registered User
 
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









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