Hello!
I'm trying to access some data in an excel file from Microstation with the help of an ADO connection. I've written code in excel that works just fine, but for some reason the same code does not work in Microstation. I have the reference "Microstation ActiveX Data Objects 6.1 Library" on and can't think of anything else needed. When I run it in microstation, all of microstation crashes on the line of "Connection.Open openstring".
This is the code:
Sub Adotest() Dim connection As ADODB.connection Set connection = New ADODB.connection Dim myfullname As String myfullname = "c:\test\test.xlsx" Dim openstring As String openstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & myfullname & _ "; Extended Properties=""Excel 12.0 Xml;HDR=NO;"";" Debug.Print openstring connection.Open openstring Dim query As String query = "Select * From [Sheet1$]" Dim rs As New ADODB.Recordset rs.Open query, connection Dim arr As Variant arr = rs.GetRows connection.Close Debug.Print arr(0, 4) End Sub
I don't get it. ADO seems fairly commonly used with Microstation, though not usually with excel files like this. I also tried this on the Connect edition, which is a bit more helpful by giving the error message "3706" saying there is no such provider. But it works in excel and using the same reference?
Hi Martin,
Martin Berg said:I have the reference "Microstation ActiveX Data Objects 6.1 Library" on and can't think of anything else needed.
I agree nothing else is required to be referenced (of course beside standard references like VBA and MicroStation object library).
Martin Berg said:When I run it in microstation, all of microstation crashes on the line of "Connection.Open openstring".
There is no message or error when code started in VBA editor?
Or, anything displayed in Windows log?
The code works fine (after modification for my installation) in my MicroStation V8i.
Martin Berg said:ADO seems fairly commonly used with Microstation
Why do you think that? From VBA macro, probably yes. But MicroStation itself use own DB interface and in other API, other libraries, depending on project requirements, are used.
Martin Berg said:I also tried this on the Connect edition, which is a bit more helpful by giving the error message "3706" saying there is no such provider.
MicroStation CE is 64bit application, so you have to install 64bit ADO library.
Martin Berg said:But it works in excel and using the same reference?
Do you use 32bit or 64bit Office? Depending on that, Excel references 32bit (from Program Files (x86)) or 64bit (from Program Files) libraries.
With regards,
Jan
Bentley Accredited Developer: iTwin Platform - AssociateLabyrinth Technology | dev.notes() | cad.point
Yes, we do have 32 bit office. So I guess that might be why it does not work in Connect. That puts things in a new perspective, as we are in the process of switching to Connect, but currently mostly use V8i. Maybe this means I'm heading down an inconvenient path by even using this way to access excel files. My ultimate goal is simply read from .xlsx-files without involving excel in VBA code.
Jan Ĺ legr said:There is no message or error when code started in VBA editor? Or, anything displayed in Windows log? The code works fine (after modification for my installation) in my MicroStation V8i.
Microstation locks up for 10 seconds or so, after that I get a bug report window from Bentley. It shows a log file that would be sent to Bentley, I could post that here but it's very long and I'm not sure what parts are relevant. It's good to hear that you got it working, then there is hope!
Martin Berg said:So I guess that might be why it does not work in Connect.
There can be 64bit ADO installed already by other application or separately.
Microsoft ActiveX Data Object 6.1 Library in 64bit version is represented by C:\Program Files\Common Files\System\ado\msado15.dll file.
C:\Program Files\Common Files\System\ado\msado15.dll
If you have not it installed, I guess it can be downloaded from Microsoft site.
Martin Berg said:Maybe this means I'm heading down an inconvenient path by even using this way to access excel files.
To use ADO to access Excel (and other supported data sources) is perfectly valid and fine.
Martin Berg said:My ultimate goal is simply read from .xlsx-files without involving excel in VBA code.
As I wrote, the code works on my V8i SS10 installation, so maybe the problem is in corrupted installation (MicroStation or ADO)?
Martin Berg said:It shows a log file that would be sent to Bentley
When you choose to send, it is sent, both log and memory dump.
Martin Berg said:I could post that here but it's very long and I'm not sure what parts are relevant.
When the problem is in corrupted installation, it's hard to detect the problem, because it means the application runs in incorrect environment and unpredictable context.
But in general, when there is a critical problem and you really want to help Bentley to analyze it, it's possible to configure MicroStation to produce full memory dump. It's huge file, but it provides maximum details. It's typically sent to Bentley support directly. But I guess it's not time to use this option (yet).
Regards,
Martin Berg said:My ultimate goal is simply read from .xlsx-files without involving excel in VBA code
The approach using OLE-DB should be fine if you have bit-compatible version of both sides of the equation: i.e. 64-bit MicroStation with 64-bit DB driver.
However, if you are unable for whatever reason to use 64-bit Office (IT policy, issues with installation etc) then I suggest that you use a third-party library that can read Excel files
Alternatively, switch languages in CONNECT. .NET is a first-class API in MicroStation CONNECT. .NET has good DB support built-in, and there are third-party libraries available for Excel file interchange.
Also for 64-bit .NET apps, there are Excel-like components that give spreadsheet capability in MicroStation along with Excel file compatibility...
Regards, Jon Summers LA Solutions