none
Load Excel Sheet into DataTable RRS feed

  • Question

  • The code below works on four machines but does not work on a fifth machine.  The machines are all Windows 10, office 2016.  The test was to load the same sheet from all machines.  This was done one at a time.

    In the code you will see a call to ShowStatus which is a simple debugging aid.

    The method below is called from a button click.

        Private Function GetDataFromSheet(excelPath As String, SheetName As String) As DataTable
            Dim conn As OleDbConnection
            Dim cmnd As New OleDbDataAdapter
            Dim pth As String = "'" & excelPath & "'"
            'note that the extended props. are enclosed in "
            Dim cs As String = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pth & ";Extended Properties=""Excel 12.0;HDR=NO;READONLY=TRUE"""
            'Dim cs As String = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & pth & ";Extended Properties=""Excel 12.0;HDR=NO;READONLY=TRUE"""
            conn = New OleDbConnection(cs)
            cmnd.SelectCommand = New OleDbCommand
            cmnd.SelectCommand.Connection = conn
            ShowStatus("Load Sheet") '<<<<<<<<<<<<<<<<<<<<<  DO SEE THIS 
    
            conn.Open()
            ShowStatus("Get Sheets") '<<<<<<<<<<<<<<<<<<<<<  NEVER SEE THIS ON FAIL
    
            Dim sheets As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            'note [] and $ to select sheet
            cmnd.SelectCommand.CommandText = String.Format("select * from [{0}$]", SheetName)
            Dim dt As New DataTable
            ShowStatus("Fill Table")
            cmnd.Fill(dt)
            cmnd.Dispose()
            conn.Close()
            conn.Dispose()
            ShowStatus("Add Sel")
            dt.Columns.Add("Sel", GetType(Integer))
            Return dt
        End Function
    

    Whatever is wrong appears to be at  conn.Open.

    It doesn't throw an exception and the UI is responsive, i.e. you can close the program.

    Let me say this again, the code works perfectly on four machines, and on one it does not.

    Thanks for any insights.


    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it."

    - from former MSDN User JohnWein

    SerialPort Info

    Multics - An OS ahead of its time.

    Friday, November 2, 2018 12:44 PM

Answers

