excel vba - How to use SQL Select Query in VBA code? -

i have 1 query of sql, want put in vba code bring result in excel sheet.

i tried code pasted below.

sub connect2sqlxpress()  dim ors object dim ocon object  set ocon = createobject("adodb.connection")  set ors = createobject("adodb.recordset")  set ocon = new adodb.connection ocon.connectionstring = "provider=sqloledb.1;integrated security=sspi"     ocon.open ors.source = "select keyinstn,longname,shortname,shortestname new_inst ......"   ors.open range("a1").copyfromrecordset ors ors.close ocon.close  if not ors nothing set ors = nothing  if not ocon nothing set ocon = nothing 

can please me in figuring out going wrong ??

to create connection string try this:

1. right-click on desktop (or folder)
2. choose new > textfile
3. right-click on file have created, , change it's name (and extension) connectioninfo.udl
4. double-click on file connectioninfo.udl , should open in microsoft data - core services.
5. set provider, servername, , database click test connection before clicking
6. right-click on file, , time choose open notepad
7. you should see proper ole db connection string, can copy vb code.

after that, need ensure select query correct, running in sql server management services or whatever see results.

once have recordset open in excel, use copyfromrecordset method of range object dump recordset excel