Nick, that is a great tip, I have not come accross that one before.

Thanks

MH

"NickHK" wrote in message
...
Phil,
You can create/test connection string with this method:
- Create a new text file somewhere suitable.
- Rename to Whatever.udl
- Double click and follow the wizard, filling in info
- Click OK.
- Open the file in a text editor and copy the connection string.

MZ-Tools uses this method in its add:
http://mztools.com/index.htm

NickHK

"Phil" wrote in message
...
Hi MH,

Yes it is with 2003, I tried your suggestion and get the same problem, I
hace the following error messages :

Connection failed:
SQLState: '01S00'
[Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
Connection failed:
SQLState: '08004'
SQL Server Error: 4060
Server rejected the connection, Access to selected database has been
denied

Sorry about that, hope that this is a little more informative

Thanks PD

"MH" wrote:

SQL Server 2003?

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={SQL
Server};Server=sqlserver;UID=ME;pword=PASSAPP=Micr osoft®

Query;WSID=sqlserver;DATABASE=("RISP-MIS");Network=DBMSSOCN;Trusted_Connecti
on=Yes"
_
, Destination:=Range("A1"))
.CommandText = Array("select * from dtproperties")

The WITH block in the above snippet should read:

With ActiveSheet
.QueryTables...

I suspect that is why your code is failing but without any error
messages to
go on it's hard to guess!

MH

"Phil" wrote in message
...
Hi,

Hope someone can help, I am trying to pull data into Excel from SQL
Server
2003 and my script keeps failing, I think it might have something to
do
with
the way that I am construting my access string and specificaly the
database
part as I am not sure if it needs to be surrounded by either brackets
or
""
tried both ways but not getting anywhere.

Range("A2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={SQL
Server};Server=sqlserver;UID=ME;pword=PASSAPP=Micr osoft®

Query;WSID=sqlserver;DATABASE=("RISP-MIS");Network=DBMSSOCN;Trusted_Connecti
on=Yes"
_
, Destination:=Range("A1"))
.CommandText = Array("select * from dtproperties")
Debug.Print .CommandText
.Name = "Sheet1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True

Again, hope someone can help

Thanks PD