Unanswered: Export sql query to excel vba easier way
I have a query ( Using sql) containing data that i need to export into excel.
I am currently exporting it by cut and paste the data, it is
not a very intuitive way of doing things, what i would prefer is if i
could have a button on a form which when it was clicked, it
automatically exported the query to a Excel file. Can anyone help me
with the VBA that would be neccessary, so i can connect excel and ms sql.
Have you tried using MS query from Excel? Open a blank worksheet, click on "Data", "Import External Data", then "New Database Query". That will open up MS-Query with the query wizard. You can use the wizard to recreate your sql query from you db tables, or just stop the wizard, click on the SQL button in MS query and you can actually paste the sql code there. Will probably have some syntax issues, but those are pretty easy to solve. Once the query is created in MS-Query and you export the results to Excel, the query will be saved with the worksheet and you can use VB to simply refresh the query by the name you assign. Will need to set up an OBDC SQL driver to your db for this to work.
Note: If you can possibly create the query via the MSquery wizard, that is best, since you can then use fields from the Excel sheet to contain variable parameters to pass through to the query if you need to. If there are no variables to change on the sql query, it doesn't matter then.
You Can also use Ms SQL Server DTS.
Open the Export Wizard and set the source and destination. then enter your Query to run. at the Final Step Save the package as Vbscript.
now you can use this Code in your VB program and Run In your desired Time. you can also schedule it that SQL Server exports your data to Excel file in specified Times.
hope to be useful