Have Excel run a SQL script in query analyzer

[Techfi]

I have a database where I have to run a SQL script daily to get some stats, and then I copy the results from query analyzer and paste them into a spreadsheet. Any way I can have Excel start up query analyzer and run this for me, and bring the results back to the spreadsheet?

 

[DonkeyOte]

why not run the SQL from within Excel in a Macro?

What type of database are you querying? (Access etc..)

 

[Techfi]

It is a SQL2000 database. I didn't know you could run a script from Excel. Tell me more, pleas. Thanks!

 

[DonkeyOte]

I've not tried querying a SQLSvr db just access - in VBA - Tools - References see if you have a library for SQLSvr - this will enable you to create SQLSvr as an object in Excel - at which point you can code as if you were in SQLSvr itself.

Have a look with Search with SQL Server as your subject - you may find an example.

dk or one of the other MVP's may be able to help you out....soz.

Found this...

http://www.mrexcel.com/board2/viewtopic.php?t=43283&highlight=sql+sql+server

 

[NateO]

You can definitely connect to closed Access databases and pass sql strings either using DAO or ADO. Here's an ADO example using the late bind (which is why I use numbers instead of terms from a collection that I don't have reference to):

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> Import()
<SPAN style="color:darkblue">Dim</SPAN> cn <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>, rs <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>, myCalls <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> MySql <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, dbfullname <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, myCnt <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>
dbfullname = "P:\DATA\TestUpdate.mdb"
myCalls = "22" <SPAN style="color:green">'SQL Variable</SPAN>
MySql = "SELECT [BC_Calls], [talk], [work] " & _
    "FROM tblMONTHLY_BASELINE WHERE " & _
    "[BC_Calls]=<SPAN style="color:green">'" & myCalls & "';" 'Stack your SQL string</SPAN>
myCalls = <SPAN style="color:darkblue">Empty</SPAN> <SPAN style="color:green">'Clear SQL variable string</SPAN>

<SPAN style="color:darkblue">Set</SPAN> cn = CreateObject("ADODB.Connection")
cn.<SPAN style="color:darkblue">Open</SPAN> "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
       & dbfullname & ";"  <SPAN style="color:green">'Create DB connection</SPAN>
    
<SPAN style="color:darkblue">Set</SPAN> rs = CreateObject("ADODB.Recordset")
<SPAN style="color:darkblue">With</SPAN> rs
    <SPAN style="color:darkblue">Set</SPAN> .ActiveConnection = cn
    .Source = MySql <SPAN style="color:green">'Pass your SQL</SPAN>
    .<SPAN style="color:darkblue">Open</SPAN> , , 3, 3   <SPAN style="color:green">'.Open , , adOpenStatic, adLockOptimistic</SPAN>
    myCnt = .RecordCount
    <SPAN style="color:darkblue">If</SPAN> myCnt > 0 <SPAN style="color:darkblue">Then</SPAN>
        .MoveLast: .MoveFirst
         <SPAN style="color:green">'Pull data to first sheet, cells a1:RecordestCountRow & column 3 _
            3 fields in the sql pass</SPAN>
        Sheets(1).Range(Cells(1, 1), Cells(myCnt, 3)).CopyFromRecordset rs
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
    .<SPAN style="color:darkblue">Close</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
cn.<SPAN style="color:darkblue">Close</SPAN>
<SPAN style="color:darkblue">Set</SPAN> rs = Nothing: <SPAN style="color:darkblue">Set</SPAN> cn = <SPAN style="color:darkblue">Nothing</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
</FONT>

Using the late bind is less efficient, and you need to know the numerical values of the object's properties. But, you need not set a reference, and there is some merit to this.

I used a variable in the sql statement as an example. You could easily have a variable input from excel (e.g., input box or range object) and pass that to your sql statement....

Not sure if your db supports ADO or DAO... I have no experience with SQL2000, this definitely works with Access '00. If not, I suspect you can create a hidden instance of your object (the database) and work with that. Just make sure you quit the instance (myObj.quit).

Perhaps the example/thoughts are applicable.

 

[poolmwv]

Can you explain this in simple English to someone who has rudimentary Excel knowledge?

In Excel, I have written a query against a DB2 database that will return anything from our inventory that has been updated since the last time it was run (variable number of rows). It is imported as an SQL table and I query a different SQL table to populate 2 more fields (indices based on Asset_Owner and Asset_Type). If there is a typo, the index won't get populated and the update to the SQL database will bomb.

I would like to populate those two fields while still in Excel so that I can eyeball the data before sending it to SQL.

