Macro to get data from another application

[j844929]

Can anyone help? Is it possible to create a macro that basically goes into Business Objects, copies a table, and then pastes it into a specified worksheet in Excel?

 

[ChrisUK]

Yes!

It would be better to get the information from the underlying table rather than BO. Here is some code I've used to query a table accessed through ODBC.

Hope this helps

....................................

Dim SQLStatement
Dim ConnectString
Dim MyODBCConnection

MyODBCConnection = "UsersDB"

' Open connection
' You need to set the ADO reference up in the Tools/Refence menu (Microsoft Actice X data objects Library)
Set ConnectDB = New ADODB.Connection
ConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=" & MyODBCConnection
ConnectDB.Open ConnectString

' Open recordset from database table
Set rsNames = New ADODB.Recordset
rsNames.CursorLocation = adUseClient

' Use client cursor to enable AbsolutePosition property
SQLStatement = "SELECT * from testtable"
rsNames.Open SQLStatement, ConnectDB, adOpenStatic, adLockReadOnly, adCmdText

' Setup a counter to run down the spreadsheet rows
LineCount = 1

' Loop through returned records
Do While Not rsNames.EOF
Worksheets("Sheet1").Cells(LineCount, 1).Value = rsNames.Fields("NameField")
Worksheets("Sheet1").Cells(LineCount, 2).Value = rsNames.Fields("DetailsField")
LineCount = LineCount + 1
rsNames.MoveNext
Loop

Set ConnectDB = Nothing
Set rsNames = Nothing

 

[j844929]

What a star! Might take me a while to get there, but I'm much closer now! Thanks