All

I am using Excel 2007 to generate a dashboard with information on a Sharepoint list. The list and Excel file are changed very frequently and I must be sure users are using the latest version. That's why I created an extra Sharepoint list which only contains a version number. I now need some VBA-code to check this. I do not want to use DoCmd.TransferSharePointList

I found some partial code on the internet, but it gives me an automation error on the "rs.open"-statement. According information I have found, the SQL-string should be correct.

To retrieve the List-ID, I went to the Sharepoint List Settings. In the URL I copied everything behind the word list. Then I browsed to http://www.albionresearch.com/misc/urlencode.php, I put the string in "Encoded", pressed on URLDecode and used the plain string as List ID.

Furthermore, the full URL of the sharepoint list is something like
http://sharepoint.company.com/compan.../AllItems.aspx

Can someone help me? 

Private Sub Workbook_Open()
Dim strVersion As String
Dim strTekst, strTitel As String
Dim strDummy As Variant
Dim ie As Object
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

strVersion = "2012-03-04-15:00"   'This is the version of the current Excel workbook.


con.Open ("Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=http://sharepoint.company.com;LIST={783ACD12-55DD-4385-8A6B-C06915A297D3}")
rs.Open "SELECT * from list", con, adOpenDynamic, adLockBatchOptimistic
If rs![version check] <> strVersion Then
        strTitel = "Version conflict"
        strTekst = "There is a version conflict. Although you can continue, the dashboard might give impropriate results." & vbCrLf & _
            "your version: " & strVersion & vbCrLf & _
            "current version: " & rs![version check] & vbCrLf & vbCrLf & _
            "Do you want to download the latest version?"
        strDummy = MsgBox(strTekst, vbYesNo, strTitel)
    
        If strDummy = vbYes Then
            Set ie = CreateObject("Internetexplorer.Application")
            ie.Visible = True
            ie.Navigate "http://sharepoint.company.com/company/location/GSS/Regional%20Teams/EMEA/Lists/Reporting"
        End If
    End If
End Sub