Macro to get data from another application

j844929

Active Member
Joined
Aug 18, 2002
Messages
423
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?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
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

Active Member
Joined
Aug 18, 2002
Messages
423
What a star! Might take me a while to get there, but I'm much closer now! Thanks
 

Forum statistics

Threads
1,181,687
Messages
5,931,435
Members
436,789
Latest member
portugeezers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top