How to fetch data from database using VBA script

  • Hi Excel Gurus,

    I need to get only some records from
    database, which are in the column A
    of the sheet.

    Currently i am importing all the data
    using database query into one master
    file and then using vlookup to get
    values of those particular records.
    But as i need to do this very often, it is
    very time consuming and want to
    automate it using VBA script.

    The Database has, say columns
    In excel sheet I give values for "PRIM"
    in column "A"

    How to fetch the respective records?

    Thanx in Advance


    Thanks: ~Yogendra

  • Hi Roy,

    May be the problem text became a bit confusing.

    I want to get the data directly from
    the database using ODBC using VBA and not from the Master file.

    If we use master file, what you say,
    i.e. filter can be a good suggestion,
    but there is limitation on the size of
    master file, which i want to avoid
    by getting the data directly from database

    Any other solutions to have a VBA
    script to connect to database
    and fetch only restricted rows
    based on the excel file?

    Thanks: ~Yogendra

  • Hi,

    Sounds that You ´may try an ADO-approach which following procedure show how to create:

    (You need to set a reference to the MS Active Data Objects x.x Library via Tools | Reference in the VB-editor.)

    <FONT&gt;<PRE&gt;<BR&gt;<FONT color=blue&gt;Sub </FONT&gt;Import_AccessData()
    <BR&gt; <FONT color=blue&gt;Dim </FONT&gt;cnt <FONT color=blue&gt;As</FONT&gt;<FONT color=blue&gt; New </FONT&gt;ADODB.Connection
    <BR&gt; <FONT color=blue&gt;Dim </FONT&gt;rst <FONT color=blue&gt;As</FONT&gt;<FONT color=blue&gt; New </FONT&gt;ADODB.Recordset
    <BR&gt; <FONT color=blue&gt;Dim </FONT&gt;stDB <FONT color=blue&gt;As</FONT&gt;<FONT color=blue&gt; String</FONT&gt;
    <BR&gt; <FONT color=blue&gt;Dim </FONT&gt;wsSheet <FONT color=blue&gt;As</FONT&gt; Worksheet
    <BR&gt; <FONT color=blue&gt;Dim </FONT&gt;lnNoField <FONT color=blue&gt;As</FONT&gt;<FONT color=blue&gt; Long</FONT&gt;, lnCount <FONT color=blue&gt;As</FONT&gt;<FONT color=blue&gt; Integer</FONT&gt;
    <BR&gt; <FONT color=blue&gt;Set </FONT&gt;wsSheet = ThisWorkbook.Worksheets("Sheet1")
    <BR&gt; stDB = ThisWorkbook.Path & "" & "XLData.mdb"
    <BR&gt; wsSheet.<FONT color=blue&gt;Range</FONT&gt;("A1").CurrentRegion.Clear
    <BR&gt; cnt.<FONT color=blue&gt;Open </FONT&gt;"Provider=Microsoft.Jet.OLEDB.4.0;" & _
    <BR&gt; "Data Source=" & stDB & ";"
    <BR&gt; rst.<FONT color=blue&gt;Open </FONT&gt;"SELECT * FROM tblNamn", cnt
    <BR&gt; lnNoField = rst.Fields.Count
    <BR&gt; <FONT color=blue&gt;For </FONT&gt;lnCount = 0 <FONT color=blue&gt;To </FONT&gt;lnNoField - 1
    <BR&gt; wsSheet.Cells(1, lnCount + 1).Value = rst.Fields(lnCount).Name
    <BR&gt; <FONT color=blue&gt;Next</FONT&gt;
    <BR&gt;<FONT color=#ccffcc&gt; '******************XL-97***********************************
    </FONT&gt;<BR&gt; <FONT color=blue&gt;Dim </FONT&gt;vaData <FONT color=blue&gt;As</FONT&gt;<FONT color=blue&gt; Variant</FONT&gt;
    <BR&gt; <FONT color=blue&gt;Dim </FONT&gt;lnRow <FONT color=blue&gt;As</FONT&gt;<FONT color=blue&gt; Long</FONT&gt;, lnCol <FONT color=blue&gt;As</FONT&gt;<FONT color=blue&gt; Long</FONT&gt;, lnPost <FONT color=blue&gt;As</FONT&gt;<FONT color=blue&gt; Long</FONT&gt;, lnField <FONT color=blue&gt;As</FONT&gt;<FONT color=blue&gt; Long</FONT&gt;
    <BR&gt; vaData = rst.GetRows()
    <BR&gt; lnPost = UBound(vaData, 2) + 1
    <BR&gt; lnField = UBound(vaData, 1) + 1
    <BR&gt; <FONT color=blue&gt;For </FONT&gt;lnRow = 2 <FONT color=blue&gt;To </FONT&gt;lnPost
    <BR&gt; <FONT color=blue&gt;For </FONT&gt;lnCol = 1 <FONT color=blue&gt;To </FONT&gt;lnField
    <BR&gt; Cells(lnRow, lnCol).Value = vaData(lnCol - 1, lnRow - 2)
    <BR&gt; <FONT color=blue&gt;Next</FONT&gt;
    <BR&gt; <FONT color=blue&gt;Next</FONT&gt;
    <BR&gt;<FONT color=#ccffcc&gt; '******************XL- 2000 / 2002 ***************************
    </FONT&gt;<BR&gt; wsSheet.Cells(2, 1).CopyFromRecordset rst
    <BR&gt; <FONT color=blue&gt;Set </FONT&gt;rst =<FONT color=blue&gt; Nothing</FONT&gt;
    <BR&gt; <FONT color=blue&gt;Set </FONT&gt;cnt =<FONT color=blue&gt; Nothing</FONT&gt;
    <BR&gt;<FONT color=blue&gt;End Sub</FONT&gt;

  • Thanx a million:flower:

    Can you tell me how to amend the
    code to get Oracle Connectivity instead
    of access, because, there is
    nothing like file or path that can be set
    for the database?



    Thanks: ~Yogendra

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!