how to pull Access query having UDF to excel ?

[hvsummer]

Currently I use indirect way to pull data to excel.
I create table and query result insert into it.
then pull data from that table to excel.

how can I get query result directly from access to excel ? (that query using UDF in access)

[zmbd]

HV,
You are going to have to give an example here... we don't usually write the code out and there will be specifics that may apply to your project.

However, with that said, this sounds like an application automation with Excel, something that you've shown in your other posts you have some familiarity with :)

[hvsummer]

No, I don't need code == I mean what method can get those data out from query that having UDF inside Access to excel ==

I can write code myself, just need the right way to go 0.0

[mbizup]

Try posting some specific examples...

Offhand, I'd suggest:

DoCmd.TransferSpreadsheet  acExport, , YourQueryName,YourFilePath, True ' etc

Or automation code as zmbd suggested if you are not using a saved query, or if you need more customization. There are plenty of examples to be found out there with searches such as 'Export Access Recordset to Excel'

If those methods aren't working for you, let us know what specific problems you are encountering (or specific goals you are trying to reach)

[jimatqsi]

Maybe I'm out to lunch today and forgetting something obvious, but what is UDF?

[mbizup]

jimatqsi,

UDF = 'User Defined Function' (custom code)

[hvsummer]

Mbizup: Thank for reply, but that code won't work since I don't "export",
I need to "pull" data, mean the excel is exist and everything prepaired, just need data from Access lay on the sheet, that all ==

unlucky I can't use query connection or access's db connection because I used UDF on Saved query that block me to get data == don't tell me I have to use automaton access from excel 0.0 It like "turn on the light in the afternoob" ==

[zmbd]

HV: Your function has to be executed in some fashion.
When your query is picked up by the ACE/JET/DBE and it encounters the UDF the DBE calls the VBA interpreter, passes any fields in the current record as needed, and waits for the returned value.

Now running the UDF in the query directly from Excel, from what I've read, has been disabled for security reasons.

You might be able to have the function ran within Access - make sure it is set as "PUBLIC' then one could try some basic application automation (I don't see an easy way around this):

Dim zAcc As Object
 Set zAcc = CreateObject("Access.Application")
 zAcc.OpenCurrentDatabase "FileNameAndPathGoHere"
 zAcc.Run ("FunctionNameGoesHere")
 if not zAcc Is Nothing then
   zAcc.CloseCurrentDatabase
   set zAcc = Nothing
 end if

Along this same line, would could create sub that executes your query, pass in any augments (obj.run [procedurename],[aug1]...)... honestly, what you appear to be doing here is not something I've done before and really seems to be very, complex...

[jforbes]

I don't understand why you are moving your data around so much. Why don't you just use Access to create your Reports?

[hvsummer]

In my company I have to use excel to report. So that I prepair a template in excel that already have summary there, just need to get data out from access ==

If I could use Access report, I would please to do. unfornaturely, that's not my decision ==

@zmbd:
"Now running the UDF in the query directly from Excel, from what I've read, has been disabled for security reasons."

If it can be disabled then it can be enable, how to do that ?

btw, can ADO or DAO recordset open saved query to get data ?

I got to use UDF because Access don't have function I need ==
Like this, I have to report eachmonth, so query should get data from that month. Instead manually Month(BDate) = 11 each query, I call 1 UDF that get that month from Table1 through ADO.

I know I can use DLookup to do that but I have 2 or more UDF in used, so that just an example, that I need some function Access build-in can't provide

[Rabbit]

What are the UDFs doing exactly?

[MikeTheBike]

Hi

I have written/crated dozens of reports in Excel usung data from Access and I invariable write the code in an Excel Module(s) providing buttons/UserForms for parameter selection etc (parameter retieved from the Access DB on the forms Initialize event obviously!).

To do this I use ADO connections and recordset objects.

The advantage I find in doing this is that I have total control over the report and its formatting; it also runs faster in Excel (that is a mystery but it usualy the case).
Some of these reports a quite large with 30 plus sheet and significant amounts of data.

The required query string(s) can (usually are) created in the Access query designer and pasted into Excel VBA (with mods to concatenate variable as necessary, particularly the wild card character in ADO i.e. uses %).

Virtually all the reports are created on the fly (only use template for complicated graph).

In my case this is almost essential as in some cases as I pull data from up to 4 Databases for a single report.

So, I would recommend this approach is considered in these circumstances.

Just to complete this little diatribe, I also use Excel to import data from excel spread sheet (generated by a financial/SQL database, again giving me total control over data validation etc. and the ability to save the data to the database structure as required.

Even when I do export data from within Access I always use automation (late binding) and never the built in DoCmd.TransferSpreadsheet. Again this eliminated any formatting issued etc.

btw, can ADO or DAO recordset open saved query to get data ?

Yes it can, it is treated just the same as a table (as it is in Access). However I always connect to the back end (its quicker) and stored queries are normally only in the front end, in which case I just use the stored query string in code as a sub-query.

Not sure if this is any help with the initial question, but I thought it might. I am sure other will have an opinion on this !!



MTB

[hvsummer]

@mikethebike:

wow, after I read your suggestion, I pop up new idea, why don't I copy my SQL stored to excel vba and copy those UDF in access to excel too lol, then I'll have a stored query inside Excel :D