none
How to connect to Excel 2016 with oledb. RRS feed

  • Question

  • I'm trying to connect to an excel sheet office 2016.
    But I always get an error.
    What's wrong ?


    private static String filePath = @"C:\Users\oliver\Documents\klantenformulier.xlsx";
    
    private string connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0 Xml;Data Source={0}; Extended Properties='Excel 12.0; HDR=Yes'", filePath);
    
    //Is there something wrong with the above connection string ?
    
    //I use this code to test the connection:
    //I always get the exception after oleDBConnection.open();
    
    
            public void connectieMaken()
            {
    
                OleDbConnection oleDbConnection = new OleDbConnection(this.connectionString);
                try
                {
                    oleDbConnection.Open();
                    MessageBox.Show("Connection Successful");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Connection failed :" + oleDbConnection.ConnectionString);
                }
    
            }


    Friday, July 26, 2019 8:44 PM

All replies

  • Hello,

    Please provide the error message.

    The following is all messed together and works with Excel 2016.

    using System;
    using System.Data.OleDb;
    using System.IO;
    
    namespace WindowsFormsApp2
    {
    
        public class Demo
        {
            public Demo()
            {
                const string fileName = "E1.xlsx";
                if (File.Exists(fileName))
                {
                    var con = new SmartConnection();
                    using (var cn = new OleDbConnection(con.ConnectionString(fileName, 1, ExcelHeader.No)))
                    {
                        cn.Open();
                        Console.WriteLine("Open for work");
                    }
                }
                else
                {
                    Console.WriteLine("File does not exists");
                }
            }
        }
    
        public enum ExcelHeader
        {
            Yes,
            No
        }
    
        public class SmartConnection
        {
            public string ConnectionString(string pFileName, int pImex = 1, ExcelHeader pHeader = ExcelHeader.No)
            {
                var builder = new OleDbConnectionStringBuilder();
                if (System.IO.Path.GetExtension(pFileName)?.ToUpper() == ".XLS")
                {
                    builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                    builder.Add("Extended Properties", $"Excel 8.0;IMEX={pImex};HDR={pHeader.ToString()};");
                }
                else
                {
                    builder.Provider = "Microsoft.ACE.OLEDB.12.0";
                    builder.Add("Extended Properties", $"Excel 12.0;IMEX={pImex};HDR={pHeader.ToString()};");
                }
    
                builder.DataSource = pFileName;
    
                return builder.ConnectionString;
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Friday, July 26, 2019 8:52 PM
    Moderator
  • I get this exception:

    System.InvalidOperationException: 'De Microsoft.ACE.OLEDB.12.0-provider is not registered...

    I have to install the provider I guess.
    Isn't it standard in Windows ? If I deploy this should I install the provider on every client pc ?


    Saturday, July 27, 2019 6:29 AM
  • I get this exception:

    System.InvalidOperationException: 'De Microsoft.ACE.OLEDB.12.0-provider is not registered...

    I have to install the provider I guess.
    Isn't it standard in Windows ? If I deploy this should I install the provider on every client pc ? 

    ACE drivers are not installed by default.

    For example on my configuration (Windows 10 with Office 2016 32-bit) I had to install 64-bit drivers from Microsoft Access Database Engine 2016 Redistributable

    And your connection string is not correct (Xml is in "Extended Properties")

    Saturday, July 27, 2019 8:30 AM
  • A lot of topics on this.

    I have both 64-bit drivers installed:

    EXEC master.dbo.sp_MSset_oledb_prop;

    My office version is: Office 365 MSO (16.0.11727.20222) 64-bits.

    And Visual Studio is still saying my provider is not installed. (tried 12.0 and 16.0 same error.)

    How to fix this ? 


    Sunday, July 28, 2019 7:02 AM
  • Hi 

    Thank you for posting here.

    First, it is best for you to install the Excel software. 

    Second, based on my search, you could try to install Microsoft Access Database Engine 2010 software to solve the exception.

    Note: Please restart the computer after you install the software if you want to test the code if it works.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 29, 2019 2:00 AM
    Moderator