Open Excel temp from a Query

[Runawaygeek]

Hi all,

Bit of an odd one, but can you Export a Query to Excel as a temp file.
By that i mean, not writing an Excel file to a save location, but more Opening excel, putting the data in and then the user has to save the document?

The reason being, I dont know or have access to any of the network that I need would normally set as PATH.

So i am hoping there is a way to just open new and copy data in?

Any ideas?

Thanks,
Ben

 

[The_Doc_Man]

You always have access to the folder in which the database is located.

The CurrentDB.Name will give you the fully qualified name with drive letter, path, database name, and .MDB or .ACCDB (or the ...DE equivalents) as appropriate. Using one or the other of the string search and extraction functions, you should be able to extract the device and path of the database itself.

If you have a linked back-end, the Connection string for a linked table also contains the device, path, name, and type for the back-end file. Again, string diddling can net you the device and path of the back-end file.

 

[MrHans]

You can also use an Access Excel automation example, for example this one provided by Daniel Pinault from the DevHut:
https://www.devhut.net/2017/03/15/ms-access-vba-export-recordset-to-excel/

If you simply don't save the file, the user can do that himself or just close the file without saving....

 

[Runawaygeek]

Thanks, i used this, it works great. His page is full of handy tips.

All i need to resolve now is how to save the user input before i call the function.
When the query opens it asks the user to inputs Dates (Between [Start Date:] AND [End Date]) as part of the where clause.

But the code takes the query as a record set, so it never asks for the dates and then fails for missing 2 parameters.

I know, i want my cake and to eat it!


thanks anyway.
Ben