hello all,

looking for advice for the best way to view information from an sql database.
i need the info to be refreshed everytime its open. the excel sheet will be stored on a sharepoint so that anytime anyone opens it the table refreshes with the data.

its the best way to do this... VBA SQL code?..

You can open Sharepoint lists directly in Excel. Any reason not to do that?

There are ways to do it "by hand". You don't want to try them in VBA unless you have to.


What does this have to do with SQL btw? Is there another part of this solution not yet described?

thanks for the reply. ill try give more detailed information as what i want to do.

Data is entered in a website and stored in a MS SQL Server.
I want to access this data (i have the connection string and passwords) and show in an excel sheet so that anyone on my network can view it and add comments to the data in the excel file.

when the excel file is opened, i want the data to refresh so the latest data can be seen

Ah, I get it.

You should be able to setup an "import" over OLEDB or however you want to connect. There's nothing special about it being in Sharepoint other than it might complain about rights to modify the excel sheet depending on how you setup permissions.

the reason i mentioned sharepoint is that this is where i will be storing it on the network so everyone has access to it.

just in regards to "importing" over OLEDB....

will this allow anyone to open the excel file in the sharepoint location, and refresh the data? does oledb not tie it to your own pc?

Nope, OLEDB is fine.

DSN-based ODBC would tie it to a specific machine due to requiring a DSN entry.

ok thanks but when i try to import using 'from other sources.. from data connection wizard (import data for an unlisted format by using the data connection wizard and OLEDB).. then click Microsoft SQL Server.. then i enter the server name (and username and password).. then select the database and table.. then hit finish.....

the connection file is stored on my C drive??

if i want other people to be able to open this and refresh the data, it wont work because it will reference my C drive?

am i doing something wrong?

I see now. 2007 puts your balls in a vice and tries to force you to never embed any data connection information.

Here's a way around it: Create the connection file and save it like you normally would. Then, click "connections", highlight the connection you created and click "properties.

Switch over to the "definition" tab. Save the connection string in notepad or something, then type some random text into the connection string box and click "ok".

It will yell at you that the connection string is no longer identical to the file and will be stored in the spreadsheet. Obviously this is acceptable. This will clear the connection file association and you are then free to paste in the correct connection string again.


what a POS.

mmmmmm.. thats annoyin alright.

could you help me code using VBA where i could store the connection string, username, password and SQL code and put the data in A1?

here is what i have done.. but its not working.. (keep in mind im only starting VBA!!)


Sub DataExtract()


Dim cnData As ADODB.Connection
Set cnData = New ADODB.Connection
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "OLEDB;Provider=SQLOLEDB.1;" & _
"Data Source=***.com\SQL01,1433;" & _
"Password=****;Persist Security Info=True;" & _
"User ID=****;"


cnData.Open strConn

Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset

With rsData
' Assign the Connection object.
.ActiveConnection = cnData
' Extract the required records.
.Open "SELECT * FROM *

' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rsData

' Tidy up
.Close
End With

cnData.Close
Set rsData = Nothing
Set cnData = Nothing