You are here:

Excel/Extracting Data from SQL server

Advertisement


Question
Hi

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.
   rst.Close
   cnt.Close
   Set rst = Nothing
   Set cnt = Nothing
    
End Sub

Answer
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.

Thanks,
Gulshan.
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

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.

Experience

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

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.