ACE OLEDB Connection to Excel File giving me Error (1 Viewer)

Djblois

Registered User.
Local time
Yesterday, 22:37
Joined
Jan 26, 2009
Messages
598
I am working on some vba to connect to an excel file, so I can insert new records or edit records from access. I have modified similar code that I use to connect to an access table, just updated the connection string with what I found here:

Excel connection strings - ConnectionStrings.com

Here is my code currently:
Code:
Sub ConnectToExcelFile()
   
    On Error GoTo fn_err
   
    Dim ErrorType As String
    Dim blnCloseConn As Boolean: blnCloseConn = True
    Dim ExcelFile As String: ExcelFile = "W:\COMBINED Salesforce AccountID and Email 060622 For New Front End.xlsx"
    Dim SQL As String: SQL = "SELECT * FROM Lookup"
           
    ErrorType = "Connection"
    'Create the ADODB connection object.
    Dim con As Object: Set con = CreateObject("ADODB.connection")
   
    MsgBox "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelFile & "; Extended Properties=""Excel 12.0 Xml;HDR=YES""""; "
   
    'Open the connection.
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelFile & "; Extended Properties=""Excel 12.0 Xml;HDR=YES""""; "
   
    ErrorType = "Recordset"
    'Create the ADODB recordset object.
    Dim rs As Object: Set rs = CreateObject("ADODB.Recordset")
   
    'Open the recordset.
    rs.Open SQL, con

        'Check if the recordset is empty.
    If rs.EOF And rs.BOF Then Err.Raise (1)
   
fn_Exit:
   
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not con Is Nothing And blnCloseConn Then
        If con.Open Then con.Close
        Set con = Nothing
    End If

    Exit Sub
fn_err:
    If Err.Number = 1 Then
        MsgBox "There are no records in the recordset!", vbCritical, "No Records"
    ElseIf Err.Number = 9 Then
        MsgBox ErrorType & " was not created!", vbCritical, "Error"
    ElseIf Err.Number = -2147217805 Then
        MsgBox "Connection Failed"
        blnCloseConn = False
    Else
        MsgBox "Error# " & Err.Number & ": " & Err.Description
    End If
    Resume fn_Exit
   
End Sub

It looks correct but on the con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelFile & "; Extended Properties=""Excel 12.0 Xml;HDR=YES""""; " line I keep receiving this error:

Format of the initialization string does not conform to the OLE DB specification.

What is wrong with the connection string?
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 06:37
Joined
Sep 21, 2011
Messages
10,838
Why not debug.print the connection string and compare against example?
 

Djblois

Registered User.
Local time
Yesterday, 22:37
Joined
Jan 26, 2009
Messages
598
@Gasman

Don't you see in the code above, I did the same thing with a msgbox and the connection string looks correct
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:37
Joined
Sep 21, 2011
Messages
10,838
I have never used connection strings.
However, if I was trying to do as you are doing, I would compare, side by side.?
I mainly use debug.print as then I can copy and paste the output. You could use that method to paste back here, if you cannot see the issue.
You could have also posted the example link, rather than make people going looking for it?
 

Djblois

Registered User.
Local time
Yesterday, 22:37
Joined
Jan 26, 2009
Messages
598
I have never used connection strings.
However, if I was trying to do as you are doing, I would compare, side by side.?
I mainly use debug.print as then I can copy and paste the output. You could use that method to paste back here, if you cannot see the issue.
You could have also posted the example link, rather than make people going looking for it?
I did post the example link. and I did compare them side by side using msgbox, instead of debug.print
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:37
Joined
Sep 21, 2011
Messages
10,838
You did not post an example link, just the link to go searching for the correct one. I am unable to compare the two easily, unless I compare side by side. Yes, yours 'looks' correct but obviously not? Just saying how I would approach the problem.
Why not just link to the excel file?, as it is harcoded anyway
 

Users who are viewing this thread

Top Bottom