How to fetch data from database using VBA script

yjoshi · Mar 6th 2003

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
PRIM,B,C,D&E.
In excel sheet I give values for "PRIM"
in column "A"

How to fetch the respective records?

Thanx in Advance

:flower:

**royUK** · Mar 6th 2003

I think you should look at AutoFilter or Advanced Filter. This can be achieved using VBA.

yjoshi · Mar 6th 2003

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?

XL-Dennis · Mar 6th 2003

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

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

yjoshi · Mar 6th 2003

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?

Regards

Yogendra

XL-Dennis · Mar 6th 2003

Hi Yogendra,

I dont use Oracle but following page shows how to set up an ADO-connection with a an Oracle-database:

http://www.able-consulting.com/ADO_Conn.htm

And You may also have a look on following utility that make it more smoother to establis connections etc:

http://www.oraxcel.com/projects/sqlxl/index.htm

Good Luck!

How to fetch data from database using VBA script

Participate now!

Share