You are here:

Excel/Extracting Data from SQL server



I trust I find you well

Please may you assist

I would like to extract data from a SQL server. The data should meet criteria specified in excel on worksheet "Query". Extracted data should be put on excel worksheet "Query_Results".

The code I have is as follows;

Sub Extract_Data_from_SQL_server()
   Dim cnt As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim stSQL As String
   Dim wbBook As Workbook
   Dim wsSheet As Worksheet
   Dim rnStart As Range
   Dim serveraddress As String
   Dim database As String
   Dim UserID As String
   Dim Password As String
'Assigning excel values needed to open database
   serveraddress = [serveraddress]
   database = [database]
   UserID = [UserID]
   Password = [Password]
Const stADO As String = "Server=serveraddress;Database=database;UID=UserID;Pwd=Password"
   Set wbBook = ActiveWorkbook
   Set wsSheet = wbBook.Worksheets(1)
   With wsSheet
       Set rnStart = .Range("A1")
   End With
   stSQL = "SELECT * FROM Settings"
   Set cnt = New ADODB.Connection
   With cnt
       .CursorLocation = adUseClient
       .Open stADO
       .CommandTimeout = 0
       Set rst = .Execute(stSQL)
   End With
'Here we add the Recordset to the sheet from A1
   rnStart.CopyFromRecordset rst
'Cleaning up.
   Set rst = Nothing
   Set cnt = Nothing
End Sub

Hi Brian,

Can you specify the part where you need help? Whether you are having trouble connecting to the database or getting the date of is it with the accuracy of data or excel handling of the data.

Depending on your response we can see if this question can be handled by an excel expert or do we need to find a SQL expert.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts




I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.


I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2017 All rights reserved.