Trying to get data from SQL Server (MSDE) database with Excel 97

S

Stevie_mac

Trying to get data from SQL Server (MSDE) database with Excel 97
The 1st part fails with ...
Runtime error 1004
Application-defined or object-defined error

'Create external data range
Set objMyQueryTable = ActiveSheet.QueryTables.Add( _
Connection:="OLEDB;Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=MyData;Data Source=localhost", _
Destination:=Range("a1")) **** Fails On This Line ****

'Retrieve data
With objMyQueryTable
.CommandText = "Select * From MyData"
.CommandType = xlCmdSQL
.Name = "MyData"
.Refresh False
End With

I know the connection string is good - I set up a udl file with the same Connection String
I can get this to work with DSN and URL Connection Strings but not OLEDB

Any clues? (PS, using a DSN is not an option since this will be opened on lots of different machines)
Or any other suggestions getting data from MSDE into excel
 
G

Guest

you may want to start with recording a macro the import of data from the database and then review the code
good luc
kanan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Import Data from SQL server to excel 0
Extract data from SQL Server table into excel using Listbox Selection as criteria 0
SQL ODBC Connection 1
Excel - VBA - QueryTable error 1004 0
OLEDBConnection Properties 1
Data query refresh problem with changing filenames 1
access selective data...using query 5
Excel AddNew QueryTable '1004' problem 1

Top