Hi All,
i have been using this function in Inventor 2010. we now upgraded to 2016 and this function does not work anymore.
I also have got Active x and Excel library references loaded.
Seems "dbconnection.Open dbconnectionstring is not working now. Is this related to inventor migrating to EXCEL API?
i am lost and any help is appreciated.
Jiten
Public Function ReadDataFromWorkbook(SourceFile, SourceRange As String) As Variant 'Public Function Since it is also used in Frm_iproperties ' requires a reference to the Microsoft ActiveX Data Objects library ' requires a reference to the Microsoft Excel 14.0 Object library ' if SourceRange is a range reference, this function can only return data from the first worksheet in SourceFile ' if SourceRange is a defined name reference, this function can return data from any worksheet in SourceFile ' SourceRange must include the range headers ' examples: ' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:A21") ' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21") ' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "DefinedRangeName") Dim dbConnectionString As String, dbConnection As ADODB.Connection, rs As ADODB.Recordset dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=False;DBQ=" & SourceFile 'dbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & SourceFile & ";Extended Properties=Excel 8.0;HDR=Yes" Set dbConnection = New ADODB.Connection MsgBox dbConnectionString On Error GoTo InvalidInput 'dbConnection.ConnectionString = dbConnectionString dbConnection.Open dbConnectionString ' open the database connection ' 32bit office clashes with 64 bit inventor here Set rs = dbConnection.Execute("[" & SourceRange & "]") On Error GoTo 0 ReadDataFromWorkbook = rs.GetRows ' returns a two dim array with all records in rs rs.Close dbConnection.Close ' close the database connection Set rs = Nothing Set dbConnection = Nothing On Error GoTo 0 Exit Function InvalidInput: MsgBox "The source file or source range is invalid!" & SourceFile & err, vbExclamation, "Get data from closed workbook" Set rs = Nothing Set dbConnection = Nothing End Function
Solved! Go to Solution.
Solved by norman.yuan. Go to Solution.
When it worked for your Inventor 2010, it must be this combination: Inventor 2010 is 32bit and the OS is WinXP. Here you have 2 issues:
1. Database engine: Microsoft.Jet.Oledb.4.0. Prior to MS Access 2007(or Access2010?)/Windows Vista, this DB engine comes with Windows OS. You are now likely using newer Windows (7, 8, or 10), which does not have this DB engine, unless you have older MS Access installed (MS Access 2007 or older). Thus, you cannot use Microsoft.Jet.Oledb.4.0 any more. However, you can download Microsoft Access DB Engine, which is the replacement of Jet Engine (but not coming with Windows OS automatically). If you (or your app's user have later MS Access installed, this DB Engine then is available.
2. However, since now you are using 64-bit Invetor, the Microsoft Access DB Engine must be 64 bit (make sure you downloaded 64 bit one, not 32-bit one). If your (or your user's) computer somehow already has 32-bit MS Access (or MS Office that includes MS Access), then you cannot install 64-bit DB engine. You either use 32-bit Inventor, or you use 64-bit MS Access (or remove MS Access). If you use older version of MS Office (2010), it is tricky to install 64-bit DB engine with 32-bit Office in the same computer.
If you could port/upgrade your app to use AutoCAD .NET API, you would have much more and easier options.
By the way, in your cose (of simply reading data from Excel sheet via ADODB, there is no need to set reference to Excel object library: the code does not actually open/start Excel application in order to read data in *.xls file.
Norman Yuan
Thank you norman.
Sorry, I should have deleted Microsoft.Jet.Oledb.4.0. I had commented that out so its not executed.
The ADODB connection is not working. I think Microsoft Excel Driver is not working, may be it is 32bit Driver.
I am basically a design engineer with half baked knowledge in VBA. Also i dont understand 32 bit and 64 bit thing quite well but i know it does create issues. from you reply i think i am stuck in this one.
My Actual Problem
There is one big excel file and i need some data from it(Not all the data). Opening this Excel file takes up a long time so this database method used to work out faster.
Can you suggest some examples where such thing has been done and compatible with 2016 Inventor. if you can share any link that you may know of will be of immense help.
Thanks and Regards,
Jiten
Yes, the issue is caused by the fact that the VBA in your newer Inventor (must be 2014 or newer, right?) is 64-bit VBA, while the driver for accessing data in the Excel sheet is 32 bit.
Assume you are using 32-bit MS Office 2013 or later in your (or your users') computer, so that you do not have problem to install 64-bit MS Access DB Engine (download from here).
So, download and install the DB engine, in AutoCAD VBA, set reference to MS ActiveX Data Object Library 2.8, then you can try following code:
Option Explicit Public Sub ConnectToExcel() '' Excel file Dim xlsFile As String xlsFile = "C:\Temp\MyData.xlsx" '' 2 columns and 8 rows data, with first row as Header Dim dataRange As String dataRange = "A1:B8" Dim cnn As ADODB.Connection Dim cmd As ADODB.Command Dim rs As ADODB.Recordset '' Use MS Access DB Engine 64 bit, '' Notice that "HDR=YES;" treats the first row as header Dim cnString As String cnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & xlsFile & _ ";Extended Properties=""Excel 12.0;HDR=YES;""" Set cnn = New ADODB.Connection cnn.Open cnString MsgBox "Connection!" On Error Resume Next Set rs = cnn.Execute("[" & dataRange & "]") If Err.Number <> 0 Then MsgBox "Error: " & Err.Description End If Do While Not rs.EOF MsgBox rs.Fields(0).Value & vbCrLf & rs.Fields(1).Value rs.MoveNext Loop rs.Close cnn.Close MsgBox "Done." End Sub
Again, only one version of MS Access DB Engine can be installed (32, or 64 bit), meaning, if the computer has 32-bit MS Access installed, you cannot install 64-bit DB Engine.
But the better solution is probably giving up VBA and move to .NET API, because there is much less supported tools, components can be used in 64-bit VBA.
Norman Yuan
Hi Norman,
Talked to IT Guys here and confirmed the issue. You Nailed it. I need to update my office to 64bit. i was not able to install the Driver for 64 bit as 32 bit office is installed and needs to be removed and 64 bit office to be installed. that would take its own time but i think whatever you have shown is bound to work after that.
Thanks and Regards,
Jiten
Just want to make sure that you only need to remove 32-bit MS Office is because your MS Office suite includes MS Access. If you do not have MS Access installed, then you can install 64-bit MS Access DB Engine without along with 32-bit MS Office, provided the MS Office is Office 2010 or later (for Office 2010, it is tricky: you need to remove it first, and then install the 64-bit DB Engine, then reinstall the 32-bit office).
Of course you may have to do this to all possible users of this application, which could be an issue. IMO, it is not worth extra effort on porting old 32-bit VBA to 64-bit VBA apps in many, or even most cases, because there are so many needed components hardly available and the replacements (such as this in your case) either difficult to use or not suitable.
Norman Yuan
Can't find what you're looking for? Ask the community or share your knowledge.