Search in SQL DB - pull data to Excel

[espenskeie]

Hi

I have a database with stock quotes, 75 million rows. Is it possible to make a search in this database and pull out data in a range, for example the stock MSFT (first column in DB), date 1/25/07 to 10/25/12 (second column in DB)?

Here is what I have so far, but this will pull the whole DB at 75 million rows, and I don't think Excel will handle that very well.

Sub Import_Stock()


    Dim Cn As ADODB.Connection
    Dim ServerName As String
    Dim DatabaseName As String
    Dim TableName As String
    Dim UserID As String
    Dim Password As String
    Dim rs As ADODB.Recordset
    Dim RowCounter As Long
    Dim ColCounter As Integer
    Dim NoOfFields As Integer
    Dim StartRow As Long
    Dim EndRow As Long
    Dim shtSheetToWork As Worksheet
    Set shtSheetToWork = ActiveWorkbook.Worksheets("Sectors")
    Set rs = New ADODB.Recordset
    Dim lr As Long
    
    ServerName = "ESPENS-NYE-PC\SQL2008E" ' Enter your server name here
    DatabaseName = "pairTradeFinder" ' Enter your  database name here
    TableName = "stock" ' Enter your Table name here
    UserID = "" ' Enter your user ID here
     ' (Leave ID and Password blank if using windows Authentification")
    Password = "" ' Enter your password here
    NoOfFields = 18 ' Enter number of fields to update (eg. columns in your worksheet)
    StartRow = 2 ' Enter row in sheet to start reading  records
    EndRow = shtSheetToWork.Cells(Rows.Count, 1).End(xlUp).Row ' Enter row of last record in sheet


    Dim connection_string As String
    connection_string = "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=" & DatabaseName & ";Trusted_connection=yes;" 'UID='" & UserID & ";Password='" & Password & ";"
           
    Set Cn = New ADODB.Connection
    Cn.Open connection_string


    rs.CursorLocation = adUseClient
    rs.Open TableName, Cn, adOpenDynamic, adLockOptimistic
    
    Sheets("StockFK").Cells.Clear
    
    'Read from SQL and import to Excel
    With Worksheets("StockFK").Range("A1:Z5000") ' Enter your sheet name and range here
        .ClearContents
        .CopyFromRecordset rs
    End With
    
    With Worksheets("StockFK")
    lr = .Cells(.Rows.Count, "A").End(xlUp).Row
    ForeignKey = .Range("A" & lr).Value
    End With
    
    Sheets("Temp").Range("A1").Value = ForeignKey
    
     ' Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing


End Sub

Kind regards
Espen

 

[Akashwani]

Hi Espen,

I don't understand or write VBA, so I cannot help you with your problem, sorry.
However, have you looked at Power Pivot?.....

Download Microsoft® SQL Server® 2012 PowerPivot for Microsoft® Excel® from Official Microsoft Download Center

There is a video on this page regarding downloading from a SQL server database...
PowerPivot | Microsoft BI

MrExcel has numerous videos on Power Pivot, example here....

PowerPivot Data Analyst 2 - Importing Data to PowerPivot - YouTube

Type Power Pivot into the search box on the MrExcel YouTube page and take a look at what he has to offer.

I hope that helps.

Good luck.

Ak

 

[espenskeie]

Thanks, I'll take a look at it and see if it can be modified to fit my use.

Kind regards
Espen