CODEWITHSUNDEEP
vbaexcel-2007
user2328935
user2328935

Reputation: 25

Could not find installable ISAM in excel 2007 vba

i want to try connecting excel to excel using ado connection and vba. but the problem is it give's an error could not find installable ISAM. i tried to look at other solution but same issue will return. may be there's a activex control that im missing? here's my code

Dim cN As ADODB.Connection '* Connection String
Dim RS As ADODB.Recordset '* Record Set
Dim sQuery As String '* Query String
Dim i1 As Long
Dim lMaxRow As Long '* Last Row in the Sheet
Dim iRevCol As Integer '*
Dim i3 As Integer

Set cN = New ADODB.Connection
cN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\PC\Desktop\Excel Programming\PlayerDatabase.xlsm;Readonly=False;Extended Properties=Excel 12.0;;HDR=yes;Persist Security Info=False"
cN.ConnectionTimeout = 40
cN.Open

Set RS = New ADODB.Recordset

lMaxRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
sQuery = "Select * From [Player$]"


RS.ActiveConnection = cN
RS.Source = sQuery
RS.Open

If RS.EOF = True And RS.BOF = True Then
    MsgBox ("End of File")
End If

If RS.State <> adStateClosed Then
RS.Close
End If

If Not RS Is Nothing Then Set RS = Nothing
If Not cN Is Nothing Then Set cN = Nothing

UPDATE:

now i change my connectionstring to this

cN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\PC\Desktop\Excel Programming\PlayerDatabase.xlsm;Extended Properties='Excel 12.0 Macro;HDR=YES'"

but it gives me error Cannot update. Database or object is readonly.

when i put readonly=false

cN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\PC\Desktop\Excel Programming\PlayerDatabase.xlsm;ReadOnly=false;Extended Properties='Excel 12.0 Macro;HDR=YES'"

it will give an error as could not find installable ISAM :(

Upvotes: 0

Views: 5556

Answers (2)

user4049619
user4049619

Reputation: 11

Solved it as follows:

string ConeectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFlp.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"");
OleDbConnection oconn = new OleDbConnection(ConeectionString);

Upvotes: 0

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

I don't know what is inside your XLSM file you retrieve data from but you connection string should be as simple as possible. This is working for me (but I didn't check for read-only parameter):

"Provider=Microsoft.ACE.OLEDB.12.0;" & _
           "Data Source=C:\Users\Dane\BazaDanych.xlsm;" & _
           "Extended Properties=Excel 12.0 Macro"

Upvotes: 1

Related Questions