0

Story:

The legacy project is moving from VBA to VB.NET. One of the key functions is the connection to Excel. VBA function works always on any machine x32/x64, Windows 7/8/10, with just Excel installed.

The same function on VB.NET throws an error for users who don't have MS-Access installed. 

System.Runtime.InteropServices.COMException: Provider cannot be found. It may not be properly installed.
at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack, Boolean IgnoreReturn)
   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
   at ConsoleApp3.Module1.Main() in ...source\repos\ConsoleApp3\ConsoleApp3\Module1.vb:line 11

Problem:

Users don't have Admin rights so any solution that requires installation of any software is not applicable.

Functions:

VBA perfectly works everywhere:

Sub workingVBAFunction()
   Dim conn As Object
   Dim connStr As String
   Dim path As String
   path = ThisWorkbook.FullName
   connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
   Set conn = CreateObject("ADODB.Connection")
   Call conn.Open(connStr)
   conn.Close
   Set conn = Nothing
End Sub

VB.NET works only on machines with MS-Access installed:

Sub failingDotNETFunction()
    Dim conn As Object = CreateObject("ADODB.Connection")
    Dim connStr As String
    Dim path As String
    path = Environ("userprofile") & "\desktop\Book1.xlsm"
    connStr = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={path};Extended Properties=""Excel 12.0 Xml;HDR=YES;"";"
    conn.Open(connStr)
    conn.Close
End Sub

Question:

How to create a connection with VB.NET without MS-Access Engine on the Local machine? It should be possible because VBA somehow does it. How to replicate the behavior of VBA to VB.NET?

Already tried:

None of this works on machines without Access

  • This Answer
  • Changing the provider in the connection string:
    1. "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & path & ";Extended Properties='Excel 8.0 Xml'"
    2. "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DSN=Excel Files; DBQ=" & path & ";"
    3. "Excel File=" & path & ";"
  • Build Application for Any CPU/x64/x86
hiichaki
  • 834
  • 9
  • 19
  • You dont need to have access installed, but you do need to install the free to use engine... https://www.microsoft.com/en-us/download/details.aspx?id=54920 – braX Dec 19 '19 at 10:25
  • Users don't have Admin rights so **any solution** that requires **installation of any software** is not applicable. – hiichaki Dec 19 '19 at 10:28
  • Then you are out of luck. Sorry. – braX Dec 19 '19 at 10:28
  • 4
    There is nothing that would stop VB.NET from using a provider usable from VBA on the same machine (especially when you are using the COM ADODB from VB.NET). What should fix it is building to the same bitness as the Excel installed on the machine. If that does not fix it, you are doing something wrong. – GSerg Dec 19 '19 at 10:30

0 Answers0