CopyDataFromQuery

Genoma · June 12th, 2012, 07:45 PM

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.

gjgriffith · June 12th, 2012, 07:56 PM

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,

Genoma · June 12th, 2012, 08:26 PM

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

gjgriffith · June 12th, 2012, 08:54 PM

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,

Genoma · June 12th, 2012, 09:00 PM

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

gjgriffith · June 12th, 2012, 09:13 PM

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,

Genoma · June 12th, 2012, 09:26 PM

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