All replies

  • Greetings,

    Where is the path located, on a server or local box? If not on a local box it might be a rights issue but that should throw an exception, just toss this out as this as you are too thinking this is strange.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, November 2, 2018 12:52 PM
    Moderator
  • Greetings,

    Where is the path located, on a server or local box? If not on a local box it might be a rights issue but that should throw an exception, just toss this out as this as you are too thinking this is strange.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Funny thing is... the test file is on a server and belongs to, wait for it..... the user that has the issue.  He can open the workbook manually FWIW.

    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it."

    - from former MSDN User JohnWein

    SerialPort Info

    Multics - An OS ahead of its time.

    Friday, November 2, 2018 12:58 PM
  • Perhaps trying a variation for the connection string e.g.

    Dim cs As String = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pth & ";Extended Properties=""Excel 12.0 Xml;HDR=NO;READONLY=TRUE"""
    Dim cs As String = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pth & ";Extended Properties=""Excel 12.0 Xml;IMEX=1;HDR=NO;READONLY=TRUE"""


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, November 2, 2018 1:52 PM
    Moderator
  • Hello,

    Dim pth As String = "'" & excelPath & "'"

    You have path quoted. As far as I know - quoting not required for source.

    -----

    You didn't provide a sample of your filename. I do have an issue with file when filename didn't have an extension ".xsl", but it was with Excel 8.0.


    Sincerely, Highly skilled coding monkey.

    Friday, November 2, 2018 2:16 PM
  • Hello,

    Dim pth As String = "'" & excelPath & "'"

    You have path quoted. As far as I know - quoting not required for source.

    -----

    You didn't provide a sample of your filename. I do have an issue with file when filename didn't have an extension ".xsl", but it was with Excel 8.0.


    Sincerely, Highly skilled coding monkey.


    That was one of the things I tried.  Saw it somewhere and it didn't hurt, works just fine.  I had the user restart his PC and he is still having the issue.

    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it."

    - from former MSDN User JohnWein

    SerialPort Info

    Multics - An OS ahead of its time.

    Friday, November 2, 2018 2:40 PM
  • Perhaps trying a variation for the connection string e.g.

    Dim cs As String = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pth & ";Extended Properties=""Excel 12.0 Xml;HDR=NO;READONLY=TRUE"""
    Dim cs As String = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pth & ";Extended Properties=""Excel 12.0 Xml;IMEX=1;HDR=NO;READONLY=TRUE"""


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tried both, no joy.  Still 4 good, 1 fail.

     It is like there is an error but it is not being thrown.

    The info I have says that when you add a reference to Microsoft.Office.Interop.Excel you get Microsoft.ACE.OLEDB. 


    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it."

    - from former MSDN User JohnWein

    SerialPort Info

    Multics - An OS ahead of its time.


    • Edited by dbasnett Friday, November 2, 2018 3:46 PM
    Friday, November 2, 2018 3:46 PM
  • Hmmm... is there a proper version of Excel installed? 

    As far as I can see - 

    Microsoft.ACE.OLEDB.12.0

    are targeted for  Excel 97-2003 - it's Excel 8.0, not Excel 12.0 in Extended Properties. 


    Sincerely, Highly skilled coding monkey.

    Friday, November 2, 2018 4:07 PM
  • Hmmm... is there a proper version of Excel installed? 

    As far as I can see - 

    Microsoft.ACE.OLEDB.12.0

    are targeted for  Excel 97-2003 - it's Excel 8.0, not Excel 12.0 in Extended Properties. 


    Sincerely, Highly skilled coding monkey.


    All of the examples I saw showed it like I have it, and it works on four machines.  Caused error when changed to 8 during debugging.

    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it."

    - from former MSDN User JohnWein

    SerialPort Info

    Multics - An OS ahead of its time.

    Friday, November 2, 2018 4:31 PM
  • Can you open a file if you have it on the local PC? 

    P.S. It's a bit difficult to understand your answer about version of Excel installed on PC. 

    Check the following for connection string https://www.connectionstrings.com/ace-oledb-12-0/


    Sincerely, Highly skilled coding monkey.

    Friday, November 2, 2018 5:09 PM
  • One more think. 

    If you logon as one of the users on which PC your code work and use your program - will it work as expected? 


    Sincerely, Highly skilled coding monkey.

    Friday, November 2, 2018 5:18 PM
  • All of the PC's have the same version of Excel and Windows.  The user can't open the file no matter where it is, we copied the file from the server to his PC.

    I am thinking about having the IT people reinstall excel on his machine.


    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it."

    - from former MSDN User JohnWein

    SerialPort Info

    Multics - An OS ahead of its time.

    Friday, November 2, 2018 6:08 PM
  • Turns out it was the provider was not registered and that I wasn't seeing the error because I'm not very smart sometimes.  I had the code that called the method in an empty try catch with a Stop commented out. 

    WTG Dewayne

    THANKS everyone.  Sorry.

    edit - the fix was to install this

    https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255


    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it."

    - from former MSDN User JohnWein

    SerialPort Info

    Multics - An OS ahead of its time.


    • Edited by dbasnett Friday, November 2, 2018 6:30 PM
    • Marked as answer by dbasnett Wednesday, November 7, 2018 1:35 PM
    Friday, November 2, 2018 6:29 PM
  • Turns out it was the provider was not registered and that I wasn't seeing the error because I'm not very smart sometimes.  I had the code that called the method in an empty try catch with a Stop commented out. 

    WTG Dewayne

    THANKS everyone.  Sorry.

    edit - the fix was to install this

    https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255


    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it."

    - from former MSDN User JohnWein

    SerialPort Info

    Multics - An OS ahead of its time.


    Everyone at some point in time does this or similar ooops and if they say no they are lying. Happy to hear you are back up and running :-)

    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, November 2, 2018 9:13 PM
    Moderator
  • So, it's a local Excel problem. Reinstall?

    Sincerely, Highly skilled coding monkey.

    Friday, November 2, 2018 9:16 PM