Excel (Macro: VBA) - call Sybase Stored Procedure

[robz2009]

I have an Excel workbook with four sheets inside and each one contains the results of a Database query I have setup. The way the table is populated is via a stored procedure in Sybase. I’d like to amend the spreadsheet so that it calls the stored procedure, I can then refresh the queries and the data shown.

I am familiar with writing VBA and done some research and have the following code,

Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
con.Open "DSN"
cmd.ActiveConnection = con
cmd.CommandText = "spname"

But it comes back with a compile error “Invalid outside procedure” highlighting the initial Set statement.

If someone can help\advise on how I can write a procedure in the Excel VBA to call a stored procedure in Sybase.

 

[SkipVought]

Hi,

Have you tried MS Query? In Excel you can do a query by example. If your proc is listed, then it's avalable.

Data > Import External Data > New Database Query... for a start.

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

 

[robz2009]

I already have the queries setup referencing the tables via this option? I cannot see stored procedures via MSQuery?

As stated I'd like to setup the VBA code to call the Stored Procedure. Once I have this sorted I can add some further dialogue.

 

[xlbo]

That error would indicate that your SET statements are not sitting within a sub...end sub routine

should be:

sub whatever()

Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
con.Open "DSN"
cmd.ActiveConnection = con
cmd.CommandText = "spname"

end sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[robz2009]

Apologies, it must have been me being thick and overwriting the start of the sub routine.

This now compiles, thank you. I do have a further issue though - it's still in relation to the post header so thought I'd contiune the post....

It now fails on this line

con.Open "DSN"

I get the error,

Run-time error '-2147467259 (80004005)':

Automation error
Unspecified error

I wish to use my exisiting DSN connection, is this feasible? Or should I specify the full connection string? This is a Sybase database so if anyone can state what the full connection string should be that'd help.

 

[robz2009]

I have tried the alternative of specifying the full connection string but still get the same error.

So if I use,

Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
con.Open "CODADEV"

Or

Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
con.Open "Provider=Sybase.ASEOLEDBProvider;Srvr=SYB_DEV_DS,5000;Catalog=MYDB;User Id=User;Password=password"

I get the same error...

Run-time error '-2147467259 (80004005)':

Automation error
Unspecified error

 

[robz2009]

Ok, I have now got past the connection issues. This code works with no error.

Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
con.Open "DSN=DSN;;DATABASE=DB;SERVER=server,5555;UID=user;PASSWORD=password;;;;"
cmd.ActiveConnection = con

The following code doesn't fail in Excel but if I query Err it states "Object doesn't support this property or method"

cmd.CommandText = "exec stored procedure"

Is this failing or is it a red herring? How can I\should I be running the stored procedure from the VBA in Excel? I have checked the DB and don't see any new connection created ? And the end data table hasn't been re-populated?

 

[robz2009]

Ignore my last post, for some reason when I have closed the spreadsheet and stepped through the process it has successfully connected and called the stored procedure. Very strange!

Thanks for all the input.