Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have one query of SQL, which I want to put in a VBA code to bring result in Excel sheet.

I tried the code pasted below.

Sub Connect2SQLXpress()

Dim Ors As Object Dim oCon As 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 From New_inst WHERE ......" 

Ors.Open Range("A1").CopyFromRecordset Ors
Ors.Close oCon.Close

If Not Ors Is Nothing Then Set Ors = Nothing 
If Not oCon Is Nothing Then Set oCon = Nothing

Can someone please help me in figuring out that what is going wrong ??

share|improve this question
what is the error you are getting? – Philip Jul 11 at 11:02
1  
Well, it would help a great deal if you told us what wasn't working. You haven't indicated what the problem is yet. "Here's some code. What's wrong?" doesn't describe the problem very clearly. Can you edit your question and add some information about what the actual issue is you're having? (It will be useful for people who are trying to find an answer to a similar problem in the future.) Thanks. – Ken White Jul 11 at 11:35
@KenWhite: from looking at it I'm guessing the SQL Query is wrong, and the Connection string is wrong (no database or server!) – Philip Jul 11 at 11:59
@user2563891: For help on opeing ADO Connections, take a look at MSDN Library: Connecting to a SQL Server Data Source and W3Schools: ADO Tutorial – Philip Jul 11 at 12:04
@Philip: The poster is asking for help here; we shouldn't have to guess what the problem is they want our help with, when they have that information. It also doesn't do any good for future readers here when searching, because the search engine can't guess at what the question is about. :-) – Ken White Jul 11 at 12:28
show 2 more commentsadd comment (requires an account with 50 reputation)

1 Answer

up vote 0 down vote

to create a good connection string try this:

  1. Right-click on your desktop (or any folder)
  2. choose New > textfile
  3. Right-click on the file you have created, and change it's name (AND EXTENSION) to ConnectionInfo.UDL
  4. Double-click on the file ConnectionInfo.UDL and this should open in Microsoft data - Core Services.
  5. Set your Provider, ServerName, and Database then click Test Connection before clicking
  6. Right-click on the file, and this time choose to Open it with Notepad
  7. You should see a proper OLE DB Connection string, that you can copy into your VB Code.

After that, you need to ensure your SELECT Query is correct, by running it in SQL Server Management Services or whatever to see that you get results.

Once you have a recordset open in Excel, you could use the CopyFromRecordset method of the range object to dump your recordset into Excel

share|improve this answer
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.