How to write Macro in Excel i.e in Visual Basic Editor to get the data from external datasource i.e (MS Acess or Oracle)
Please give the code.
Printable View
How to write Macro in Excel i.e in Visual Basic Editor to get the data from external datasource i.e (MS Acess or Oracle)
Please give the code.
the following code reads an ODBC.Datasource:
Sub Abfrage()
Dim Cnn as new ADODB.Connection
Dim Rs as new ADODB.Recordset
Dim Cmd as new ADODB.Command
Dim i as Integer
Dim r as Range
Cnn.Open ("DSN=AnyName") 'use the DSN from ODBC
set Cmd.ActiveConnection = Cnn
Cmd.CommandText = "SELECT * FROM AnyTable" 'SQL-Command
RtRs.CursorLocation = adUseClient
RtRs.Open RtCmd, , adOpenDynamic, adLockOptimistic
Rs.MoveFirst
i = 1
Do While Not Rs.EOF
i = i + 1
Cells(i, 1).Value = RtRs!AnyColum ' must be a valid Colum from your Database
Rs.MoveNext
Loop
Cnn.Close
end sub
Thanks for giving reply intime.
Thanks a lot