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?

 

[treewx]

Great thanks for that.. I'm just not sure how that fits in with the rest of the query?? Sorry I forgot to mention . .I'm using the below:

Sub ConnectToSQLServer()
Dim adoCN As ADODB.Connection, adoRS As ADODB.Recordset
Dim strSQL As String
Set adoCN = New ADODB.Connection

'///////////////////////////////////////////////////////////
'CRUCIAL PART - ONCE YOU GET THIS WORKING YOU'RE LAUGHING!
'This part will open a connection to a SQL Server database
'You will need to change the Data Source, Initial Catalog,
'user ID and password to suit your needs
adoCN.Open "Provider=sqloledb;" & _
"Data Source=ertyerty;" & _
"Initial Catalog=res_db;" & _
"User Id=ASDFGG;" & _
"Password= "ertyertyerty''
'///////////////////////////////////////////////////////////

'Now that the connection is open, you can create recordsets and work
'with them in Excel e.g.
Set adoRS = New ADODB.Recordset
'Change this SQL statement to whatever you need
strSQL = "SELECT Security, * FROM res_db.dbo.Instruments where security = 'MSFT.US'"
'Open the recordset
adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
'Copy the recordset into a worksheet (will only work with Excel 2000 onwards)
ActiveCell.CopyFromRecordset adoRS

'You should disconnect from the database at the end of any code which opens
'it.Leaving a connection open could cause you problems.
adoRS.Close
adoCN.Close

End Sub

 

[treewx]

Sorry to Bump this. .but does anyone have any idea on how to made this code work as described above?? Thanks in advance!

 

[Fazza]

Hi, I don't know about making the code work as described. I'm just answering about inserting the code in the code you posted. Suggest you highlight the code with the mouse, use CTRL-C to copy, then in the code window paste (CTRL-V). cheers

Sub ConnectToSQLServer()
  Dim adoCN As ADODB.Connection, adoRS As ADODB.Recordset
  Dim strSQL As String
  Set adoCN = New ADODB.Connection
  '///////////////////////////////////////////////////////////
  'CRUCIAL PART - ONCE YOU GET THIS WORKING YOU'RE LAUGHING!
  'This part will open a connection to a SQL Server database
  'You will need to change the Data Source, Initial Catalog,
  'user ID and password to suit your needs
  adoCN.Open "Provider=sqloledb;" & _
      "Data Source=ertyerty;" & _
      "Initial Catalog=res_db;" & _
      "User Id=ASDFGG;" & _
      "Password= "ertyertyerty    ''
  '///////////////////////////////////////////////////////////
  'Now that the connection is open, you can create recordsets and work
  'with them in Excel e.g.
  Set adoRS = New ADODB.Recordset
  'Change this SQL statement to whatever you need
  strSQL = "SELECT Security, * FROM res_db.dbo.Instruments where security = 'MSFT.US'"
 
 
'##################################################
  'THIS IS WHERE I'VE INSERTED THE NEW CODE 20-JAN-11
  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)
  strSQL = Replace$(sSQL, "zz", sCode)
  '##################################################
 
 
  'Open the recordset
  adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
  'Copy the recordset into a worksheet (will only work with Excel 2000 onwards)
  ActiveCell.CopyFromRecordset adoRS
  'You should disconnect from the database at the end of any code which opens
  'it.Leaving a connection open could cause you problems.
  adoRS.Close
  adoCN.Close
End Sub