Web Services using vba

[Guest]

Is it possible to consume web services in Excel using vba or do you need to
use .NET?

 

[Fredrik Wahlgren]

Is it possible to consume web services in Excel using vba or do you need to
use .NET?

Maybe. I wouldn't recommend it. I think you would need VB6 or .Net code that
in turn calls Excel. Microsoft has recommended against using Excel as a
server application. Can you please tell more about what you want to do?

/Fredrik

 

[Guest]

Thanks for the reply. I used to be able to connect directly to a SQL Server
database and bring the data automatically into Excel but now I have to go
through a terminal server to access the data so I was hoping to create web
services to the data on the SQL Server and consume them in Excel.

 

[Dave Patrick]

You should be able to use ADO

Dim strSQL1 As String
strConnect1 = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=MyDatabase;Data Source=MyServer"
Dim cnn1 As ADODB.Connection
Set cnn1 = New ADODB.Connection
cnn1.Open strConnect1
Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset

strSQL1 = "SELECT lastname, firstname FROM tbl_names "

rs1.Open strSQL1, cnn1, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
do something here

rs1.MoveNext
Loop
rs1.Close
cnn1.Close

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Thanks for the reply. I used to be able to connect directly to a SQL
Server
| database and bring the data automatically into Excel but now I have to go
| through a terminal server to access the data so I was hoping to create web
| services to the data on the SQL Server and consume them in Excel.
|
| "Fredrik Wahlgren" wrote:
|
| >
| > | > > Is it possible to consume web services in Excel using vba or do you
need
| > to
| > > use .NET?
| > > --
| > > djb
| >
| > Maybe. I wouldn't recommend it. I think you would need VB6 or .Net code
that
| > in turn calls Excel. Microsoft has recommended against using Excel as a
| > server application. Can you please tell more about what you want to do?
| >
| > /Fredrik
| >
| >
| >

 

[Dave Patrick]

Of course I forgot to;
Dim strConnect1 as String

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

 

[Fredrik Wahlgren]

Dave's suggestion is probably waht you need. If you can create a connection
to your SQL server on your local computer, you won't need Citrix. In my
opinion, using ADO in vba will make it easy and flexible to get data. The
only problem could be the connection string. You can create an empty text
file and change the extension to udl if your PC is set to show them. The
icon will change. When you doubleclick on it, there will be a wizard that
helps you to build an connection.

You can either use the udl file in your code or change the extension to txt
which will reveal the connection string.

/Fredrik

 

[Dave Patrick]

On the web service possibility here's an article in my last months issue of
MSDN though I've not tried any of it as yet.

http://msdn.microsoft.com/msdnmag/issues/05/02/ExcelWebServices/default.aspx

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Dave's suggestion is probably waht you need. If you can create a
connection
| to your SQL server on your local computer, you won't need Citrix. In my
| opinion, using ADO in vba will make it easy and flexible to get data. The
| only problem could be the connection string. You can create an empty text
| file and change the extension to udl if your PC is set to show them. The
| icon will change. When you doubleclick on it, there will be a wizard that
| helps you to build an connection.
|
| You can either use the udl file in your code or change the extension to
txt
| which will reveal the connection string.
|
| /Fredrik

 

[Guest]

This is the way I used to be able to connect but can no longer because I am
going through a terminal server that doesn't have Excel on the server so I
would have to have a way to send credentials, not only to the particular
database, but to log into terminal server and get the data back thru terminal
server...which I do not know if there is a way to do this. Thus..the reason I
was thinking it may be best to try to use a web service to connect to the
data through http.

djb

 

[Guest]

Dave, I think this article is exactly what I am looking for. Thanks so much
for your help and pointing me to this info!
djb

 

[Dave Patrick]

Glad to hear it helps. You're welcome.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Dave, I think this article is exactly what I am looking for. Thanks so
much
| for your help and pointing me to this info!
| djb

 

[Fredrik Wahlgren]

Is it possible to consume web services in Excel using vba or do you need to
use .NET?

Here's a link that I found which I think you will find useful
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnasdj01/html/asp0193.asp

/Fredrik

 

[Thomas Risi]

There is an addin from MS for the vba-ide. Look for "Microsoft Office 2003
Web Services Toolkit für Visual Basic for Applications" (and XP) ...