By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,805 Members | 1,100 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,805 IT Pros & Developers. It's quick & easy.

Can't find installable ISAM error message when using ADODB method

Walt in Decatur
P: 20
I'm trying to use this method to copy an range in Excel workbook that is closed to the one that's open. I'm using the following code, which gets the "Can't find installable ISAM" error when I get to the line "cnt.Open". My Windows files (various .dll) are up to date, so there is some other hitch in there.

Sub Get_Data_Closed_Workbooks()
Dim cnt As ADODB.Connection
Dim cmd As ADODB.Command
Dim stCon As String, stSQL As String
Dim vaNames As Variant
Dim i As Long

'Here we create an array of known files
vaNames = VBA.Array("sending.xlsm")

Set cnt = New ADODB.Connection
Set cmd = New ADODB.Command

For i = LBound(vaNames) To UBound(vaNames)
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\WAB_Stuff\Work Stuff\Excel Stuff\" & vaNames(i) & ";" & _
"Extended Properties='Excel 12.0;HDR=No'"
'Here we insert records without the need to locate the next empty row
'Note! The corresponding fieldnames in each sheet must be in first cell
'and the value below it.
stSQL = "INSERT INTO [Sheet1$] IN 'C:\WAB_Stuff\Work Stuff\Excel Stuff\receiving.xlsm' 'Excel 12.0;'" & _
"SELECT * FROM [Sheet1$C1:C4]"
cnt.ConnectionString = stCon
cnt.Open
cmd.ActiveConnection = cnt
cmd.CommandText = stSQL
cmd.Execute
stCon = Empty
stSQL = Empty
cnt.Close
Next i

Set cmd = Nothing
Set cnt = Nothing

End Sub

Googling for a solution has so far not yielded any fixes. I'm a complete VB dilettante, since I'm an architect by training. Any help will be appreciated.
Aug 11 '09 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,623
@Walt in Decatur
  1. Hello Walt, first, I took the liberty of formatting the code for better readability.
    Expand|Select|Wrap|Line Numbers
    1. Sub Get_Data_Closed_Workbooks()
    2. Dim cnt As ADODB.Connection
    3. Dim cmd As ADODB.Command
    4. Dim stCon As String
    5. Dim stSQL As String
    6. Dim vaNames As Variant
    7. Dim i As Long
    8.  
    9. 'Here we create an array of known files
    10. vaNames = VBA.Array("sending.xlsm")
    11.  
    12. Set cnt = New ADODB.Connection
    13. Set cmd = New ADODB.Command
    14.  
    15. For i = LBound(vaNames) To UBound(vaNames)
    16.   stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    17.           "Data Source=C:\WAB_Stuff\Work Stuff\Excel Stuff\" & vaNames(i) & ";" & _
    18.           "Extended Properties='Excel 12.0;HDR=No'"
    19.  
    20.   'Here we insert records without the need to locate the next empty row
    21.   'Note! The corresponding fieldnames in each sheet must be in first cell
    22.   'and the value below it.
    23.   stSQL = "INSERT INTO [Sheet1$] IN 'C:\WAB_Stuff\Work Stuff\Excel Stuff\receiving.xlsm' 'Excel 12.0;'" & _
    24.           "SELECT * FROM [Sheet1$C1:C4]"
    25.  
    26.   cnt.ConnectionString = stCon
    27.   cnt.Open      'Error here
    28.  
    29.   cmd.ActiveConnection = cnt
    30.   cmd.CommandText = stSQL
    31.     cmd.Execute
    32.  
    33.   stCon = Empty
    34.   stSQL = Empty
    35.     cnt.Close
    36. Next i
  2. Judging from the Jet Version, I assume you are using Access 2003, is this correct?
Aug 11 '09 #2

Expert 100+
P: 1,287
Here's a Microsof Help and Support page on this topic, with links at the top to other versions of Access which say the same thing (the registry has an invalid path to the indexed sequential access method driver):
You receive a "Could not find installable ISAM" error message or some file types are missing when you import files, export files, or link files in Access 2000
Aug 11 '09 #3

Walt in Decatur
P: 20
Adezii:

Thanks for code clean-up. I am truly a dabbler... :-(

I've substituted your code, but changed from Jet.4.0 to ACE.12.0 because I have Access 2007. When I ran it, the error came at line 31, "Cannot Update. Database or object is read-only". I'm actually using this code to get certain cells in an Excel workbook updated via paste linking cells from a closed workbook. The workbook from which I would like to copy is closed and not marked read-only when I run the script (called by an event in a Combobox) in the "receiving" Excel file.

I've formatted the source cells as a table, because that seemed to be one of the issues with the ISAM error I was receiving earlier when trying to use the Jet.4.0 engine reference in the code.
Aug 11 '09 #4

ADezii
Expert 5K+
P: 8,623
@Walt in Decatur
Have you thought about the possibility of 'Linking' the Excel Workbook to be updated, and using Automation code to do the actual updating or dynamically Linking/Unlinking each Excel Workbook in the Array?
Aug 11 '09 #5

Walt in Decatur
P: 20
Actually I have found another script that works great (see below). It's called by a macro which specifies the workbook, sheet and range to be copied (This workbook is the "Data" workbook. This works marvelously, but I need one more thing to happen.

The script is triggered in a "Review" workbook. In it I use a combobox to change the value of a particular cell "A2" (to a different individual's name). In the "Data" workbook, a corresponding cell is linked to this cell, also "A2", in the "Review" workbook. When both workbooks are open, this link updates automatically and in turn updates a bunch of cells in the "Review" workbook, displaying correct data for the individual just selected. No fancy script is needed in this case.

I want to amend the sub shown below to update cell "A2" in the "Data" workbook before the data cells are copied the corresponding range in the "Review" workbook. Would this have to be done first through a separate script, or can it be incorporated into the script below?
Expand|Select|Wrap|Line Numbers
  1. Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
  2.                    SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)
  3. ' 30-Dec-2007, working in Excel 2000-2007
  4.     Dim rsCon As Object
  5.     Dim rsData As Object
  6.     Dim szConnect As String
  7.     Dim szSQL As String
  8.     Dim lCount As Long
  9.  
  10.     ' Create the connection string.
  11.     If Header = False Then
  12.         If Val(Application.Version) < 12 Then
  13.             szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  14.                         "Data Source=" & SourceFile & ";" & _
  15.                         "Extended Properties=""Excel 8.0;HDR=No"";"
  16.         Else
  17.             szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
  18.                         "Data Source=" & SourceFile & ";" & _
  19.                         "Extended Properties=""Excel 12.0;HDR=No"";"
  20.         End If
  21.     Else
  22.         If Val(Application.Version) < 12 Then
  23.             szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  24.                         "Data Source=" & SourceFile & ";" & _
  25.                         "Extended Properties=""Excel 8.0;HDR=Yes"";"
  26.         Else
  27.             szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
  28.                         "Data Source=" & SourceFile & ";" & _
  29.                         "Extended Properties=""Excel 12.0;HDR=Yes"";"
  30.         End If
  31.     End If
  32.  
  33.     If SourceSheet = "" Then
  34.         ' workbook level name
  35.         szSQL = "SELECT * FROM " & SourceRange$ & ";"
  36.     Else
  37.         ' worksheet level name or range
  38.         szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
  39.     End If
  40.  
  41.     On Error GoTo SomethingWrong
  42.         Set rsCon = CreateObject("ADODB.Connection")
  43.     Set rsData = CreateObject("ADODB.Recordset")
  44.  
  45.     rsCon.Open szConnect
  46.     rsData.Open szSQL, rsCon, 0, 1, 1
  47.  
  48.     ' Check to make sure we received data and copy the data
  49.     If Not rsData.EOF Then
  50.  
  51.         If Header = False Then
  52.             TargetRange.Cells(1, 1).CopyFromRecordset rsData
  53.         Else
  54.             'Add the header cell in each column if the last argument is True
  55.             If UseHeaderRow Then
  56.                 For lCount = 0 To rsData.Fields.Count - 1
  57.                     TargetRange.Cells(1, 1 + lCount).Value = _
  58.                     rsData.Fields(lCount).Name
  59.                 Next lCount
  60.                 TargetRange.Cells(2, 1).CopyFromRecordset rsData
  61.             Else
  62.                 TargetRange.Cells(1, 1).CopyFromRecordset rsData
  63.             End If
  64.         End If
  65.  
  66.     Else
  67.         MsgBox "No records returned from : " & SourceFile, vbCritical
  68.     End If
  69.  
  70.     ' Clean up our Recordset object.
  71.     rsData.Close
  72.     Set rsData = Nothing
  73.     rsCon.Close
  74.     Set rsCon = Nothing
  75.     Exit Sub
  76.  
  77. SomethingWrong:
  78.     MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, _
  79.            vbExclamation, "Error"
  80.     On Error GoTo 0
  81.  
  82. End Sub
  83.  
  84. Function LastRow(sh As Worksheet)
  85.     On Error Resume Next
  86.     LastRow = sh.Cells.Find(What:="*", _
  87.                             After:=sh.Range("A1"), _
  88.                             Lookat:=xlPart, _
  89.                             LookIn:=xlFormulas, _
  90.                             SearchOrder:=xlByRows, _
  91.                             SearchDirection:=xlPrevious, _
  92.                             MatchCase:=False).Row
  93.     On Error GoTo 0
  94. End Function
  95.  
  96.  
  97. Function Array_Sort(ArrayList As Variant) As Variant
  98.     Dim aCnt As Integer, bCnt As Integer
  99.     Dim tempStr As String
  100.  
  101.     For aCnt = LBound(ArrayList) To UBound(ArrayList) - 1
  102.         For bCnt = aCnt + 1 To UBound(ArrayList)
  103.             If ArrayList(aCnt) > ArrayList(bCnt) Then
  104.                 tempStr = ArrayList(bCnt)
  105.                 ArrayList(bCnt) = ArrayList(aCnt)
  106.                 ArrayList(aCnt) = tempStr
  107.             End If
  108.         Next bCnt
  109.     Next aCnt
  110.     Array_Sort = ArrayList
  111. End Function
Aug 12 '09 #6

ADezii
Expert 5K+
P: 8,623
I've created a Custom Function for you named fUpdateCellAInDataWorkbook() which will Update Cell A2 in the Data.xls Workbook to any Value you specify. The Function accepts 2 Arguments: the Absolute Path to the Data Workbook, and the New Value to overwrite the existing one. If Cell A1 is Updated successfully the Function returns True and processing continues as Normal in the Calling Routine. If an Error occurs, the Function returns False, a Message Box indicating the Error appears, the Function exists, returns to the Calling Routine, then Exits the Calling Routine. It can be executed totally independently from your existing code. It can probably also be incorporated into the existing code, but its placement would be crucial. In any event, you can try both approaches. Let me know how you make out.
  1. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fUpdateCellAInDataWorkbook(strPathToWorkbook As String, strUpdateTo As String) As Boolean
    2. On Error GoTo Err_fUpdateCellAInDataWorkbook
    3. 'MUST Set a Reference to the Microsoft Excel X.X Object Library
    4. Dim appExcel As Excel.Application
    5. Dim wbExcel As Excel.Workbook
    6.  
    7. Set appExcel = CreateObject("Excel.Application")
    8. Set wbExcel = appExcel.Workbooks.Open(strPathToWorkbook)
    9.  
    10. appExcel.Sheets("Sheet1").Select
    11. appExcel.Cells(1, 2) = strUpdateTo
    12.  
    13. appExcel.DisplayAlerts = False
    14. wbExcel.Save
    15.  
    16. Set wbExcel = Nothing
    17. appExcel.Quit
    18.  
    19. fUpdateCellAInDataWorkbook = True
    20.  
    21. Exit_fUpdateCellAInDataWorkbook:
    22.   Exit Function
    23.  
    24. Err_fUpdateCellAInDataWorkbook:
    25.   fUpdateCellAInDataWorkbook = False
    26.     MsgBox Err.Description, vbExclamation, "Error in fUpdateCellAInDataWorkbook()"
    27.     Resume Exit_fUpdateCellAInDataWorkbook
    28. End Function
  2. Sample Call:
    Expand|Select|Wrap|Line Numbers
    1. If fUpdateCellAInDataWorkbook("C:\Test\Data.xls", "Fred Flintstone") Then
    2.   'Cell A in the Data Workbook was successfully Updated, proceed normally
    3. Else    'get outta Dodge!
    4.   Exit Sub
    5. End If
Aug 12 '09 #7

Walt in Decatur
P: 20
ADezii:

Thanks for the custom function. I have tested a version of it (see code below). It works fine. However, it is not any faster than the code which actually opens the source workbook (see the other code snippet below). In either case, it takes about 30-40 seconds for the code to execute. Any suggestions for speeding things up will be appreciated.

Custom Function Code:
Expand|Select|Wrap|Line Numbers
  1. Public Function fUpdateWorkbook(strPathToWorkbook As String) As Boolean
  2.    On Error GoTo Err_fUpdateWorkbook
  3.    'MUST Set a Reference to the Microsoft Excel X.X Object Library
  4.    Dim appExcel As Excel.Application
  5.    Dim wbExcel As Excel.Workbook
  6.  
  7.    Set appExcel = CreateObject("Excel.Application")
  8.    Set wbExcel = appExcel.Workbooks.Open(strPathToWorkbook)
  9.  
  10.   appExcel.Sheets("Summary").Select
  11.   appExcel.Cells(2, 1) = ThisWorkbook.Sheets("Summary").Range("A2")
  12.  
  13.   appExcel.DisplayAlerts = False
  14.   wbExcel.Save
  15.  
  16.   Set wbExcel = Nothing
  17.   appExcel.Quit
  18.  
  19.   fUpdateWorkbook = True
  20.  
  21. Exit_fUpdateWorkbook:
  22.   Exit Function
  23.  
  24. Err_fUpdateWorkbook:
  25.   fUpdateWorkbook = False
  26.   MsgBox Err.Description, vbExclamation, "Error in fUpdateWorkbook()"
  27.   Resume Exit_fUpdateWorkbook
  28.   End Function
Actually Open Workbook code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ComboBox1_change()
  2. With Application
  3. .ScreenUpdating = False
  4. If Not IsFileOpen("C:\WAB_Stuff\Work Stuff\Excel Stuff\RegTrackSys_Test.xlsm") Then
  5. Workbooks.Open "C:\WAB_Stuff\Work Stuff\Excel Stuff\RegTrackSys_Test.xlsm"
  6. Sheets((1)).Range("a7:w65").Copy
  7. Workbooks("RegTrackSysReview_Test.xlsm").Activate
  8. Sheets("Summary").Select
  9. Range("a7:w65").Select
  10. ActiveSheet.Paste link:=True
  11. Workbooks("RegTrackSys_Test.xlsm").Activate
  12. Workbooks("RegTrackSys_Test.xlsm").Save
  13. Workbooks("RegTrackSys_Test.xlsm").Close
  14. ThisWorkbook.Sheets((1)).Range("A2").Activate
  15. Else
  16. Workbooks("RegTrackSys_Test.xlsm").Sheets("Summary").Range("A2") = ThisWorkbook.Sheets((1)).Range("A2")
  17. ThisWorkbook.Sheets((1)).Range("A2").Activate
  18. End If
  19. .ScreenUpdating = True
  20. End With
  21. End Sub
Aug 25 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.