Add linked server at Excel VBA

I had SQLDMO at reference library.

Private Sub test()
Dim s As SQLDMO.SQLServer
Dim ls As SQLDMO.LinkedServer
Set s = New SQLDMO.SQLServer
s.Connect "Server1", "ID", "Password"
Set ls = New SQLDMO.LinkedServer
With ls
.Name = "Server2"
.ProviderName = "SQLOLEDB"
.DataSource = "Server2"
' .ProviderString = ""

End With
s.LinkedServers.Add ls
s.Close

'End Sub

I block ProviderString as i don't know what is it.

I got an error message when it's running the line "s.LinkedServers.Add ls" that

"Run-time error '-2147206257 (80043b8f)':

Automation error"

My question:

1. What is providerstring? what should be put here?

2. How to fix the error

Hi there,

Provider string should look something like the following:

@.provstr=N'DRIVER={<driver name of your linked server, in this case Excel} ;SERVER=<address if your server where the driver resides>;DATABASE=<database name>; USER=<username>; PASSWORD=<password>; OPTION=3'

I found this here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=304918&SiteID=1

Also, this may help: http://www.databasejournal.com/features/mssql/article.php/10894_3085211_2

Since you're not executing within the QA of management studio, I'm guessing that you only need to put Excel's driver name in your provider string, but don't take my word as I haven't tried doing this in VB.

Hope this helps!

Isa