Export Query To Excel

[weirddemon]

Does anyone know if there's a method to export a query into an excel spreadsheet?

I've looked around, but I can't seem to find any solution that works.

I'm just getting the data like so:

SELECT tbl_HPMRBInventory.ID, tbl_HPMRBInventory.RecvDate, tbl_HPMRBInventory.CSO_N, tbl_Commodity.CommodityType, tbl_HPOEMPN.Part_Number, tbl_HPOEMPN.Platform, tbl_HPMRBInventory.Site_ID, tbl_HPMRBInventory.GP_TransferError, tbl_HPOEMPN.OEM_Name
FROM ((tbl_HPMRBInventory LEFT JOIN tbl_HPOEMPN ON tbl_HPMRBInventory.PN_ID = tbl_HPOEMPN.ID) LEFT JOIN tbl_Commodity ON tbl_HPOEMPN.Commodity_Type = tbl_Commodity.ID) LEFT JOIN tbl_Location ON tbl_HPOEMPN.LocationID = tbl_Location.LocationID
WHERE (((tbl_HPMRBInventory.GP_TransferError) Like 'Invalid Part in GP*') AND ((tbl_HPMRBInventory.GP_Transfer)=0 Or (tbl_HPMRBInventory.GP_Transfer) Is Null))
ORDER BY tbl_HPMRBInventory.RecvDate;

So I was hoping I could do the same, but automatically export it all to a spreadsheet.

[Shaggy Hiker]

Who would run the query? Excel can't, SQL Server can't, Access might come pretty close, and .NET can....with a few steps along the way. I have a class over in the .NET CodeBank that I wrote to export datatables and datareaders. That might not apply for you, though.

[jcis]

I think SQL Server can do it all by itself if you create a DTS (Data Transformation Services) package that sends the query results to an Excel file, looks like an easy one (1 step or 2). Then a job can execute the package either directly or scheduled.

[weirddemon]

Who would run the query? Excel can't, SQL Server can't, Access might come pretty close, and .NET can....with a few steps along the way. I have a class over in the .NET CodeBank that I wrote to export datatables and datareaders. That might not apply for you, though.

I was planning on executing the query and everything through a scheduled job. I've setup the email portion through the job. Now I just need to export the data and attach the sheet.

I think SQL Server can do it all by itself if you create a DTS (Data Transformation Services) package that sends the query results to an Excel file, looks like an easy one (1 step or 2). Then a job can execute the package either directly or scheduled.

Everything that I've looked up so far suggests that DTS is depreciated. I'll keep looking though.

[TysonLPrice]

I'm not sure exactly what you want but you can put the query into Excel and run it when you open the spreadsheet. The user refreshes the data when it comes up. Look at the menu item "From Other Sources" in version 2007. I think it was "External data sources" in 2003.

Basically you set up a connection.
Step through selecting your tables etc.
Edit the query Excel created replacing it with yours.
And go

http://excelusergroup.org/blogs/nick...rnal-data.aspx

[abhijit]

In 2010, the option is under the Data tab captioned as "connections".
You can use this to link your sheet to an external data source.

However, I am guessing that's not what you are looking for.