Text File Query - Provider Error

[erock24]

I just changed to windows 7 and excel 2010. Now my excel macro code errors out saying provider not found, may not be properly installed. This is the function where the error occurs, do anyone know how to update the code to work with my new version of windows and excel.

Private Function GetCNN(ByVal csvPath As String) As ADODB.Connection
    Set GetCNN = New ADODB.Connection
    With GetCNN
        .CommandTimeout = 0
        .ConnectionTimeout = 0
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source = " & csvPath & ";" & _
                            "Extended Properties = ""text;HDR=Yes;FMT=Delimited(,)"""
        .Open
    End With
End Function

 

[Ruddles]

Try:-

.Provider="Microsoft.Ace.OLEDB.14.0"

 

[erock24]

i get the same error message, "Provider cannot be found, it may not be properly installed. "
Error 3706.... Do i need to install something??

 

[Ruddles]

Possibly... I'm just Googling for a likely looking solution.

Check this: http://stackoverflow.com/questions/...ovider-is-not-registered-on-the-local-machine

 

[erock24]

Thanks for the link, I actually found what I needed from reading some fine print attached to a microsoft link within the thread. All i needed to do was change what you originally suggested to:

.Provider="Microsoft.Ace.OLEDB.12.0"

thanks,

 

[Ruddles]

D'oh! I thought .12 for Excel 2007, .14 for Excel 2010.