Importing external data into Excel using VBA

[OMJ]

Hi,

I have successfully managed to connect to a database on a server, and NOT using the wizard I executed the below query in Microsoft Query then return the data to Excel.

select * from projects
where team = 'TEAM1'
and startdate >
to_date( '01-apr-2007 00:00:00', 'dd-mon-yyyy hh24:mi:ss' )

However, I'd like to be able to program this in VBA instead. But I have no idea how. Could anybody please give me some pointers? I just want it to be so when the macro is run, it connects to the DB and then runs that query, returning it to Excel.

Really would appreciate some help! Many thanks if you can!

 

[PHV]

Did you macro record what you did manually ?
Tip: have a look at QueryTable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[OMJ]

I wasn't able to record it manually as macro. By the way I forgot to mention in my first post that the DB I'm connecting to is an Oracle DB if that makes any difference (sorry if that sounds stupid, am really new to all this).

 

[SkipVought]

Hi,

Now that you have inserted a QueryTable in a sheet in your workbook, select in the QT, turn on the macro recorder and record, Data > Import External Data > Edit Query... navigate to the query grid and File > Return data to Excel. Turn of your recorder and observe your generated code.

Post back with your recorded code if you need further help.

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[taupirho]

Some code below for a typical ODBC connection to Oracle. Supply your own ids,passwords,hostnames, tablenames etc


Dim db_data_qt As Connection

Dim rs_qt As Recordset

Set db_data_qt = New Connection

Set rs_qt = New Recordset

db_data_qt.Open "Driver={Microsoft ODBC for Oracle};Server=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=your.host.IP.number)(PORT=1521)))(CONNECT_DATA=(SID=mysid)));uid=myuserid;pwd=mypassword; "

rs_qt.Open "select * from yourtable", db_data_qt, adOpenStatic, adLockOptimistic

If rs_qt.RecordCount >= 1 Then
rs_qt.movefirst
for i = 1 to rs_qt.recordcount
cells(i,1) = rs_qt.fields(0).value
rs_qt.movenext
next
end if




In order to understand recursion, you must first understand recursion.

 

[OMJ]

Just wanted to say thanks guys! Have managed to, thanks to your help, solve this one the other day! :-D