I have copied the code posted here into Visual Basic in Excel and made some changes that I *think* will work (I'm not sure what to do for that Microsoft driver string since I am not going against Access but SQL. I just ommitted it).

Although I programmed in Basic many years ago, this is the first time I have tried using VB in an Office Application. Now what? I feel like I am sitting on a saddle next to a horse and am puzzled why I'm not moving. I'm not even sure I pasted the code into the write place. I just clicked on "View Code" and pasted it.

 

[dk]

Can you explain this in simple English to someone who has rudimentary Excel knowledge?

In Excel, I have written a query against a DB2 database that will return anything from our inventory that has been updated since the last time it was run (variable number of rows). It is imported as an SQL table and I query a different SQL table to populate 2 more fields (indices based on Asset_Owner and Asset_Type). If there is a typo, the index won't get populated and the update to the SQL database will bomb.

I would like to populate those two fields while still in Excel so that I can eyeball the data before sending it to SQL.

I have copied the code posted here into Visual Basic in Excel and made some changes that I *think* will work (I'm not sure what to do for that Microsoft driver string since I am not going against Access but SQL. I just ommitted it).

Although I programmed in Basic many years ago, this is the first time I have tried using VB in an Office Application. Now what? I feel like I am sitting on a saddle next to a horse and am puzzled why I'm not moving. I'm not even sure I pasted the code into the write place. I just clicked on "View Code" and pasted it.

Hi,

Nate's code can easily be modified to work with SQL Server. The beauty of ADO is that once a connection is established i.e. the Connection object is opened, you can use a standard set of methods to access many different data sources. Here is some sample code for connecting to SQL Server. It won't work as it is because I don't know the name of your server, database, etc. and will require some modifying before it does what you need.

I used the excellent resource from here in my code - it has a heap of examples of connecting to various data sources.

PLEASE NOTE : You must open the VB Editor (Alt F11), click Tools, References and select Microsoft ActiveX Data Objects 2.n Library before this code will work.

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> ConnectToSQLServer()
    <SPAN style="color:darkblue">Dim</SPAN> adoCN <SPAN style="color:darkblue">As</SPAN> ADODB.Connection, adoRS <SPAN style="color:darkblue">As</SPAN> ADODB.Recordset
    <SPAN style="color:darkblue">Dim</SPAN> strSQL <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>

    <SPAN style="color:darkblue">Set</SPAN> adoCN = <SPAN style="color:darkblue">New</SPAN> ADODB.Connection


    <SPAN style="color:green"><SPAN style="color:green"><SPAN style="color:green">'///////////////////////////////////////////////////////////</SPAN></SPAN></SPAN>
    <SPAN style="color:green">'CRUCIAL PART - ONCE YOU GET THIS WORKING YOU'RE LAUGHING!</SPAN>

    <SPAN style="color:green">'This part will open a connection to a SQL Server database</SPAN>
    <SPAN style="color:green">'You will need to change the Data Source, Initial Catalog,</SPAN>
    <SPAN style="color:green">'user ID and password to suit your needs</SPAN>

    adoCN.<SPAN style="color:darkblue">Open</SPAN> "Provider=sqloledb;" & _
               "Data Source=myServerName;" & _
               "Initial Catalog=myDatabaseName;" & _
               "User Id=myUsername;" & _
               "Password=myPassword"
    <SPAN style="color:green"><SPAN style="color:green"><SPAN style="color:green">'///////////////////////////////////////////////////////////</SPAN></SPAN></SPAN>


    <SPAN style="color:green">'Now that the connection is open, you can create recordsets and work</SPAN>
    <SPAN style="color:green">'with them in Excel e.g.</SPAN>
    <SPAN style="color:darkblue">Set</SPAN> adoRS = <SPAN style="color:darkblue">New</SPAN> ADODB.Recordset

    <SPAN style="color:green">'Change this SQL statement to whatever you need</SPAN>
    strSQL = "SELECT Field1, Field2 FROM YourTable"

    <SPAN style="color:green">'Open the recordset</SPAN>
    adoRS.<SPAN style="color:darkblue">Open</SPAN> strSQL, adoCN, adOpenForwardOnly, adLockReadOnly

    <SPAN style="color:green">'Copy the recordset into a worksheet (will only work with Excel 2000 onwards)</SPAN>
    ActiveCell.CopyFromRecordset adoRS



    <SPAN style="color:green">'You should disconnect from the database at the end of any code which opens</SPAN>
    <SPAN style="color:green">'it.  Leaving a connection open could cause you problems.</SPAN>
    adoRS.<SPAN style="color:darkblue">Close</SPAN>
    adoCN.<SPAN style="color:darkblue">Close</SPAN>


<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
</FONT>

 

[Monty Latif]

Thanks. Truly appreciate your help.

 

[treewx]

Hey this is awesome ..thanks heaps. .does any one know how to add in a popup to a vaiable into the script??

For instance I need to bring back stock data. . if I have a Pop text box I cold put in "GOOG" and then that would get added into the statement:

SELECT Security, Price
FROM res_db.dbo.Instruments where security = 'GOOG'

Would that be possible?? Thanks

 

[Fazza]

Sub something_like()
  Const sSQL As String = "SELECT Security, Price FROM res_db.dbo.Instruments where security = 'zz'"
  Dim sCode As String
  sCode = Application.InputBox(Prompt:="Stock Code?", Type:=2)
  Debug.Print Replace$(sSQL, "zz", sCode)
End Sub