Hi,

I am trying to link Excel to Peoplesoft to be able to import/update data automatically via Excel by using a macro/VBA. I found the following connection string but I am having trouble getting it to work:

Public gCn As New ADODB.Connection

Private Function GetCon() As ADODB.Connection
If gCn.State = adStateClosed Then

With gCn

.ConnectionString = "Driver={Microsoft ODBC for
Oracle};Server=<dbname>;Uid=<userid>;Pwd =<password>;"

.Open

End With
End If
End Function

Then you can run a query on a button click of Activate:

Private Sub CommandButton1_Click()
Dim rs As New ADODB.Recordset
Dim sSql As String
Dim i As Integer
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Range("A4").Select

GetCon
MousePointer = 11
DoEvents
sSql = "<put SQL here>"
With rs

.ActiveConnection = gCn

.CursorType = adOpenDynamic

.LockType = adLockOptimistic

.CursorLocation = adUseClient

.Open sSql, gCn, , , adCmdText
End With
If Not rs.BOF Or Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF

For i = iStart To rs.RecordCount - 1
DoEvents

Worksheets("Sheet1").Cells(i + 4, 1).Value = rs(0)

Worksheets("Sheet1").Cells(i + 4, 2).Value = rs(1)

Worksheets("Sheet1").Cells(i + 4, 3).Value = rs(2)

Worksheets("Sheet1").Cells(i + 4, 4).Value = rs(3)

Worksheets("Sheet1").Cells(i + 4, 5).Value = rs(4)

rs.MoveNext
Next i
Loop
End If
rs.Close
Set rs = Nothing
gCn.Close
Set gCn = Nothing
MousePointer = 0
MsgBox "DONE"
Worksheets("Sheet1").Cells(1, 7).Value = CStr(Now())
End Sub

Could someone please help me getting this to work? Thanks in advance.

Marald