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:
It looks correct but on the
What is wrong with the connection string?
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: