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.Connectio n
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("send ing.xlsm")
Set cnt = New ADODB.Connectio n
Set cmd = New ADODB.Command
For i = LBound(vaNames) To UBound(vaNames)
stCon = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=C:\WAB_S tuff\Work Stuff\Excel Stuff\" & vaNames(i) & ";" & _
"Extended Properties='Exc el 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\W ork Stuff\Excel Stuff\receiving .xlsm' 'Excel 12.0;'" & _
"SELECT * FROM [Sheet1$C1:C4]"
cnt.ConnectionS tring = stCon
cnt.Open
cmd.ActiveConne ction = 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.
7 10657 ADezii 8,834
Recognized Expert Expert @Walt in Decatur - Hello Walt, first, I took the liberty of formatting the code for better readability.
- Sub Get_Data_Closed_Workbooks()
-
Dim cnt As ADODB.Connection
-
Dim cmd As ADODB.Command
-
Dim stCon As String
-
Dim 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 'Error here
-
-
cmd.ActiveConnection = cnt
-
cmd.CommandText = stSQL
-
cmd.Execute
-
-
stCon = Empty
-
stSQL = Empty
-
cnt.Close
-
Next i
- Judging from the Jet Version, I assume you are using Access 2003, is this correct?
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.
ADezii 8,834
Recognized Expert Expert @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?
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? -
Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
-
SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)
-
' 30-Dec-2007, working in Excel 2000-2007
-
Dim rsCon As Object
-
Dim rsData As Object
-
Dim szConnect As String
-
Dim szSQL As String
-
Dim lCount As Long
-
-
' Create the connection string.
-
If Header = False Then
-
If Val(Application.Version) < 12 Then
-
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
-
"Data Source=" & SourceFile & ";" & _
-
"Extended Properties=""Excel 8.0;HDR=No"";"
-
Else
-
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
-
"Data Source=" & SourceFile & ";" & _
-
"Extended Properties=""Excel 12.0;HDR=No"";"
-
End If
-
Else
-
If Val(Application.Version) < 12 Then
-
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
-
"Data Source=" & SourceFile & ";" & _
-
"Extended Properties=""Excel 8.0;HDR=Yes"";"
-
Else
-
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
-
"Data Source=" & SourceFile & ";" & _
-
"Extended Properties=""Excel 12.0;HDR=Yes"";"
-
End If
-
End If
-
-
If SourceSheet = "" Then
-
' workbook level name
-
szSQL = "SELECT * FROM " & SourceRange$ & ";"
-
Else
-
' worksheet level name or range
-
szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
-
End If
-
-
On Error GoTo SomethingWrong
-
Set rsCon = CreateObject("ADODB.Connection")
-
Set rsData = CreateObject("ADODB.Recordset")
-
-
rsCon.Open szConnect
-
rsData.Open szSQL, rsCon, 0, 1, 1
-
-
' Check to make sure we received data and copy the data
-
If Not rsData.EOF Then
-
-
If Header = False Then
-
TargetRange.Cells(1, 1).CopyFromRecordset rsData
-
Else
-
'Add the header cell in each column if the last argument is True
-
If UseHeaderRow Then
-
For lCount = 0 To rsData.Fields.Count - 1
-
TargetRange.Cells(1, 1 + lCount).Value = _
-
rsData.Fields(lCount).Name
-
Next lCount
-
TargetRange.Cells(2, 1).CopyFromRecordset rsData
-
Else
-
TargetRange.Cells(1, 1).CopyFromRecordset rsData
-
End If
-
End If
-
-
Else
-
MsgBox "No records returned from : " & SourceFile, vbCritical
-
End If
-
-
' Clean up our Recordset object.
-
rsData.Close
-
Set rsData = Nothing
-
rsCon.Close
-
Set rsCon = Nothing
-
Exit Sub
-
-
SomethingWrong:
-
MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, _
-
vbExclamation, "Error"
-
On Error GoTo 0
-
-
End Sub
-
-
Function LastRow(sh As Worksheet)
-
On Error Resume Next
-
LastRow = sh.Cells.Find(What:="*", _
-
After:=sh.Range("A1"), _
-
Lookat:=xlPart, _
-
LookIn:=xlFormulas, _
-
SearchOrder:=xlByRows, _
-
SearchDirection:=xlPrevious, _
-
MatchCase:=False).Row
-
On Error GoTo 0
-
End Function
-
-
-
Function Array_Sort(ArrayList As Variant) As Variant
-
Dim aCnt As Integer, bCnt As Integer
-
Dim tempStr As String
-
-
For aCnt = LBound(ArrayList) To UBound(ArrayList) - 1
-
For bCnt = aCnt + 1 To UBound(ArrayList)
-
If ArrayList(aCnt) > ArrayList(bCnt) Then
-
tempStr = ArrayList(bCnt)
-
ArrayList(bCnt) = ArrayList(aCnt)
-
ArrayList(aCnt) = tempStr
-
End If
-
Next bCnt
-
Next aCnt
-
Array_Sort = ArrayList
-
End Function
ADezii 8,834
Recognized Expert Expert
I've created a Custom Function for you named fUpdateCellAInD ataWorkbook() 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. - Function Definition:
- Public Function fUpdateCellAInDataWorkbook(strPathToWorkbook As String, strUpdateTo As String) As Boolean
-
On Error GoTo Err_fUpdateCellAInDataWorkbook
-
'MUST Set a Reference to the Microsoft Excel X.X Object Library
-
Dim appExcel As Excel.Application
-
Dim wbExcel As Excel.Workbook
-
-
Set appExcel = CreateObject("Excel.Application")
-
Set wbExcel = appExcel.Workbooks.Open(strPathToWorkbook)
-
-
appExcel.Sheets("Sheet1").Select
-
appExcel.Cells(1, 2) = strUpdateTo
-
-
appExcel.DisplayAlerts = False
-
wbExcel.Save
-
-
Set wbExcel = Nothing
-
appExcel.Quit
-
-
fUpdateCellAInDataWorkbook = True
-
-
Exit_fUpdateCellAInDataWorkbook:
-
Exit Function
-
-
Err_fUpdateCellAInDataWorkbook:
-
fUpdateCellAInDataWorkbook = False
-
MsgBox Err.Description, vbExclamation, "Error in fUpdateCellAInDataWorkbook()"
-
Resume Exit_fUpdateCellAInDataWorkbook
-
End Function
- Sample Call:
- If fUpdateCellAInDataWorkbook("C:\Test\Data.xls", "Fred Flintstone") Then
-
'Cell A in the Data Workbook was successfully Updated, proceed normally
-
Else 'get outta Dodge!
-
Exit Sub
-
End If
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: -
Public Function fUpdateWorkbook(strPathToWorkbook As String) As Boolean
-
On Error GoTo Err_fUpdateWorkbook
-
'MUST Set a Reference to the Microsoft Excel X.X Object Library
-
Dim appExcel As Excel.Application
-
Dim wbExcel As Excel.Workbook
-
-
Set appExcel = CreateObject("Excel.Application")
-
Set wbExcel = appExcel.Workbooks.Open(strPathToWorkbook)
-
-
appExcel.Sheets("Summary").Select
-
appExcel.Cells(2, 1) = ThisWorkbook.Sheets("Summary").Range("A2")
-
-
appExcel.DisplayAlerts = False
-
wbExcel.Save
-
-
Set wbExcel = Nothing
-
appExcel.Quit
-
-
fUpdateWorkbook = True
-
-
Exit_fUpdateWorkbook:
-
Exit Function
-
-
Err_fUpdateWorkbook:
-
fUpdateWorkbook = False
-
MsgBox Err.Description, vbExclamation, "Error in fUpdateWorkbook()"
-
Resume Exit_fUpdateWorkbook
-
End Function
Actually Open Workbook code: - Private Sub ComboBox1_change()
-
With Application
-
.ScreenUpdating = False
-
If Not IsFileOpen("C:\WAB_Stuff\Work Stuff\Excel Stuff\RegTrackSys_Test.xlsm") Then
-
Workbooks.Open "C:\WAB_Stuff\Work Stuff\Excel Stuff\RegTrackSys_Test.xlsm"
-
Sheets((1)).Range("a7:w65").Copy
-
Workbooks("RegTrackSysReview_Test.xlsm").Activate
-
Sheets("Summary").Select
-
Range("a7:w65").Select
-
ActiveSheet.Paste link:=True
-
Workbooks("RegTrackSys_Test.xlsm").Activate
-
Workbooks("RegTrackSys_Test.xlsm").Save
-
Workbooks("RegTrackSys_Test.xlsm").Close
-
ThisWorkbook.Sheets((1)).Range("A2").Activate
-
Else
-
Workbooks("RegTrackSys_Test.xlsm").Sheets("Summary").Range("A2") = ThisWorkbook.Sheets((1)).Range("A2")
-
ThisWorkbook.Sheets((1)).Range("A2").Activate
-
End If
-
.ScreenUpdating = True
-
End With
-
End Sub
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Mike Grimmett |
last post by:
I am using the code below to connect to an Access back end. When I try to
connect, I receive a "Could Not find Installable ISAM" error. I went through
the docs on MS SUpport site and they did not seem to be the issue.
It bombs on the cnn.Open line
Any help would be appreciated. Thanks.
Mike
|
by: Robert Lawson |
last post by:
I continue to get the below error message when trying to load a aspx
file. Could someone please point me in the right direction for solving
this? I'm trying to access an access data base and I'm the only one
accessing it at this time. I'm trying to learn this step by step and I'm
at the begining of ADO stuff.
Server Error in '/' Application.
------------------------------------------------------------------------
--------
|
by: JP Lacasse |
last post by:
I translate a web application from ASP to ASP.NET and I keep getting the following error: Could not find installable ISA
I used the same ConnectString ("DBQ=" & Server.MapPath("Database\Icsdata.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};Provider=Microsoft.Jet.OLEDB.4.0;" to point to my Access Database
Any Suggestions why I am getting this error message
Would appreciate any help because I am stuck
Thanks -- J.P. Lacass
|
by: ibeetb |
last post by:
When using ADO.NET, here is my code to bring in a table from EXCEL:
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Inetpub\wwwroot\ExcelData.xls;" _
& "Extended Properties=Excel 11.0;"
When it tries to OPEN my connection, I receive the following error:
COULD NOT FIND INSTALLABLE ISAM!
|
by: Job Lot |
last post by:
i am querying excel file as follows
Dim conn As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source='" &
"C:\Temp\SSPortfolio.xls" & " '; " & _
"Extended Properties=Excel 8.0;" &
"HDR=Yes;" & "IMEX=1")
'! Select the data from Sheet1 of the workbook.
| |
by: Anatoly Kurilin |
last post by:
Hi, each time I use that code for calculating a number of user connected to
a data file, I get an error message:
Could not find installable ISAM. What do I do wrong?
Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim intUserCount As Integer
Dim strPassword As String
|
by: Dan |
last post by:
Hi,
My global.asax contains this:
Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
Dim oConnection As New OleDbConnection()
oConnection.ConnectionString =
System.Configuration.ConfigurationManager.AppSettings("newres")
oConnection.Open()
Session("oConnection") = oConnection
End Sub
|
by: indhu |
last post by:
error is coming when i run the form.
Couldn't Find Installable ISAM.
CON.OPEN error is pointing to this place. problem in db or else
|
by: Vee007 |
last post by:
Following is my code:
Dim objCatalog As ADOX.Catalog
Dim objTableLink As ADOX.Table
Dim objADOConnection As ADODB.Connection
Try
objADOConnection = New ADODB.Connection
objADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Database\abc.mdb;User Id=admin;Password=;")
objCatalog = New ADOX.Catalog
objCatalog.ActiveConnection = objADOConnection
...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |