[V8i SS10 VBA] ADO connection to excel

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?

Parents
  • Hi Martin,

    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).

    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.

    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.

    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.

    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

  • 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. 

    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!

  • 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.

    If you have not it installed, I guess it can be downloaded from Microsoft site.

    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.

    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)?

    It shows a log file that would be sent to Bentley

    When you choose to send, it is sent, both log and memory dump.

    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,

      Jan

  • 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

Reply
  • 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

Children
No Data