Hi

Try the following,
1.
Turn on the VBA Record Button from Excel

Go to Data>Get External Data>New Database Query.

Then Select a Database.

Stop the Recorder.

*Note that before you select a Database, ensure that you hve a DSN Setup.

You Can setup a DSN by Going to Control Panel and Selecting a Database.


2. Then yiou need to assign a button to the sheet via the Control Toolbox.

-then call the macro from the button


some sample code for you.

This is the SQL/VB code inside a VBA Macro


Sub Query_Download()

Sheets("TNR").Select

Cells.Select
Cells.Clear


With ActiveSheet.QueryTables.Add( _
Connection:="ODBC;DRIVER=SQL Server;SERVER=;UID=;PWD=;APP=Microsoft Open Database Connectivity;" & _
"DATABASE=TNR", Destination:=Range("A1"))

''Execute the SQL script to retrieve the date''



.CommandText = "SELECT Name" & _
"FROM Database"

.Name = "List"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True

End With


Range("B1").Select
ActiveCell.FormulaR1C1 = "Match"
Range("c1").Select

End Sub


*Note that you would have to assign a value to Servwer, USerID and Password(Server is the IP Address)

This code won't work on your PC, but it is a sample


Originally posted by lh2001
Hi,

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.