Updating Excel data with DSN-less connection.

[travisbrown]

Used to be that I could find instructions on how to set up Excel to update on open from a SQL Server table using a DSNless connection. Now I can't seem to find instructions anywhere.

Can anyone guide me through this?

 

[kjv1611]

Something like this?

http://www.carlprothman.net/Default.aspx?tabid=90

 

[kjv1611]

And a reference direct from Microsoft:

http://support.microsoft.com/kb/165866

 

[travisbrown]

Well, I don't have a problem figuring out the connection string for SS, but have no recollection how to set it up in Excel. And I've seen the KB article from MS. Seems that I once wrote a SUB that executed on open.

I'm trying to figure out where to specify the connection string in Excel.

 

[kjv1611]

Try along these lines, then..

 

[travisbrown]

Yes, thanks.

I've been searching using those keywords for a while before hitting Tek-Tips. Lots of instructions on how to read/write an Excel file from an external scripts, etc. Used to be that I could find amidst all that a couple instructions on how to get an Excel file to auto-update on open. Been years since I've done it, though.

I want to do this from within Excel - similar to the "Get External Data" function, not connect to an Excel file from somewhere else.

 

[kjv1611]

Well, in Excel VBA, I know you can set it up via a connection. I've just never used (that I'm aware of) the type connections you're talking about.

I'd wonder if it'll work via an ADODB connection..

Try searching on that, as there are lots of samples out there.. I've got some, myself, but would have to find it as it's not coming to mind right away at the moment.

 

[SkipVought]

But why DSN-less?

faq68-5829.



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

 

[travisbrown]

Ah, because I have to deliver this Excel sheet to multiple people and I want the Excel file to be populated with the most recent data from a SQL Server DB every time they open it.

I don't want them to have to configure a DSN and it will get passed around and forwarded, etc.

 

[travisbrown]

And MS Query is how I got the data in there first time. I'm just trying to figure out how to modify the connection so it's not dependent on a DSN.

 

[SkipVought]

My FAQ approch is DSN-less.

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

 

[travisbrown]

Thanks Skip.

But isn't your FAQ specifying how to query an Excel sheet, not a SS db? Where would you save the connection string?

 

[SkipVought]

The connection string is embedded in the query object in the Excel sheet.

It uses the same general approch, except that you selct the SQL Server driver, rather than the Excel driver.

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

 

[travisbrown]

Ah. I'm doing something wrong because Excel seems to make me set up a DSN as part of that process.

This is along the lines of what I remember - setting up a connection then editing the connection string to use an ADOBD or Jet driver rather than a file DSN.

 

[dhookom]

I just wrote some code today to modify the connections a co-worker had set up using file DSNs. This code pulls connection string information from ranges on a worksheet.

Public Sub ChangeConnections()
    Dim strConnection As String
    Dim i As Integer
    Dim strServer As String
    Dim strDatabase As String
    Dim strLogin As String
    Dim strPassword As String
    Dim strSQL As String
    ' should add code to make sure each of these have a value
    ' should also add error handling code
    strServer = Range("Server").Value
    strDatabase = Range("database").Value
    strLogin = Range("login").Value
    strPassword = Range("password").Value
    'build the connection string
    strConnection = "ODBC;DRIVER=SQL Server;SERVER=" & strServer & _
        ";ID=" & strLogin & ";Password=" & strPassword & ";Database=" & strDatabase & _
        ";Trusted_Connection=False;"
        
    For i = 1 To ActiveWorkbook.Connections.Count 'loop through all connections
        Debug.Print i & ": " & ActiveWorkbook.Connections(i).Name
        Debug.Print ActiveWorkbook.Connections(i).ODBCConnection.Connection
        Debug.Print ActiveWorkbook.Connections(i).ODBCConnection.CommandText
        Debug.Print "=============================================="
        If i = 4 Then 'test update the cost center connection
            ' command text must remove the reference to the database name
            strSQL = "SELECT CCLNUM, CCLNAM, CCLDES " & _
                    "FROM SCMCCLI " & _
                    "ORDER BY CCLNUM"
            ActiveWorkbook.Connections(i).ODBCConnection.CommandText = strSQL
            ActiveWorkbook.Connections(i).ODBCConnection.Connection = strConnection
            
        End If
    Next
End Sub

Duane
Hook'D on Access
MS Access MVP